SQL 常用函数

问题

SQL 中有哪些常用的内置函数？字符串函数、日期函数、数值函数怎么用？

答案

SQL 内置函数是数据分析的基础工具。本文按字符串、日期、数值、类型转换、条件/控制五大类系统整理。

MySQL vs PostgreSQL

不同数据库的函数名称和语法可能不同，本文以 MySQL 为主，重要差异处标注 PostgreSQL 写法。

---

字符串函数

基础操作

-- 拼接
SELECT CONCAT('Hello', ' ', 'World');          -- 'Hello World'
SELECT CONCAT_WS('-', '2024', '01', '15');     -- '2024-01-15'（指定分隔符）

-- 长度
SELECT LENGTH('Hello');              -- 5（字节数）
SELECT CHAR_LENGTH('你好世界');       -- 4（字符数，推荐）

-- 大小写
SELECT UPPER('hello');               -- 'HELLO'
SELECT LOWER('HELLO');               -- 'hello'

-- 截取
SELECT SUBSTRING('Hello World', 1, 5);  -- 'Hello'（从第 1 位开始取 5 个字符）
SELECT LEFT('Hello', 3);                -- 'Hel'
SELECT RIGHT('Hello', 3);               -- 'llo'

查找与替换

-- 查找位置（1-based，找不到返回 0）
SELECT LOCATE('World', 'Hello World');   -- 7
SELECT INSTR('Hello World', 'World');    -- 7

-- 替换
SELECT REPLACE('Hello World', 'World', 'SQL'); -- 'Hello SQL'

-- 去除空白
SELECT TRIM('  Hello  ');             -- 'Hello'
SELECT LTRIM('  Hello  ');            -- 'Hello  '
SELECT RTRIM('  Hello  ');            -- '  Hello'
SELECT TRIM(BOTH 'x' FROM 'xxxHelloxxx'); -- 'Hello'

-- 填充
SELECT LPAD('42', 5, '0');            -- '00042'（左填充到 5 位）
SELECT RPAD('Hi', 10, '.');           -- 'Hi........'

正则与模式匹配

-- LIKE 模式匹配
SELECT * FROM users WHERE name LIKE '张%';      -- 以"张"开头
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- MySQL 8.0+ 正则
SELECT * FROM users WHERE name REGEXP '^[张李王]';  -- 以张/李/王开头
SELECT REGEXP_REPLACE('a1b2c3', '[0-9]', '');       -- 'abc'
SELECT REGEXP_SUBSTR('price: $100.50', '[0-9]+\\.?[0-9]*'); -- '100.50'

实用场景

-- 提取域名
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain FROM users;
-- 'zhang@gmail.com' → 'gmail.com'

-- 手机号脱敏
SELECT CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS masked_phone FROM users;
-- '13812345678' → '138****5678'

-- 分隔字符串处理（MySQL 没有 split，用 SUBSTRING_INDEX 模拟）
SELECT SUBSTRING_INDEX('a,b,c', ',', 1);   -- 'a'
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c', ',', 2), ',', -1); -- 'b'

---

日期函数

获取当前时间

SELECT NOW();             -- '2024-01-15 14:30:00'（日期+时间）
SELECT CURDATE();          -- '2024-01-15'（仅日期）
SELECT CURTIME();          -- '14:30:00'（仅时间）
SELECT UNIX_TIMESTAMP();   -- 1705301400（Unix 时间戳）

日期提取

SELECT YEAR('2024-01-15');         -- 2024
SELECT MONTH('2024-01-15');        -- 1
SELECT DAY('2024-01-15');          -- 15
SELECT HOUR('2024-01-15 14:30:00'); -- 14
SELECT DAYOFWEEK('2024-01-15');    -- 2（1=周日, 2=周一）
SELECT WEEKDAY('2024-01-15');      -- 0（0=周一, 6=周日）

-- EXTRACT 统一语法（SQL 标准）
SELECT EXTRACT(YEAR FROM '2024-01-15');    -- 2024
SELECT EXTRACT(MONTH FROM '2024-01-15');   -- 1
SELECT EXTRACT(HOUR FROM '2024-01-15 14:30:00'); -- 14

日期格式化

