字符串函数

导学

数据库中的文本数据经常需要清洗、拼接、截取和格式化。MySQL 5.7 提供了丰富的字符串函数，掌握它们能让你在 SQL 层完成大量文本处理工作。

定义

字符串函数：对字符串数据进行操作的函数，包括拼接、截取、查找、替换、格式化、大小写转换等。

常用字符串函数

拼接函数

CONCAT

CONCAT(str1, str2, ...) 将多个字符串拼接为一个字符串。

当前数据状态：

先创建示例表并插入数据：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

当前表数据：

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

操作语句：

SELECT
    emp_id,
    CONCAT(emp_name, ' - ', dept) AS full_info
FROM employees;

操作后的数据状态：

emp_id	full_info
1	大翔 - 技术部
2	白歌 - 技术部

结果解读：CONCAT 将 emp_name、分隔符和 dept 拼接为完整信息。注意 emp_id=2 的 score 为 NULL，如果 CONCAT 参数中出现 NULL，整个结果会变成 NULL。

NULL 陷阱演示：

SELECT
    emp_id,
    CONCAT(emp_name, ' ', score, ' ', dept) AS full_info_with_score
FROM employees;

结果：

emp_id	full_info_with_score
1	大翔 100 技术部
2	NULL

结果解读：emp_id=2 因为 score 为 NULL，导致整个拼接结果为 NULL。如需将 NULL 视为空字符串，应使用 IFNULL 包裹：

SELECT
    emp_id,
    CONCAT(emp_name, ' ', IFNULL(score, ''), ' ', dept) AS safe_full_info
FROM employees;

结果：

emp_id	safe_full_info
1	大翔 100 技术部
2	白歌技术部

CONCAT_WS

CONCAT_WS(separator, str1, str2, ...) 使用指定分隔符拼接字符串，WS = With Separator。它会自动跳过 NULL 值。

当前数据状态：复用上面的 employees 表。

操作语句：

SELECT
    emp_id,
    CONCAT_WS(' ', emp_name, score, dept) AS full_info_ws
FROM employees;

操作后的数据状态：

emp_id	full_info_ws
1	大翔 100 技术部
2	白歌技术部

结果解读：CONCAT_WS 使用空格作为分隔符，并且自动跳过了 score 为 NULL 的行，不会导致整体结果为 NULL。这是处理可能含 NULL 字段拼接的推荐方式。

拼接日期格式示例

SELECT CONCAT_WS('-', '2024', '01', '15') AS formatted_date;

结果：

formatted_date
2024-01-15

截取函数

SUBSTRING

SUBSTRING(str, pos, len) 从指定位置开始截取指定长度的字符串，MySQL 字符串位置从1开始。

当前数据状态：复用上面的 employees 表。

操作语句：

SELECT
    emp_id,
    emp_name,
    SUBSTRING(emp_name, 1, 1) AS first_char,
    SUBSTRING(dept, 1, 2) AS dept_prefix,
    SUBSTRING(dept, 3) AS dept_suffix
FROM employees;

操作后的数据状态：

emp_id	emp_name	first_char	dept_prefix	dept_suffix
1	大翔	大	技术	部
2	白歌	白	技术	部

结果解读：SUBSTRING(emp_name, 1, 1) 从第 1 位开始取 1 个字符得到姓氏；SUBSTRING(dept, 1, 2) 从第 1 位开始取 2 个字符得到 '技术'；SUBSTRING(dept, 3) 从第 3 位取到末尾得到 '部'。

LEFT 和 RIGHT

LEFT(str, len) 从左侧截取指定长度，RIGHT(str, len) 从右侧截取指定长度。

当前数据状态：复用上面的 employees 表。

操作语句：

SELECT
    emp_id,
    emp_name,
    LEFT(emp_name, 1) AS first_char,
    RIGHT(dept, 1) AS last_char
FROM employees;

操作后的数据状态：

emp_id	emp_name	first_char	last_char
1	大翔	大	部
2	白歌	白	部

结果解读：LEFT 和 RIGHT 是 SUBSTRING 的快捷方式，分别从左端和右端截取，在取固定前缀或后缀时非常直观。

MySQL 的字符串位置从1开始，不是 0。SUBSTRING(str, 0) 返回空字符串。

查找函数

LOCATE 和 INSTR

LOCATE(substr, str, pos) 返回子串首次出现的位置，可选参数 pos 指定起始搜索位置。INSTR(str, substr) 功能相同但参数顺序相反。

当前数据状态：复用上面的 employees 表。

操作语句：

SELECT
    emp_id,
    emp_name,
    LOCATE('大', emp_name) AS da_position,
    LOCATE('歌', emp_name) AS ge_position,
    INSTR(emp_name, '张') AS not_found
FROM employees;

操作后的数据状态：

