如何检测MySQL的mediumtext字段是否实际存在有效非空内容

你遇到的情况完全不需要怀疑数据存储异常，本质是两个常见问题叠加：

• MySQL默认的TRIM()处理范围极窄，仅会移除字段首尾ASCII值为32的普通半角空格，对全角空格、制表符、换行符、空字节、零宽字符、不间断空格这类肉眼完全看不到的字符，没有任何处理效果。
• 当字段连续存入16000个这类不可见字符时，数据库可视化工具预览时会把全是空白的内容渲染为空白，看起来和空值完全没有区别，但实际字符长度会被准确统计为16000。
• 固定的16000长度基本都是文本写入时的截断逻辑导致的：不少上传组件、富文本编辑器在写入超出长度限制的内容时，会把截断点落在连续的空白字符段上，最终存进数据库的全是没有业务价值的占位空白，自然看不到任何有效内容。

1. 全量剔除不可见字符后统计有效长度 #

直接运行以下查询，把所有常见不可见冗余字符全部替换为空后再计算长度：

SELECT
  CHAR_LENGTH(content) AS 原始存储长度,
  CHAR_LENGTH(
    TRIM(
      REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(
          content,
          CHAR(12288), ''), -- 全角空格
          CHAR(160), ''),   -- 不间断空格
          CHAR(9), ''),     -- 制表符
          CHAR(10), ''),    -- 换行符
          CHAR(13), ''),    -- 回车符
          CHAR(0), ''),     -- 空字节
          CHAR(8203), ''),  -- 零宽空格
          CHAR(65279), '')  -- 文件头BOM标识
    )
  ) AS 有效可见内容长度
FROM files_table
WHERE CHAR_LENGTH(content) = 16000;

如果查询结果里的有效可见内容长度为0，就证明该字段没有任何有效业务文本，全是不可见冗余占位。

2. 确认不可见字符的具体类型 #

如果替换后仍有剩余长度，可以提取字段前100个字符的十六进制编码，直接定位存的到底是什么内容：

SELECT
  HEX(SUBSTRING(content, 1, 100)) AS 前100字符十六进制值
FROM files_table
WHERE CHAR_LENGTH(content) = 16000;

拿到的十六进制值如果全部对应上面列的不可见字符编码，就可以100%确认内容无有效价值。

确认字段全是冗余不可见字符后，可以直接按业务需求将字段值更新为NULL或空字符串：

UPDATE files_table
SET content = NULL -- 业务需要保留空字符串的话，替换为 SET content = ''
WHERE CHAR_LENGTH(
    TRIM(
      REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(
          content,
          CHAR(12288), ''),
          CHAR(160), ''),
          CHAR(9), ''),
          CHAR(10), ''),
          CHAR(13), ''),
          CHAR(0), ''),
          CHAR(8203), ''),
          CHAR(65279), '')
    )
  ) = 0;

• 不要依赖数据库可视化工具的预览结果判断字段是否为空：工具对超长空白内容会直接折叠渲染为空白，不会逐字符展示内部的不可见字符，很容易出现视觉判断和实际存储值的偏差。
• 不要单独用普通TRIM()做空值判断：普通TRIM的处理范围太窄，之前使用的查询逻辑本身存在漏洞，才会出现筛选出“长度大于0但实际无内容”的记录。
• 不要把LENGTH()和CHAR_LENGTH()的结果混淆：LENGTH()统计的是字节长度，CHAR_LENGTH()统计的是字符个数，对于多字节字符集的字段，两个函数结果会有差异，但只要字段里全是不可见字符，两个函数的返回结果都会对应计算出总长度，不会识别内容是否有效。

内容的提问来源于stack exchange，提问作者Arpit Gupta