-- MySQL
SELECT DATE_FORMAT('2024-01-15', '%Y年%m月%d日');     -- '2024年01月15日'
SELECT DATE_FORMAT('2024-01-15 14:30:00', '%Y-%m');   -- '2024-01'
SELECT DATE_FORMAT('2024-01-15', '%W');                -- 'Monday'

-- PostgreSQL
SELECT TO_CHAR('2024-01-15'::date, 'YYYY年MM月DD日'); -- '2024年01月15日'

常用格式化符号（MySQL）：

符号	含义	示例
%Y	四位年份	2024
%m	两位月份	01
%d	两位天	15
%H	24 小时制	14
%i	分钟	30
%s	秒	00
%W	星期英文	Monday
%w	星期数字	0=周日

日期计算

-- 加减日期
SELECT DATE_ADD('2024-01-15', INTERVAL 7 DAY);      -- '2024-01-22'
SELECT DATE_ADD('2024-01-15', INTERVAL 1 MONTH);     -- '2024-02-15'
SELECT DATE_SUB('2024-01-15', INTERVAL 1 YEAR);      -- '2023-01-15'
SELECT '2024-01-15' + INTERVAL 7 DAY;                -- 简写形式

-- 日期差值
SELECT DATEDIFF('2024-01-15', '2024-01-01');          -- 14（天数差）
SELECT TIMESTAMPDIFF(HOUR, '2024-01-15 08:00', '2024-01-15 14:30');  -- 6
SELECT TIMESTAMPDIFF(MONTH, '2023-06-15', '2024-01-15');             -- 7

-- PostgreSQL 更简洁
-- SELECT '2024-01-15'::date - '2024-01-01'::date;  -- 14
-- SELECT AGE('2024-01-15', '2023-06-15');           -- '7 mons'

实用场景

-- 本周/本月/本季度的订单
SELECT * FROM orders WHERE order_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01'); -- 本月
SELECT * FROM orders WHERE YEARWEEK(order_date) = YEARWEEK(CURDATE());      -- 本周

-- 最近 30 天
SELECT * FROM orders WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- 按周/月/季度聚合
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS orders
FROM orders GROUP BY month;

-- 计算年龄
SELECT 
    name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM users;

-- 判断工作日（排除周六日）
SELECT order_date,
    CASE WHEN DAYOFWEEK(order_date) IN (1, 7) THEN '周末' ELSE '工作日' END AS day_type
FROM orders;

---

数值函数

基础运算

-- 四舍五入
SELECT ROUND(3.14159, 2);    -- 3.14
SELECT ROUND(3.145, 2);      -- 3.15（注意浮点精度问题）
SELECT ROUND(1234.5, -2);    -- 1200（负数表示整数位四舍五入）

-- 截断（不四舍五入）
SELECT TRUNCATE(3.14159, 2); -- 3.14

-- 向上/向下取整
SELECT CEIL(3.1);     -- 4
SELECT FLOOR(3.9);    -- 3

-- 绝对值
SELECT ABS(-42);       -- 42

-- 取模（求余数）
SELECT MOD(10, 3);     -- 1
SELECT 10 % 3;         -- 1

-- 幂运算
SELECT POWER(2, 10);   -- 1024
SELECT SQRT(144);      -- 12

实用场景

-- 保留两位小数的百分比
SELECT 
    category,
    SUM(amount) AS total,
    ROUND(SUM(amount) / (SELECT SUM(amount) FROM orders) * 100, 2) AS pct
FROM orders GROUP BY category;

-- 金额分桶（按千分位）
SELECT 
    FLOOR(amount / 1000) * 1000 AS bucket,
    COUNT(*) AS cnt
FROM orders
GROUP BY bucket
ORDER BY bucket;

-- 生成随机数（0~1之间）
SELECT RAND();           -- 0.732...
SELECT FLOOR(RAND() * 100); -- 0~99 之间的随机整数

-- 随机抽样
SELECT * FROM users ORDER BY RAND() LIMIT 100;

RAND() 性能

ORDER BY RAND() 会对全表数据计算随机值并排序，大表上极慢。大数据量的随机抽样应使用其他方案（如先取总数再随机偏移量）。

---

类型转换函数

CAST 与 CONVERT

