我的个人技术笔记

字符串处理

9 min read

字符串处理

下面按功能类别总结 MySQL 常用字符串函数，然后结合业务场景说明如何灵活运用。

1. 字符串连接与拼接

函数说明
CONCAT(str1, str2, ...)拼接多个字符串，遇 NULL 返回 NULL
CONCAT_WS(sep, str1, str2, ...)用分隔符拼接，自动忽略 NULL
GROUP_CONCAT([DISTINCT] expr ORDER BY … SEPARATOR …)行转列，将分组内的值拼接成一个字符串
SELECT CONCAT('Hello', ' ', 'World');         -- Hello World
SELECT CONCAT_WS('-', '2026', '05', '26');    -- 2026-05-26
SELECT GROUP_CONCAT(DISTINCT city ORDER BY city SEPARATOR '/') FROM users;

2. 截取子串

函数说明
LEFT(str, len)从左边截取 len 个字符
RIGHT(str, len)从右边截取 len 个字符
SUBSTRING(str, pos [, len]) 或 SUBSTR()、MID()从位置 pos 开始截取 len 个字符（pos 从 1 开始）
SUBSTRING_INDEX(str, delim, count)按分隔符截取，count 为正数从左数，负数从右数
SELECT LEFT('abcdef', 3);                    -- abc
SELECT RIGHT('abcdef', 2);                   -- ef
SELECT SUBSTRING('abcdef', 2, 3);            -- bcd
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);  -- www.mysql
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -1); -- com

3. 字符串长度

函数说明
CHAR_LENGTH(str) 或 CHARACTER_LENGTH()字符个数（多字节算 1 个）
LENGTH(str)字节数（UTF-8 中一个汉字通常 3 字节）
BIT_LENGTH(str)比特长度（字节数*8）
SELECT CHAR_LENGTH('你好');   -- 2
SELECT LENGTH('你好');        -- 6 （UTF-8 下）

4. 查找与定位

函数说明
LOCATE(substr, str [, pos])返回子串第一次出现的位置（从 1 开始），找不到返回 0
POSITION(substr IN str)同 LOCATE 语法不同
INSTR(str, substr)参数顺序与 LOCATE 相反
FIND_IN_SET(str, strlist)在逗号分隔的列表中查找，返回位置（从 1 开始）
FIELD(str, str1, str2, ...)在参数列表中查找，返回位置（0 表示找不到）
SELECT LOCATE('b', 'abcabc');            -- 2
SELECT INSTR('abcabc', 'b');             -- 2
SELECT FIND_IN_SET('a', 'a,b,c');        -- 1
SELECT FIELD('b', 'a', 'b', 'c');        -- 2

5. 替换与插入

函数说明
REPLACE(str, from_str, to_str)全局替换
INSERT(str, pos, len, newstr)从 pos 开始删除 len 个字符，再插入 newstr
REPEAT(str, count)重复字符串 count 次
REVERSE(str)反转字符串
SELECT REPLACE('ab12cd12ef', '12', 'XX');  -- abXXcdXXef
SELECT INSERT('abcdef', 3, 2, 'XX');       -- abXXef
SELECT REPEAT('ab', 3);                    -- ababab

6. 大小写转换

函数说明
UPPER(str) 或 UCASE(str)转大写
LOWER(str) 或 LCASE(str)转小写
SELECT UPPER('Hello');   -- HELLO
SELECT LOWER('Hello');   -- hello

7. 去除空格与填充

函数说明
TRIM([{BOTH｜LEADING｜TRAILING} [remstr] FROM] str)移除两端/前导/末尾的指定字符（默认空格）
LTRIM(str)去除左边空格
RTRIM(str)去除右边空格
LPAD(str, len, padstr)左侧填充到指定字符长度
RPAD(str, len, padstr)右侧填充到指定字符长度
SELECT TRIM('  hello  ');                -- hello
SELECT TRIM(LEADING '0' FROM '00123');   -- 123
SELECT LPAD('5', 3, '0');                -- 005
SELECT RPAD('ab', 5, '0');               -- ab000

8. 正则表达式（MySQL 8.0+）

函数说明
REGEXP_LIKE(expr, pat [, match_type])返回是否匹配（1/0）
REGEXP_INSTR(expr, pat [, pos …])返回匹配子串的位置
REGEXP_SUBSTR(expr, pat [, pos …])返回匹配的子串
REGEXP_REPLACE(expr, pat, repl [, pos …])正则替换
SELECT REGEXP_LIKE('abc123', '[0-9]+');          -- 1
SELECT REGEXP_SUBSTR('abc123def', '[0-9]+');     -- 123
SELECT REGEXP_REPLACE('abc123def', '[0-9]+', 'X'); -- abcXdef

MySQL 5.7 只能用 expr REGEXP pat 进行条件匹配，不支持提取和替换。

9. 比较与排序

函数说明
STRCMP(expr1, expr2)按当前排序规则比较，返回 -1/0/1
expr1 LIKE pat简单模式匹配（% 任意多字符，_ 一个字符）
expr NOT LIKE pat不匹配
排序规则 COLLATE可用于 ORDER BY 或 WHERE 实现大小写/重音不敏感
SELECT STRCMP('abc', 'abd');      -- -1
SELECT 'hello' LIKE '%ello';      -- 1
SELECT 'a' COLLATE utf8mb4_general_ci = 'A';  -- 1（大小写不敏感）

