MySQL中常用的字符串函数有哪些 MySQL字符串函数全解析与应用技巧-mysql教程-PHP中文网

length返回字节长度，char_length返回字符数，处理多字节字符时需注意区别；2. concat用于拼接字符串，concat_ws可指定分隔符并忽略null值；3. substring用于截取子串，支持正负位置参数；4. replace全局替换子串，instr/locate查找子串位置；5. trim/ltrim/rtrim去除空格或指定字符；6. upper/lower转换大小写；7. lpad/rpad用于左右填充；8. find_in_set在逗号分隔列表中查找值，但性能差且不推荐用于规范化设计；9. 数据清洗中常组合使用trim、lower、replace等函数统一格式；10. 在索引列上使用函数会导致索引失效，应通过预处理数据或创建生成列索引优化；11. like 'prefix%'可走索引，而'%abc'或'%abc%'不能；12. 多字节字符集下应使用char_length和substring按字符操作；13. regexp/rlike支持正则匹配但性能较低且无法使用索引；14. find_in_set适用于简单场景，但应优先采用关联表实现多值属性；15. convert和cast可用于字符集转换，适用于数据迁移或同步场景；熟练掌握这些函数及其性能影响，能有效提升数据处理效率与查询性能，是mysql文本处理的核心技能。

MySQL中常用的字符串函数有哪些 MySQL字符串函数全解析与应用技巧

MySQL中常用的字符串函数，它们是处理文本数据的瑞士军刀，能帮我们完成拼接、截取、查找、替换、格式化等一系列操作。理解并熟练运用这些函数，对于数据清洗、报表生成乃至日常的SQL查询效率都有着不可小觑的影响。说实话，很多时候数据源并不那么“干净”，字符串函数就是我们去“打磨”它们的利器。

解决方案

谈到MySQL的字符串函数，我个人觉得它们可以大致分为几类，这样理解起来会更有条理。

1. 基础操作：长度与连接

```
LENGTH(str)
```
登录后复制
和
CHAR_LENGTH(str)
登录后复制
: 这俩常常让人混淆。
```
LENGTH
```
登录后复制
返回的是字符串的字节长度，而
```
CHAR_LENGTH
```
登录后复制
返回的是字符数。在处理多字节字符集（比如UTF-8）时，这个区别就非常关键了。一个汉字在UTF-8里通常占3个字节，那么
```
LENGTH('你好')
```
登录后复制
会是6，而
```
CHAR_LENGTH('你好')
```
登录后复制
则是2。我以前就因为没注意这个，导致截取出来的中文内容乱码，踩过不少坑。
```
SELECT LENGTH('Hello World'), CHAR_LENGTH('Hello World');
-- 结果：11, 11
SELECT LENGTH('你好世界'), CHAR_LENGTH('你好世界');
-- 结果：12, 4 (假设UTF-8编码)
```
登录后复制
```
CONCAT(str1, str2, ...)
```
登录后复制
和
CONCAT_WS(separator, str1, str2, ...)
登录后复制
:
```
CONCAT
```
登录后复制
就是简单的字符串拼接，有多少个参数就拼多少个。
```
CONCAT_WS
```
登录后复制
（Concatenate With Separator）则更方便，它会在每个字符串之间插入一个指定的分隔符，并且会自动跳过NULL值，这在生成地址或完整姓名时特别好用。
```
SELECT CONCAT('Hello', ' ', 'World');
-- 结果：Hello World
SELECT CONCAT_WS('-', '2023', '10', '26');
-- 结果：2023-10-26
SELECT CONCAT_WS('-', 'First', NULL, 'Last');
-- 结果：First-Last
```
登录后复制

2. 内容处理：查找与替换

SUBSTRING(str, pos, len)

登录后复制

或
SUBSTR(str, pos, len)
登录后复制
: 从字符串中提取子串。

pos

登录后复制

是起始位置（1开始），

len

登录后复制

是长度。如果

pos

登录后复制

是负数，则从字符串末尾开始计数。