-- CAST：SQL 标准语法
SELECT CAST('42' AS SIGNED);            -- 字符串→整数
SELECT CAST('3.14' AS DECIMAL(5,2));    -- 字符串→精确小数
SELECT CAST('2024-01-15' AS DATE);      -- 字符串→日期
SELECT CAST(42 AS CHAR);               -- 数字→字符串

-- MySQL CONVERT
SELECT CONVERT('42', SIGNED);
SELECT CONVERT('2024-01-15', DATE);

隐式类型转换

-- MySQL 会自动类型转换，但可能导致意外结果
SELECT '10' + 5;              -- 15（字符串隐式转为数字）
SELECT 'abc' + 5;             -- 5（'abc' 转为 0）
SELECT '10abc' + 5;           -- 15（'10abc' 转为 10）

隐式转换的索引陷阱

-- phone 列是 VARCHAR 类型
SELECT * FROM users WHERE phone = 13800000000;
-- MySQL 会对每一行的 phone 做 CAST 转换，导致索引失效！

-- 正确写法：保持类型一致
SELECT * FROM users WHERE phone = '13800000000';

---

JSON 函数

数据分析中越来越多地遇到 JSON 格式存储的数据（如用户行为日志、埋点属性、配置信息等），掌握 JSON 函数可以直接在 SQL 中处理这些数据，无需导出到应用层再解析。

MySQL vs PostgreSQL

MySQL 8.0+ 和 PostgreSQL 9.3+ 都提供了完善的 JSON 函数支持，但语法差异较大，以下分别介绍。

MySQL JSON 函数

提取值

MySQL 提供两种方式提取 JSON 字段中的值：函数式语法 JSON_EXTRACT 和更简洁的操作符语法。

-- 准备数据：用户行为日志，properties 字段存 JSON
-- {"event": "click", "page": "home", "duration": 120, "tags": ["vip", "new"]}

-- JSON_EXTRACT(json_col, path)：提取指定路径的值（返回带引号的 JSON 值）
SELECT JSON_EXTRACT(properties, '$.event') FROM user_logs;
-- 结果："click"（带引号）

-- -> 操作符：等价于 JSON_EXTRACT，返回 JSON 类型
SELECT properties -> '$.event' FROM user_logs;
-- 结果："click"（带引号）

-- ->> 操作符（推荐）：等价于 JSON_UNQUOTE(JSON_EXTRACT(...))，返回去引号的纯字符串
SELECT properties ->> '$.event' AS event_name FROM user_logs;
-- 结果：click（不带引号，可直接用于 WHERE 过滤）

-- 嵌套路径
SELECT properties ->> '$.page' AS page FROM user_logs;

-- 数组索引（0-based）
SELECT properties ->> '$.tags[0]' AS first_tag FROM user_logs;
-- 结果：vip

-- JSON_UNQUOTE：手动去掉 JSON 字符串的引号
SELECT JSON_UNQUOTE(JSON_EXTRACT(properties, '$.event')) FROM user_logs;
-- 等价于 properties ->> '$.event'

-- JSON_CONTAINS：判断 JSON 中是否包含指定值
SELECT * FROM user_logs
WHERE JSON_CONTAINS(properties, '"vip"', '$.tags');
-- 找出 tags 数组中包含 "vip" 的记录

-- JSON_ARRAY_LENGTH：获取 JSON 数组的长度
SELECT JSON_ARRAY_LENGTH(properties -> '$.tags') AS tag_count FROM user_logs;
-- 结果：2

实用分析场景

-- 场景一：按埋点事件类型统计 PV
SELECT
    properties ->> '$.event' AS event_name,
    COUNT(*) AS cnt
FROM user_logs
WHERE log_date = '2024-01-15'
GROUP BY event_name
ORDER BY cnt DESC;

-- 场景二：提取多个 JSON 属性，平铺为普通列
SELECT
    user_id,
    properties ->> '$.event'    AS event_name,
    properties ->> '$.page'     AS page,
    CAST(properties ->> '$.duration' AS UNSIGNED) AS duration_sec
FROM user_logs;

-- 场景三：JSON 数组展开为多行（MySQL 8.0+ JSON_TABLE）
-- 原始数据：{"tags": ["vip", "new", "active"]}
SELECT
    user_id,
    tag.value AS tag
FROM user_logs,
JSON_TABLE(
    properties -> '$.tags',
    '$[*]' COLUMNS (value VARCHAR(50) PATH '$')
) AS tag;
-- 结果：每个 tag 对应一行，类似 UNNEST