emp_id	emp_name	da_position	ge_position	not_found
1	大翔	1	0	0
2	白歌	0	1	0

结果解读：

LOCATE('大', emp_name) 返回 '大' 首次出现的位置，emp_id=1 中 '大翔' 的 '大' 在第 1 位
LOCATE('歌', emp_name) 返回 '歌' 首次出现的位置，emp_id=2 中 '白歌' 的 '歌' 在第 2 位
INSTR(emp_name, '张') 参数顺序与 LOCATE 相反，'大翔' 和 '白歌' 中都没有 '张'，返回 0

替换与删除

REPLACE

REPLACE(str, from_str, to_str) 将字符串中所有 from_str 替换为 to_str。

当前数据状态：复用上面的 employees 表。

操作语句：

SELECT
    emp_id,
    emp_name,
    REPLACE(emp_name, '大', '小') AS replaced_name
FROM employees;

操作后的数据状态：

emp_id	emp_name	replaced_name
1	大翔	小翔
2	白歌	白歌

结果解读：REPLACE(phone, '-', '') 将手机号中的所有横线移除，得到连续数字，便于存储或后续比对。

TRIM

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) 去除字符串首尾指定字符，默认去除空格。

当前数据状态：

以下用常量演示 TRIM 函数：

SELECT
    '  Hello World  ' AS raw_text,
    TRIM('  Hello World  ') AS trim_spaces,
    TRIM(BOTH 'x' FROM 'xxxMySQLxxx') AS trim_x,
    TRIM(LEADING ' ' FROM '  Hello World  ') AS trim_leading_space
UNION ALL
SELECT
    'xxxMySQLxxx',
    TRIM('xxxMySQLxxx'),
    TRIM(BOTH 'x' FROM 'xxxMySQLxxx'),
    TRIM(LEADING 'x' FROM 'xxxMySQLxxx');

操作后的数据状态：

raw_text	trim_spaces	trim_x	trim_leading_space
`Hello World`	Hello World	MySQL	Hello World
`xxxMySQLxxx`	`xxxMySQLxxx`	MySQL	`xxxMySQLxxx`

结果解读：

TRIM(raw_text) 默认去除首尾空格，input_id=1 的前后空格被移除
TRIM(BOTH 'x' FROM raw_text) 去除首尾的 'x' 字符，input_id=2 变为 'MySQL'
TRIM(LEADING ' ' FROM raw_text) 仅去除开头的空格，保留尾部空格

大小写转换

UPPER 和 LOWER

当前数据状态：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

当前表数据：

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

操作语句：

SELECT
    emp_id,
    emp_name,
    UPPER(emp_name) AS upper_name,
    LOWER(emp_name) AS lower_name
FROM employees;

操作后的数据状态：

emp_id	emp_name	upper_name	lower_name
1	大翔	大翔	大翔
2	白歌	白歌	白歌

结果解读：UPPER 将全部字符转为大写，LOWER 转为小写。中文没有大小写变化，英文才会变化。常用于不区分大小写的查询：WHERE LOWER(emp_name) = 'daxiang'。

长度函数

LENGTH 和 CHAR_LENGTH

LENGTH(str) 返回字节长度，CHAR_LENGTH(str) 返回字符长度。

当前数据状态：复用上面的 employees 表。

操作语句：

SELECT
    emp_id,
    emp_name,
    LENGTH(emp_name) AS byte_length,
    CHAR_LENGTH(emp_name) AS char_length
FROM employees;

操作后的数据状态（utf8mb4 下）：

emp_id	emp_name	byte_length	char_length
1	大翔	6	2
2	白歌	6	2

英文对比：

SELECT LENGTH('Hello') AS en_bytes, CHAR_LENGTH('Hello') AS en_chars;

en_bytes	en_chars
5	5

结果解读：

英文 'Hello'，LENGTH 和 CHAR_LENGTH 相等，都是 5
中文 emp_name（如 '大翔'），utf8mb4 下每个汉字占 3 字节，所以 LENGTH=6，CHAR_LENGTH=2

重要区别：LENGTH 返回字节数，CHAR_LENGTH（或 CHARACTER_LENGTH）返回字符数。在 utf8mb4 下，一个中文字符占 3 字节。若字符集为 utf8mb4，一个 emoji 占 4 字节。

SQL 综合示例

场景一：格式化手机号（脱敏）

当前数据状态：

以下用常量演示手机号脱敏：

SELECT
    '13800138001' AS phone,
    CONCAT(LEFT('13800138001', 3), '****', RIGHT('13800138001', 4)) AS masked_phone
UNION ALL
SELECT
    '13912345678',
    CONCAT(LEFT('13912345678', 3), '****', RIGHT('13912345678', 4));

操作后的数据状态：

phone	masked_phone
13800138001	138****8001
13912345678	139****5678

