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

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()

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

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

1. 预处理数据: 如果你的业务场景允许，可以在数据写入时就将其标准化。比如，所有用户名都存为小写，查询时也用小写，这样

   WHERE user_name = 'john doe'

   就能走索引了。

2. 创建函数索引（虚拟列/生成列）: 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

   。

3. 避免在

   WHERE

   子句左侧使用函数: 尽量将函数操作放在等号的右侧。

   -- 慢：LOWER(user_name) = 'john' -- 优：user_name = UPPER('john') -- 假设user_name存的是大写

4. LIKE 'prefix%'

   可以走索引: 如果你的查询是

   WHERE column LIKE 'abc%'

   ，那么这个查询是可以使用索引的，因为MySQL可以根据前缀匹配。但如果是

   LIKE '%abc'

   或

   LIKE '%abc%'

   ，那索引就无能为力了，因为它无法确定从哪里开始扫描。

5. 考虑全文索引: 对于复杂的文本搜索，比如模糊匹配、关键词搜索，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的字符串函数提供了非常丰富的功能，能够应对各种文本处理挑战。但就像任何工具一样，理解其工作原理、性能影响和适用场景，才能真正发挥它们的威力。尤其是在处理大量数据或高并发场景下，对函数选择和索引策略的权衡，往往决定了你的应用性能上限。