修改（分析场景较少用，了解即可）

-- JSON_SET：新增或更新字段
UPDATE user_logs SET properties = JSON_SET(properties, '$.processed', true);

-- JSON_REMOVE：删除指定字段
UPDATE user_logs SET properties = JSON_REMOVE(properties, '$.temp_field');

PostgreSQL JSON 函数

PostgreSQL 支持两种 JSON 类型：json（原始文本，按原样存储）和 jsonb（二进制，解析后存储，推荐使用，支持索引）。

操作符提取值

-- 准备数据（jsonb 类型）
-- properties: {"event": "click", "page": "home", "tags": ["vip", "new"]}

-- -> 操作符：提取 JSON 对象，返回 jsonb 类型（适合嵌套继续操作）
SELECT properties -> 'event' FROM user_logs;
-- 结果："click"（JSON 类型，带引号）

-- ->> 操作符：提取为纯文本（推荐，可直接比较字符串）
SELECT properties ->> 'event' AS event_name FROM user_logs;
-- 结果：click

-- 链式操作：嵌套 JSON
SELECT properties -> 'meta' ->> 'source' AS source FROM user_logs;

-- 数组元素（0-based）
SELECT properties -> 'tags' ->> 0 AS first_tag FROM user_logs;
-- 结果：vip

-- #> 和 #>> ：用路径数组提取（适合深层嵌套）
SELECT properties #>> '{meta, source}' AS source FROM user_logs;
-- 等价于 properties -> 'meta' ->> 'source'

-- jsonb_each：展开 JSON 对象的所有 key-value 为行
SELECT key, value
FROM user_logs,
jsonb_each(properties)
WHERE user_id = 1;
-- 返回：key='event', value='"click"'
--       key='page',  value='"home"'
--       ...

-- jsonb_each_text：value 以文本形式返回（去引号）
SELECT key, value
FROM user_logs,
jsonb_each_text(properties)
WHERE user_id = 1;

-- json_array_elements：展开 JSON 数组为多行（类似 MySQL JSON_TABLE）
SELECT
    user_id,
    tag::text AS tag
FROM user_logs,
json_array_elements_text(properties -> 'tags') AS tag
WHERE log_date = '2024-01-15';
-- 每个 tag 对应一行

-- @> 包含操作符（jsonb）：判断是否包含指定 JSON 子集
SELECT * FROM user_logs
WHERE properties @> '{"event": "click"}';
-- 比 JSON_CONTAINS 更简洁，且可以走 GIN 索引

-- ? 操作符：判断 key 是否存在
SELECT * FROM user_logs WHERE properties ? 'duration';

-- GIN 索引（大幅加速 JSON 查询）
CREATE INDEX idx_user_logs_props ON user_logs USING GIN (properties);

MySQL vs PostgreSQL JSON 对比

操作	MySQL	PostgreSQL
提取为文本	col ->> '$.key'	col ->> 'key'
提取为 JSON	col -> '$.key'	col -> 'key'
数组展开	JSON_TABLE(col, '$[*]' ...)	json_array_elements(col -> 'arr')
包含判断	JSON_CONTAINS(col, val, path)	col @> '{"key": val}'
数组长度	JSON_ARRAY_LENGTH(col -> '$.arr')	jsonb_array_length(col -> 'arr')
索引支持	虚拟列 + 普通索引	GIN 索引（原生支持）

分析场景推荐

• 如果只是读取JSON 字段，->> 操作符在 MySQL 和 PostgreSQL 中语法相近，容易记忆
• PostgreSQL 的 jsonb + GIN 索引在大量 JSON 过滤查询时性能远优于 MySQL
• 高频查询的 JSON 字段，建议在 MySQL 中使用虚拟生成列提取后建索引

-- MySQL：用虚拟列给高频查询的 JSON 字段加索引
ALTER TABLE user_logs
ADD COLUMN event_name VARCHAR(50)
    GENERATED ALWAYS AS (properties ->> '$.event') VIRTUAL,
ADD INDEX idx_event_name (event_name);

-- 之后查询自动走索引
SELECT COUNT(*) FROM user_logs WHERE event_name = 'click';

---

条件与控制函数