10. 其他实用函数

函数说明
FORMAT(X, D [, locale])数字格式化（千分位，返回字符串）
ASCII(str)首字符 ASCII 码
CHAR(N, ... [USING charset])将整数转为对应字符
HEX(str), UNHEX(str)字符串与十六进制互转
BIN(N), OCT(N)十进制转二进制、八进制字符串
QUOTE(str)产生带单引号的 SQL 转义字符串
SOUNDEX(str)模糊读音匹配
ELT(N, str1, str2, ...)返回第 N 个字符串
MAKE_SET(bits, str1, str2, ...)根据二进制位选择字符串子集
WEIGHT_STRING(str [AS …])排序权重值（调试用）
SELECT FORMAT(1234567.89, 2);         -- 1,234,567.89
SELECT CHAR(65 USING utf8mb4);        -- A
SELECT HEX('abc');                    -- 616263
SELECT ELT(2, 'a', 'b', 'c');         -- b

常用业务场景与处理方案

1. 拼接用户姓名（处理 NULL）

  • 直接 CONCAT(last_name, first_name) 若 last_name 为 NULL，结果全 NULL。
SELECT CONCAT_WS(' ', last_name, first_name) AS full_name FROM users;

2. 手机号/身份证脱敏

-- 手机号中间四位变 ****
SELECT CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS masked_phone
FROM users;
 
-- 身份证后四位保留，其余用*覆盖（假设18位）
SELECT CONCAT(REPEAT('*', 14), RIGHT(id_card, 4)) AS masked_id
FROM users;

3. 提取邮箱域名/用户名

SELECT SUBSTRING_INDEX(email, '@', -1) AS domain FROM users;
SELECT SUBSTRING_INDEX(email, '@', 1) AS username FROM users;

4. 从地址中提取数字（如楼栋号）

  • 适合用正则（8.0+），否则可结合 SUBSTRING 和 LOCATE。假设楼栋号为“XX号楼”前的数字。
-- MySQL 8.0+
SELECT REGEXP_SUBSTR(address, '[0-9]+(?=号楼)') AS building_no FROM houses;
 
-- 5.7 简易方式：截取“号楼”前的部分再处理（受限）
SELECT SUBSTRING_INDEX(address, '号楼', 1) AS prefix FROM houses;
-- 再提取末尾数字较为复杂

5. 判断字符串是否包含某子串

-- 返回 1 表示包含
SELECT LOCATE('admin', user_name) > 0 AS is_admin FROM users;
-- 或用 REGEXP
SELECT user_name REGEXP 'admin' FROM users;

6. 逗号分隔字段查询（多值匹配）

-- 表中 tags 列存储 'a,b,c'，查询包含 'b' 的记录
SELECT * FROM articles WHERE FIND_IN_SET('b', tags);

7. 格式化输出（生成订单号）

SELECT CONCAT('ORD', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(id, 6, '0')) AS order_no
FROM orders;
-- 结果类似 ORD20260526000123

8. 清除收件地址中的多余空格/特殊字符

-- 去首尾空格和连续空格
SELECT REPLACE(TRIM(address), '  ', ' ') AS clean_address FROM orders;
-- 更彻底的去除所有空白字符 (MySQL 8.0+)
SELECT REGEXP_REPLACE(address, '[[:space:]]+', ' ') AS clean_address FROM orders;

9. 大小写不敏感的精确查询

SELECT * FROM users WHERE UPPER(username) = UPPER('Admin');
-- 或使用 COLLATE
SELECT * FROM users WHERE username COLLATE utf8mb4_general_ci = 'Admin';

10. 按首字母分类（中文拼音需自定义函数，英文简单）

SELECT UPPER(LEFT(name, 1)) AS initial, COUNT(*) FROM users
GROUP BY initial;

11. 计算字段显示长度，避免前端溢出

SELECT title, CHAR_LENGTH(title) AS title_len FROM articles
WHERE CHAR_LENGTH(title) > 30;

12. 生成随机验证码（字母+数字）

-- 6位随机码
SELECT UPPER(SUBSTRING(MD5(RAND()), 1, 6));
-- 或自选字符集
SELECT SUBSTRING('23456789ABCDEFGHJKLMNPQRSTUVWXYZ', FLOOR(1+RAND()*32), 6);

13. 简单 JSON 字段提取（MySQL 5.7+ 支持 JSON 类型更好）

-- 假设 extra 为 JSON 字符串，提取 name
SELECT JSON_UNQUOTE(JSON_EXTRACT(extra, '$.name')) AS name FROM profiles;
-- 若单纯字符串模拟，可用 SUBSTRING_INDEX 等但不够健壮

注意

  • SUBSTRING、LEFT、RIGHT 等函数基于字符计算，多字节字符如中文不会截断乱码。
  • 涉及性能时，对大量数据使用 LIKE '%xxx%' 或正则表达式可能较慢，尽量考虑前缀索引或全文索引。
  • 正则函数 REGEXP_REPLACE、REGEXP_SUBSTR 是 MySQL 8.0 新特性，5.7 环境需用其他方式或应用程序处理。

熟练掌握这些函数和场景，可以高效解决绝大多数 SQL 中的字符串处理需求。

Graph View