mysql中的字符串函数与文本数据处理

mysql字符串函数常见坑：CONCAT遇NULL返回NULL，需用COALESCE兜底；LEFT/SUBSTRING越界行为不同，LOCATE失败易误取整串；REPLACE无正则，REGEXP_REPLACE仅8.0.4+支持；GROUP_CONCAT默认长度限1024且无声截断。

MySQL 字符串函数最常踩的坑是隐式类型转换

MySQL 在遇到字符串函数作用于数字字段或混合类型时，会自动做隐式转换，比如 CONCAT(123, 'abc') 看似没问题，但若字段是 int 且值为 NULL，CONCAT(col, '_suffix') 整个结果就变成 NULL——因为只要任一参数为 NULL，CONCAT() 就返回 NULL。这不是 bug，是设计行为。

实际处理文本数据前，务必检查空值：用 COALESCE(col, '') 或 ifNULL(col, '') 做兜底；对数字字段转字符串，显式用 CAST(col AS char) 或 CONVERT(col, CHAR)，避免依赖自动转换带来的歧义。

LEFT、SUBSTRING、SUBSTR 的行为差异与截断风险

LEFT(str, len) 和 SUBSTRING(str, pos, len)（SUBSTR 是别名）在越界时表现不同：LEFT('abc', 10) 安全返回 'abc'；而 SUBSTRING('abc', 2, 10) 也安全，但 SUBSTRING('abc', 10, 1) 返回空字符串 ''，不是 NULL。这点在分词、取后缀等逻辑中容易误判。

常见错误场景：

• 用 SUBSTRING(url, LOCATE('/', url) + 1) 提取路径，但没考虑 LOCATE() 找不到时返回 0，导致 SUBSTRING(url, 1) 取了整串——应加条件判断：IF(LOCATE('/', url) > 0, SUBSTRING(url, LOCATE('/', url) + 1), url)
• 用 LEFT(title, 50) 做摘要，但中文字符在 utf8mb4 下占 3–4 字节，而 LEFT 按「字符数」而非字节数截取，所以放心用；但若后续导出到某些旧系统（如只支持 latin1），再按字节截可能乱码——确认字符集统一比纠结函数更重要

REPLACE 和 REGEXP_REPLACE 的性能与兼容性断层

REPLACE(str, from_str, to_str) 是全版本支持的简单替换，不支持正则；REGEXP_REPLACE() 从 MySQL 8.0.4 才引入，语法类似 REGEXP_REPLACE(str, pattern, replacement)，但默认使用 POSIX ERE，不支持懒匹配、反向引用需用 1（双反斜杠），且无法开启 i 标志（大小写不敏感）除非升级到 8.0.22+ 并启用 regexp_stack_limit 等配置。

线上环境若仍跑 MySQL 5.7，别试图用 REGEXP_REPLACE——它不存在，会报错 function xxx.REGEXP_REPLACE does not exist。替代方案只能是嵌套多层 REPLACE()，或在应用层处理。

select    REPLACE(REPLACE(REPLACE(content, '<', 'zuojiankuohaophpcn'), '>', 'youjiankuohaophpcn'), '&', '&') AS escaped FROM posts;

GROUP_CONCAT 处理多值聚合时的隐式限制

GROUP_CONCAT() 常用于拼接标签、分类等文本集合，但它有默认长度限制：group_concat_max_len 系统变量默认仅 1024 字符。一旦拼接结果超长，就会被无声截断，不报错也不警告。

排查方法：

• 查当前设置：SELECT @@group_concat_max_len;
• 临时调高（会话级）：SET session group_concat_max_len = 1000000;
• 永久修改需在 my.cnf 中加 group_concat_max_len = 1000000
• 注意：即使调高，GROUP_CONCAT(DISTINCT tag ORDER BY tag SEPARATOR ', ') 仍可能因排序+去重消耗较多内存，大数据量下建议加索引或改用 jsON 聚合（MySQL 5.7+ 的 json_ARRAYAGG()）

字符集也要留心：如果字段是 utf8mb4，但 GROUP_CONCAT 结果被赋给 VARCHAR(255) 变量，超长部分照样被砍——长度限制是按字符数算的，不是字节数。