CASE WHEN

-- 简单形式
SELECT 
    CASE status 
        WHEN 1 THEN '待支付'
        WHEN 2 THEN '已支付'
        WHEN 3 THEN '已发货'
        ELSE '未知'
    END AS status_text
FROM orders;

-- 搜索形式（更灵活）
SELECT 
    CASE 
        WHEN amount >= 10000 THEN '大额'
        WHEN amount >= 1000 THEN '中额'
        ELSE '小额'
    END AS order_level
FROM orders;

IFNULL / COALESCE / NULLIF

-- IFNULL(a, b)：a 为 NULL 时返回 b（MySQL）
SELECT IFNULL(nickname, '匿名用户') FROM users;

-- COALESCE(a, b, c, ...)：返回第一个非 NULL 值（标准 SQL，推荐）
SELECT COALESCE(nickname, real_name, '匿名用户') FROM users;

-- NULLIF(a, b)：a = b 时返回 NULL，否则返回 a
SELECT NULLIF(0, 0);       -- NULL（常用于避免除以零）
SELECT 100 / NULLIF(0, 0); -- NULL（而不是报错）

IF

-- MySQL 的 IF 函数
SELECT IF(age >= 18, '成年', '未成年') AS age_group FROM users;

-- 相当于
SELECT CASE WHEN age >= 18 THEN '成年' ELSE '未成年' END AS age_group FROM users;

GREATEST / LEAST

-- 返回最大/最小值
SELECT GREATEST(10, 20, 30);  -- 30
SELECT LEAST(10, 20, 30);     -- 10

-- 实用场景：取多个日期中的最新/最早
SELECT GREATEST(create_time, update_time, login_time) AS latest_activity FROM users;

---

函数速查表

分类	函数	作用	示例
字符串	CONCAT	拼接	CONCAT('a', 'b') → 'ab'
	SUBSTRING	截取	SUBSTRING('Hello', 1, 3) → 'Hel'
	REPLACE	替换	REPLACE('abc', 'b', 'x') → 'axc'
	TRIM	去空白	TRIM(' hi ') → 'hi'
	LPAD/RPAD	填充	LPAD('5', 3, '0') → '005'
日期	NOW()	当前日期时间	'2024-01-15 14:30:00'
	DATE_FORMAT	格式化	DATE_FORMAT(d, '%Y-%m')
	DATE_ADD	加日期	DATE_ADD(d, INTERVAL 7 DAY)
	DATEDIFF	天数差	DATEDIFF('01-15', '01-01') → 14
	TIMESTAMPDIFF	时间差	TIMESTAMPDIFF(HOUR, t1, t2)
数值	ROUND	四舍五入	ROUND(3.14159, 2) → 3.14
	CEIL/FLOOR	向上/下取整	CEIL(3.1) → 4
	ABS	绝对值	ABS(-5) → 5
	MOD	取模	MOD(10, 3) → 1
转换	CAST	类型转换	CAST('42' AS SIGNED)
	COALESCE	首个非 NULL	COALESCE(a, b, 0)
	NULLIF	相等返 NULL	NULLIF(0, 0) → NULL
条件	CASE WHEN	条件分支	CASE WHEN ... THEN ... END
	IF	二元条件	IF(a>0, '正', '非正')
JSON	->>	提取为文本	properties ->> '$.event'（MySQL）
	jsonb_each	展开 kv 为行	FROM jsonb_each(col)（PG）
	json_array_elements	数组展开为行	FROM json_array_elements(col->'arr')

---

常见面试问题

Q1: CHAR_LENGTH 和 LENGTH 的区别？

答案：

• LENGTH：返回字节数。UTF-8 下一个中文字符占 3 字节
• CHAR_LENGTH：返回字符数。一个中文字符就是 1

SELECT LENGTH('你好');       -- 6（UTF-8 中每个中文 3 字节）
SELECT CHAR_LENGTH('你好');  -- 2（2 个字符）

数据分析中应该用 CHAR_LENGTH，因为我们通常关心的是字符数而非字节数。

Q2: DATEDIFF 和 TIMESTAMPDIFF 的区别？

答案：

• DATEDIFF(end, start) 只能算天数差，忽略时间部分
• TIMESTAMPDIFF(unit, start, end) 可以算任意单位（SECOND、MINUTE、HOUR、DAY、MONTH、YEAR）