结果解读：通过 LEFT(phone, 3) 取前 3 位，RIGHT(phone, 4) 取后 4 位，中间用 '****' 拼接，实现手机号脱敏显示。

场景二：提取日期字符串中的年份和月份

当前数据状态：

以下用常量演示日期字符串截取：

SELECT
    '2000-05-20' AS birth_date,
    SUBSTRING('2000-05-20', 1, 4) AS birth_year,
    SUBSTRING('2000-05-20', 6, 2) AS birth_month
UNION ALL
SELECT
    '1998-12-15',
    SUBSTRING('1998-12-15', 1, 4),
    SUBSTRING('1998-12-15', 6, 2);

操作后的数据状态：

birth_date	birth_year	birth_month
2000-05-20	2000	05
1998-12-15	1998	12

结果解读：从日期字符串中截取年份（第 1 位开始 4 个字符）和月份（第 6 位开始 2 个字符）。当然，如果字段是 DATE 类型，用 YEAR() 和 MONTH() 函数更专业，详见《日期函数》文档。

场景三：按分隔符拆分邮箱提取域名

MySQL 5.7 没有内置 SPLIT 函数，可用 SUBSTRING_INDEX 实现。

当前数据状态：

以下用常量演示邮箱拆分：

SELECT
    'daxiang@example.com' AS email,
    SUBSTRING_INDEX('daxiang@example.com', '@', 1) AS username_part,
    SUBSTRING_INDEX('daxiang@example.com', '@', -1) AS domain_part
UNION ALL
SELECT
    'baige@company.co.uk',
    SUBSTRING_INDEX('baige@company.co.uk', '@', 1),
    SUBSTRING_INDEX('baige@company.co.uk', '@', -1);

操作后的数据状态：

email	username_part	domain_part
daxiang@example.com	daxiang	example.com
baige@company.co.uk	baige	company.co.uk

结果解读：

SUBSTRING_INDEX(email, '@', 1) 返回第 1 个 @ 之前的部分（用户名）
SUBSTRING_INDEX(email, '@', -1) 返回最后一个 @ 之后的部分（域名）
若参数为正数，从左往右数；若为负数，从右往左数

场景四：统计字符串中某字符出现次数

当前数据状态：复用上面的 employees 表。

操作语句：

SELECT
    emp_id,
    emp_name,
    LENGTH(emp_name) - LENGTH(REPLACE(emp_name, '大', '')) AS da_count
FROM employees;

操作后的数据状态：

emp_id	emp_name	da_count
1	大翔	1
2	白歌	0

英文单词示例：

SELECT
    'Hello' AS word,
    LENGTH('Hello') - LENGTH(REPLACE('Hello', 'l', '')) AS l_count;

word	l_count
Hello	2

结果解读：通过 LENGTH(word) - LENGTH(REPLACE(word, 'char', '')) 的差值来统计字符出现次数。原理是将目标字符替换为空后，字符串缩短的长度就是该字符出现的次数。

常见误区

误区	正解
`CONCAT` 遇到 NULL 会跳过	`CONCAT` 遇到 NULL 结果直接为 NULL，不会跳过。
`SUBSTRING` 从 0 开始	MySQL 字符串位置从1开始。`SUBSTRING(str, 0)` 返回空。
`LENGTH` 和 `CHAR_LENGTH` 一样	`LENGTH` 是字节长度，`CHAR_LENGTH` 是字符长度，多字节字符集下不同。

面试考点

Q：CONCAT 和 CONCAT_WS 的区别？

CONCAT 直接拼接多个参数，任一参数为 NULL 则结果为 NULL；CONCAT_WS 使用指定分隔符拼接，且会自动跳过 NULL 值。例如 CONCAT_WS(',', 'a', NULL, 'b') = 'a,b'。

Q：MySQL 5.7 如何拆分字符串？

没有内置 SPLIT 函数。可用 SUBSTRING_INDEX 按分隔符提取，或借助辅助数字表 + SUBSTRING_INDEX 实现多段拆分。MySQL 8.0 引入了 JSON_TABLE 更方便处理。

Q：如何统计字符串中某个字符出现的次数？

LENGTH(str) - LENGTH(REPLACE(str, 'x', ''))。例如统计 'l' 在 'Hello' 中出现次数：LENGTH('Hello') - LENGTH(REPLACE('Hello', 'l', '')) = 5 - 3 = 2。

小结

CONCAT 拼接字符串，注意 NULL 会传染；CONCAT_WS 可自动跳过 NULL
SUBSTRING、LEFT、RIGHT 用于截取，位置从 1 开始
LENGTH 是字节长度，CHAR_LENGTH 是字符长度
字符串处理在 SQL 层完成，减少网络传输和程序层计算

下一章引子：字符串函数处理文本，日期函数则处理时间——计算年龄、月份差、日期加减等需求都依赖它们。