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中常用的字符串函数，它们是处理文本数据的瑞士军刀，能帮我们完成拼接、截取、查找、替换、格式化等一系列操作。理解并熟练运用这些函数，对于数据清洗、报表生成乃至日常的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;
在我看来，这些看似简单的函数，组合起来就能解决大部分数据“脏”的问题。关键在于你对数据的理解，以及如何巧妙地运用这些工具。
提升查询效率：字符串函数与索引的那些事儿
说到字符串函数，就不得不提它们对查询性能的影响，尤其是和索引的关系。这块儿我踩过不少坑，也总结了一些经验。
核心思想是：对索引列使用函数，通常会导致索引失效。
举个例子，如果你在
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]*$';
虽然正则表达式功能强大，但它的性能开销通常比简单的字符串匹配要大得多，并且无法利用索引。所以，只有在标准函数无法满足需求时，才考虑使用它。
3. 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的字符串函数提供了非常丰富的功能，能够应对各种文本处理挑战。但就像任何工具一样，理解其工作原理、性能影响和适用场景，才能真正发挥它们的威力。尤其是在处理大量数据或高并发场景下，对函数选择和索引策略的权衡，往往决定了你的应用性能上限。