SELECT DATEDIFF('2024-01-15 23:00', '2024-01-15 01:00');  -- 0（同一天）
SELECT TIMESTAMPDIFF(HOUR, '2024-01-15 01:00', '2024-01-15 23:00'); -- 22

注意参数顺序：DATEDIFF 是 end - start，TIMESTAMPDIFF 是 start, end。

Q3: 如何避免 "除以零" 错误？

答案：

-- ❌ 可能报错（PostgreSQL 会报错，MySQL 返回 NULL）
SELECT 100 / 0;

-- ✅ 使用 NULLIF
SELECT 100 / NULLIF(views, 0) AS ctr FROM pages;
-- views 为 0 时，NULLIF 返回 NULL，除法结果为 NULL 而非报错

-- ✅ 使用 CASE WHEN
SELECT CASE WHEN views > 0 THEN clicks * 100.0 / views ELSE 0 END AS ctr FROM pages;

Q4: GROUP_CONCAT 和 CONCAT 的区别？

答案：

• CONCAT：拼接多个列/值为一个字符串（横向拼接）
• GROUP_CONCAT：将多行的某一列合并为一个字符串（纵向聚合）

-- CONCAT：拼接列
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- GROUP_CONCAT：聚合行
SELECT department, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS members
FROM users GROUP BY department;
-- 结果：'技术部' | '张三, 李四, 王五'

警告

GROUP_CONCAT 默认最大长度 1024 字节，超出会截断。可通过 SET group_concat_max_len = 10240; 调大。PostgreSQL 对应函数是 STRING_AGG(name, ', ')。

Q5: 如何将字符串 '2024-01-15' 转为日期并计算天数差？

答案：

-- MySQL 通常自动转换
SELECT DATEDIFF(CURDATE(), '2024-01-15');

-- 显式转换（更安全）
SELECT DATEDIFF(CURDATE(), STR_TO_DATE('2024-01-15', '%Y-%m-%d'));

-- PostgreSQL
-- SELECT CURRENT_DATE - '2024-01-15'::date;

Q6: ROUND 在不同数据库中的行为一致吗？

答案：

不完全一致。当小数恰好是 .5 时：

• MySQL：四舍五入（Rounding Half Up）→ ROUND(2.5) = 3
• PostgreSQL：四舍六入五成双（Banker's Rounding）→ ROUND(2.5) = 2，ROUND(3.5) = 4

金额计算中推荐使用 DECIMAL 类型，避免浮点精度问题。

Q7: 如何提取 JSON 字段中的特定属性？MySQL 和 PostgreSQL 有何差异？

答案：

两者均支持 -> 和 ->> 操作符，但路径语法不同：

差异点	MySQL	PostgreSQL
路径语法	$.key（JSONPath 风格）	key（直接写字段名）
返回类型（->>）	纯字符串	纯字符串
数组元素	$.arr[0]	arr 取数组再用 ->> 0
嵌套路径	$.a.b.c	链式 -> 'a' -> 'b' ->> 'c' 或 #>> '{a,b,c}'

-- 同一目标：提取 properties.event 字段

-- MySQL
SELECT properties ->> '$.event' AS event_name FROM user_logs;

-- PostgreSQL
SELECT properties ->> 'event' AS event_name FROM user_logs;

关键区别：MySQL 路径必须以 $ 开头，PostgreSQL 直接写 key 名。此外，PostgreSQL 的 jsonb 类型支持 GIN 索引，过滤性能远优于 MySQL；MySQL 需要通过虚拟生成列间接实现索引加速：

-- MySQL：提取高频查询的 JSON 字段为虚拟列并建索引
ALTER TABLE user_logs
ADD COLUMN event_name VARCHAR(50)
    GENERATED ALWAYS AS (properties ->> '$.event') VIRTUAL,
ADD INDEX idx_event_name (event_name);

Q8: SUBSTRING_INDEX 怎么用？给一个实际数据清洗的例子

答案：

SUBSTRING_INDEX(str, delim, count) 按分隔符切割字符串，count 为正数时取左侧 N 段，为负数时取右侧 N 段。