SELECT SUBSTRING('MySQL Functions', 5, 9);
-- 结果：L Function
SELECT SUBSTRING('MySQL Functions', -9); -- 从倒数第9个字符开始到结束
-- 结果：Functions

登录后复制

```
REPLACE(str, from_str, to_str)
```
登录后复制
: 替换字符串中所有出现的子串。这在数据清洗中简直是神来之笔，比如把所有旧的URL前缀替换成新的。
```
SELECT REPLACE('Hello World', 'World', 'MySQL');
-- 结果：Hello MySQL
```
登录后复制
```
INSTR(str, substr)
```
登录后复制
或
LOCATE(substr, str)
登录后复制
: 查找子串在字符串中第一次出现的位置。
```
INSTR
```
登录后复制
是
```
str
```
登录后复制
在前，
```
substr
```
登录后复制
在后；
```
LOCATE
```
登录后复制
是
```
substr
```
登录后复制
在前，
```
str
```
登录后复制
在后，功能一样。如果找不到，返回0。
```
SELECT INSTR('MySQL Functions', 'Func');
-- 结果：7
SELECT LOCATE('Func', 'MySQL Functions');
-- 结果：7
```
登录后复制

LEFT(str, len)

登录后复制

和
RIGHT(str, len)
登录后复制
: 从字符串左边或右边截取指定长度的子串。

SELECT LEFT('MySQL Functions', 5);
-- 结果：MySQL
SELECT RIGHT('MySQL Functions', 9);
-- 结果：Functions

登录后复制

3. 格式化与清理

TRIM([BOTH | LEADING | TRAILING] [remstr FROM] str)

登录后复制

: 去除字符串两端、前端或后端指定字符（默认是空格）。

LTRIM

登录后复制

和

RTRIM

登录后复制

是去除左边或右边的空格。

SELECT TRIM('   Hello World   ');
-- 结果：Hello World
SELECT LTRIM('   Hello World   ');
-- 结果：Hello World   
SELECT TRIM(LEADING 'x' FROM 'xxxHello Worldxxx');
-- 结果：Hello Worldxxx

登录后复制

```
UPPER(str)
```
登录后复制
和
LOWER(str)
登录后复制
: 转换为大写或小写。在进行不区分大小写的比较时，或者需要统一数据格式时非常有用。
```
SELECT UPPER('hello world');
-- 结果：HELLO WORLD
SELECT LOWER('HELLO WORLD');
-- 结果：hello world
```
登录后复制
```
LPAD(str, len, padstr)
```
登录后复制
和
RPAD(str, len, padstr)
登录后复制
: 在字符串左边或右边填充指定字符，直到达到指定长度。常用于编号补零。
```
SELECT LPAD('123', 5, '0');
-- 结果：00123
```
登录后复制
```
FIND_IN_SET(str, strlist)
```
登录后复制
: 在一个逗号分隔的字符串列表中查找某个字符串。这个函数虽然方便，但在设计数据库时，如果能避免用逗号分隔的字符串来存储多值，我个人会更倾向于使用关联表，因为
```
FIND_IN_SET
```
登录后复制
的性能开销和索引利用率是个问题。
```
SELECT FIND_IN_SET('apple', 'apple,banana,orange');
-- 结果：1
```
登录后复制

字符串函数在数据清洗与格式化中的实践

在实际工作中，数据往往不会像教科书里那么规整。字符串函数在数据清洗和格式化方面，简直是我们的救星。我举几个常见的例子：

想象一下，你从不同系统导入的用户数据，有的姓名是“张三”，有的是“zhang san”，还有的是“ 张三 ”。这时候，我们可能需要统一格式：

-- 去除多余空格并统一大小写
UPDATE users
SET user_name = TRIM(user_name),
    email = LOWER(TRIM(email));

登录后复制

再比如，你有一列电话号码，格式五花八门，有的是“13812345678”，有的是“+86-138-1234-5678”，你只想提取纯数字：