-- 基础用法
SELECT SUBSTRING_INDEX('a,b,c,d', ',', 2);    -- 'a,b'（取前 2 段）
SELECT SUBSTRING_INDEX('a,b,c,d', ',', -2);   -- 'c,d'（取后 2 段）
SELECT SUBSTRING_INDEX('a,b,c,d', ',', -1);   -- 'd'（取最后 1 段）

数据清洗场景：提取邮箱域名、URL 路径

-- 场景一：从 email 提取域名
SELECT
    email,
    SUBSTRING_INDEX(email, '@', -1) AS domain
FROM users;
-- 'zhang@gmail.com' → 'gmail.com'
-- 'li@company.co.jp' → 'company.co.jp'

-- 场景二：从 URL 提取路径（去掉域名）
-- 'https://example.com/products/detail?id=123'
SELECT
    url,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(url, '/', 3),  -- 先取前 3 段：'https://example.com'
        '/',
        -1                             -- 再取最后 1 段：'example.com'
    ) AS hostname
FROM page_logs;

-- 场景三：取逗号分隔列表中的第 N 个元素
-- 等价于 split(str, ',')[n-1]
-- 取第 2 个元素：先取前 2 段，再取最后 1 段
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d', ',', 2), ',', -1); -- 'b'
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d', ',', 3), ',', -1); -- 'c'

PostgreSQL 中没有 SUBSTRING_INDEX

PostgreSQL 需要改用 SPLIT_PART(str, delim, n) 函数，下标从 1 开始：

-- PostgreSQL
SELECT SPLIT_PART('a,b,c,d', ',', 2);  -- 'b'（第 2 段）
SELECT SPLIT_PART('zhang@gmail.com', '@', 2);  -- 'gmail.com'

Q9: 如何将逗号分隔的字符串拆分为多行？

答案：

这是一个常见的数据清洗需求——将 "tag1,tag2,tag3" 这样存储在单列中的多值数据展开为多行，方便后续聚合分析。

MySQL 方案：借助辅助数字表

MySQL 没有内置的 split 展开功能，常见做法是用 SUBSTRING_INDEX 配合数字辅助表逐个提取：

-- 方法一：借助序号表（适合已知最大分段数）
-- 假设标签最多 5 个
WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 5
)
SELECT
    u.user_id,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(u.tags, ',', n.n),
        ',',
        -1
    ) AS tag
FROM users u
JOIN nums n
    ON n.n <= CHAR_LENGTH(u.tags) - CHAR_LENGTH(REPLACE(u.tags, ',', '')) + 1
WHERE u.tags IS NOT NULL AND u.tags != '';
-- 'vip,new,active' 展开为 3 行：vip / new / active

-- 方法二：MySQL 8.0+ JSON_TABLE（更简洁，先转 JSON 数组再展开）
SELECT
    user_id,
    tag.value AS tag
FROM users,
JSON_TABLE(
    -- 将 'vip,new,active' 转为 '["vip","new","active"]'
    CONCAT('["', REPLACE(tags, ',', '","'), '"]'),
    '$[*]' COLUMNS (value VARCHAR(50) PATH '$')
) AS tag;

PostgreSQL 方案：内置 STRING_TO_ARRAY + UNNEST

-- PostgreSQL 一行搞定
SELECT
    user_id,
    UNNEST(STRING_TO_ARRAY(tags, ',')) AS tag
FROM users
WHERE tags IS NOT NULL;
-- 'vip,new,active' 直接展开为 3 行

-- 配合聚合：统计每个标签的用户数
SELECT
    TRIM(tag) AS tag,
    COUNT(*) AS user_cnt
FROM users,
UNNEST(STRING_TO_ARRAY(tags, ',')) AS tag
GROUP BY tag
ORDER BY user_cnt DESC;

更好的设计建议

如果业务上 频繁需要展开多值字段，说明数据模型需要优化——应该建独立的关联表（如 user_tags），而非把多值塞到一个字段里。这样查询效率更高，也更容易维护。

---

相关链接

• MySQL 字符串函数
• MySQL 日期函数
• MySQL 数值函数
• MySQL JSON 函数
• PostgreSQL 函数文档
• PostgreSQL JSON 函数
• SELECT 查询基础 - WHERE、ORDER BY
• 聚合函数与 GROUP BY - 聚合上下文中的函数
• 数据类型 - 数据库的基本数据类型