-- 假设我们需要移除所有非数字字符
-- 这是一个比较复杂的场景，可能需要多次REPLACE或配合正则表达式
-- MySQL 8.0+ 支持 REGEXP_REPLACE，更强大
-- 假设我们只移除连字符和空格
UPDATE contacts
SET phone_number = REPLACE(REPLACE(phone_number, '-', ''), ' ', '');

登录后复制

还有一种情况，商品描述里混入了HTML标签，或者需要截取前N个字符作为简介：

-- 截取前100个字符作为简介，并确保多字节字符不被截断一半
-- 这里CHAR_LENGTH就派上用场了
SELECT SUBSTRING(description, 1, 100) FROM products;

登录后复制

在我看来，这些看似简单的函数，组合起来就能解决大部分数据“脏”的问题。关键在于你对数据的理解，以及如何巧妙地运用这些工具。

提升查询效率：字符串函数与索引的那些事儿

说到字符串函数，就不得不提它们对查询性能的影响，尤其是和索引的关系。这块儿我踩过不少坑，也总结了一些经验。

怪兽AI数字人

数字人短视频创作，数字人直播，实时驱动数字人

查看详情

核心思想是：对索引列使用函数，通常会导致索引失效。

举个例子，如果你在

user_name

登录后复制

列上建了索引，然后你写了这样的查询：

SELECT * FROM users WHERE LOWER(user_name) = 'john doe';

登录后复制

很遗憾，即使

user_name

登录后复制

有索引，MySQL也无法直接利用它。因为它需要对

user_name

登录后复制

列的每一行数据先执行

LOWER()

登录后复制

操作，然后才能进行比较，这相当于全表扫描。

那么，有没有办法既使用函数又利用索引呢？

预处理数据: 如果你的业务场景允许，可以在数据写入时就将其标准化。比如，所有用户名都存为小写，查询时也用小写，这样
```
WHERE user_name = 'john doe'
```
登录后复制
就能走索引了。
创建函数索引（虚拟列/生成列）: MySQL 5.7.6+ 引入了生成列（Generated Columns）。你可以创建一个虚拟列，这个虚拟列的值是基于其他列计算出来的，并且可以给这个虚拟列加索引。
```
ALTER TABLE users ADD COLUMN user_name_lower VARCHAR(255) GENERATED ALWAYS AS (LOWER(user_name)) STORED;
CREATE INDEX idx_user_name_lower ON users (user_name_lower);
-- 这样查询就能走索引了
SELECT * FROM users WHERE user_name_lower = 'john doe';
```
登录后复制
```
STORED
```
登录后复制
表示这个列的值会实际存储在表中，占用空间但查询更快；
```
VIRTUAL
```
登录后复制
则不存储，每次查询时实时计算，节省空间但可能慢一点。通常，为了索引，我们会选择
```
STORED
```
登录后复制
。
避免在
WHERE
登录后复制
子句左侧使用函数: 尽量将函数操作放在等号的右侧。
```
-- 慢：LOWER(user_name) = 'john'
-- 优：user_name = UPPER('john') -- 假设user_name存的是大写
```
登录后复制
LIKE 'prefix%'
登录后复制
可以走索引: 如果你的查询是
```
WHERE column LIKE 'abc%'
```
登录后复制
，那么这个查询是可以使用索引的，因为MySQL可以根据前缀匹配。但如果是
```
LIKE '%abc'
```
登录后复制
或
```
LIKE '%abc%'
```
登录后复制
，那索引就无能为力了，因为它无法确定从哪里开始扫描。
考虑全文索引: 对于复杂的文本搜索，比如模糊匹配、关键词搜索，MySQL的全文索引（
```
FULLTEXT
```
登录后复制
）是更好的选择，它专门为这类场景优化。

总之，在使用字符串函数时，多想一步：它会不会让我的索引失效？如果会，有没有其他办法来优化？这才是我们作为开发者需要深入思考的地方。

字符串函数进阶：处理复杂文本与多语言字符集

除了前面提到的基本操作，MySQL的字符串函数在处理更复杂文本和多语言字符集时，还有一些值得深入探讨的地方。

1. 多字节字符集（UTF-8等）的陷阱与应对

前面提到了

LENGTH

登录后复制

和

CHAR_LENGTH

登录后复制

的区别，这在处理包含中文、日文、韩文等字符的数据时尤其重要。如果你的数据库字符集是

utf8

登录后复制

或

utf8mb4

登录后复制

（推荐

utf8mb4

登录后复制

，因为它支持所有Unicode字符，包括emoji），那么：

截取操作：
```
SUBSTRING
```
登录后复制
在处理多字节字符时，
```
len
```
登录后复制
参数指的是字符数，而不是字节数。这很好，避免了截断半个字符的情况。
排序与比较：默认情况下，MySQL的字符串比较是基于字符集的排序规则（Collation）。如果你遇到大小写不敏感或特定语言的排序问题，可以显式指定Collation。例如，
```
COLLATE utf8mb4_unicode_ci
```
登录后复制
表示不区分大小写和重音的Unicode排序。

2. 正则表达式：

REGEXP

登录后复制

与
RLIKE
登录后复制

MySQL提供了

REGEXP

登录后复制

和

RLIKE

登录后复制

（它们是同义词）来进行正则表达式匹配。这比

LIKE

登录后复制

更强大，能处理更复杂的模式匹配需求。

-- 查找所有包含数字的用户名
SELECT user_name FROM users WHERE user_name REGEXP '[0-9]';

-- 查找以字母开头，后面跟着任意数量数字的字符串
SELECT data FROM my_table WHERE data REGEXP '^[a-zA-Z][0-9]*$';

登录后复制

虽然正则表达式功能强大，但它的性能开销通常比简单的字符串匹配要大得多，并且无法利用索引。所以，只有在标准函数无法满足需求时，才考虑使用它。

FIND_IN_SET

登录后复制

的优缺点与替代方案

FIND_IN_SET

登录后复制

用于在逗号分隔的字符串中查找一个值，这在某些遗留系统或非规范化数据中很常见。

-- 查找爱好列表中包含“阅读”的用户
SELECT user_name FROM users WHERE FIND_IN_SET('阅读', hobbies);

登录后复制

它的优点是方便，但缺点也明显：

性能差：无法使用索引，每次查询都需要扫描整个字符串列表。
数据完整性差：无法保证列表中的值是有效的，容易出现重复或错误数据。
扩展性差：如果需要查询多个值，或者进行更复杂的关联，会变得非常麻烦。

我个人强烈建议，如果可以，尽量将这种多值属性进行规范化，拆分成独立的关联表。比如，

users

登录后复制

表和

user_hobbies

登录后复制

关联表，

user_hobbies

登录后复制

表存储

user_id

登录后复制

和

hobby_id

登录后复制

。这样不仅查询效率更高（可以利用索引），数据管理也更清晰。

4. 字符集转换函数：

CONVERT

登录后复制

和
CAST
登录后复制

在不同字符集之间进行转换时，

CONVERT

登录后复制

和

CAST

登录后复制

函数非常有用。

-- 将字符串从一种字符集转换为另一种
SELECT CONVERT('你好' USING utf8mb4);

登录后复制

这在处理跨系统数据同步，或者旧数据迁移到新字符集时会用到。

总的来说，MySQL的字符串函数提供了非常丰富的功能，能够应对各种文本处理挑战。但就像任何工具一样，理解其工作原理、性能影响和适用场景，才能真正发挥它们的威力。尤其是在处理大量数据或高并发场景下，对函数选择和索引策略的权衡，往往决定了你的应用性能上限。

以上就是MySQL中常用的字符串函数有哪些 MySQL字符串函数全解析与应用技巧的详细内容，更多请关注php中文网其它相关文章！

大家都在看：

mysql离线安装如何导出导入数据库_mysql离线环境数据库迁移操作方法 mysql归档表如何分区_mysql归档表分区设置与优化技巧 mysql导入sql文件的方法是什么_mysql导入sql文件的具体操作步骤详解 mysql导入sql文件能导入事件吗_mysql导入包含事件的sql文件注意事项 mysql触发器可以记录变更吗_mysql触发器记录数据变更信息的实现