做后台开发的这些年varchar存数值这种“历史债”我见过太多次了。比如订单表的金额字段是 varchar(20)会员积分是 varchar(10)甚至年龄都写成 varchar(2)表没用几个月线上数据就乱成一锅粥有的是前导零有的带人民币符号有的干脆把“未知”两个字存了进去。你要做统计时SUM 出来的结果怎么都对不上账。这就是典型的 mysql 数据清洗场景字段类型选错了垃圾数据留下来了最后只能靠 SQL 一点一点清。这篇文章就是围绕“varchar 存储数值型数据”这个坑讲清楚垃圾数据是怎么产生的、怎么用 SQL 定位和清洗、清洗过程中哪些操作最危险以及最后怎么把字段一劳永逸地改成真正的数值类型。适合有 MySQL 基础、正在处理历史数据或接手老旧项目的同学看。我不写空话全是我自己在实际操作里验证过的写法。1. varchar装数值麻烦从第一天就埋下了1.1 为什么这么多表喜欢用varchar存数值按说设计表结构的时候金额就该用 DECIMAL数量就该用 INT但现实中大量表还是把数值塞进了 varchar。原因无非几种当初接口对接时上游返回的字段就是字符串开发为了省事直接建 varchar 字段存下来。导入 Excel 或 CSV 时工具默认把所有列识别成文本建表脚本也跟着生成 varchar。历史表设计比较随意字段语义不清楚后接手的人不敢乱改。有些系统为了兼容“带单位”“带符号”的展示字符串故意用 varchar比如把“1,200元”直接存进去。这些问题在前期数据量小、业务逻辑简单时不容易暴露。可一旦开始做汇总报表、对账、排序、关联查询varchar 的“灵活”就变成灾难。所谓“能用就行”最后往往要花几倍精力去填坑。1.2 排序、比较、聚合的“串”式陷阱varchar 存数值最直观的痛点是排序。字符串排序是按字符逐位比较的ORDER BY amount DESC出来可能是9排在80前面因为第一个字符9比8大。你要是给用户展示“金额从高到低”等于直接放了个错误榜单。聚合函数也有坑。SUM(amount)遇到非数字字符串时MySQL 会给 0 并且抛 WarningMAX(amount)是按字符串规则取最大值而不是真正的数值最大值AVG(amount)更是被字符串中的脏字符拉偏。更隐蔽的是 WHERE 条件参与比较时100 20会返回真因为字符串比较是逐字符比大小1小于2这跟数字比较完全不是一个逻辑。还有一类坑出现在 JOIN 上。两张表分别用 varchar 存同一个“业务编号”一张表的编号是0123另一张是123字符串等值匹配时0123不等于123关联结果直接少一批。这个问题在后面清洗时会特别明显我在第 4 章会单独讲。1.3 垃圾数据的真实长相Varchar 数值字段里的垃圾数据种类比我预想的多。我整理过一组线上表常见的脏数据基本逃不出下面这些脏数据类型示例产生原因首尾空格 123 手工录入、Excel 导入换行回车123\n复制粘贴、接口拼接千分位1,200前端展示格式被回写货币符号1200、$1200业务拼接前导零0123流水号、编号格式全角数字中文输入法非可见字符不间断空格、制表符网页表单、第三方接口纯文字未知、N/A业务异常时的兜底文案多小数点1.2.3拼接错误、爬虫数据空字符串默认值问题、接口缺参光看这些还不够实际数据里还会混着0.00、-、null这种“看起来像值”的值。所以清洗 varchar 数值数据第一步不是上来 UPDATE而是先把数据摸底摸清楚哪些能修、哪些只能置零、哪些需要人工确认都得分清楚。2. 清洗之前先给表做一次“全身CT”2.1 第一步永远是把备份留好清洗前不做备份等于不系安全带开车。哪怕你 UPDATE 语句写得再小心也保不齐某个正则表达式在 MySQL 8 和 MySQL 5.7 里表现不一样或者某个脏数据超出了你的预期。最稳的做法是建一张临时备份表把原表全量复制过去-- 先复制表结构 CREATE TABLE order_info_bak_20250101 LIKE order_info; -- 再复制数据 INSERT INTO order_info_bak_20250101 SELECT * FROM order_info; -- 核对一下行数 SELECT COUNT(*) AS 原表行数 FROM order_info; SELECT COUNT(*) AS 备份表行数 FROM order_info_bak_20250101;如果表非常大INSERT ... SELECT 会拖很久可以考虑mysqldump只导这张表mysqldump -u username -p database_name order_info order_info_bak.sql备份的目的不只是为了回滚更重要的是给你一个“清洗前后对照”的基准。后面做数据复盘时两张表对比一下就能知道每条数据被改成了什么。2.2 用条件查询给垃圾数据分类备份做完后先用 SELECT 把垃圾数据找出来不要急着改。varchar 数值字段里严格来说只有两种数据能算“合法数值”整数或者带小数点的正负数。所以第一轮筛查的基本条件可以用这个正则SELECT id, amount FROM order_info WHERE amount NOT REGEXP ^[0-9](\\.[0-9])?$;MySQL 的正则引擎是 POSIX 风格不支持\d所以这里用[0-9]更保险要允许负数可以改成^-?[0-9](\\.[0-9])?$。注意 MySQL 8.0 的REGEXP默认不区分大小写对数字纯筛没影响。实际执行时会发现NOT REGEXP查出来的结果可能比你想象的少因为 MySQL 的REGEXP遇到 NULL 字段返回的是 NULLNULL 不会被NOT REGEXP判定为 True。所以空值和 NULL 要单查SELECT id, amount FROM order_info WHERE amount IS NULL OR amount ;2.3 统计垃圾数据的规模与分布筛查出候选垃圾后最好再分个类弄清楚脏数据的“成分比例”。我常用 CASE WHEN 配合正则做一次分组统计SELECT CASE WHEN amount IS NULL THEN NULL WHEN amount THEN 空字符串 WHEN amount REGEXP ^[0-9](\\.[0-9])?$ THEN 合法数值 WHEN amount REGEXP [a-zA-Z] THEN 包含字母 WHEN amount REGEXP [-] THEN 包含全角数字 WHEN amount LIKE %,% THEN 包含千分位 ELSE 其他 END AS 脏数据分类, COUNT(*) AS 数量 FROM order_info GROUP BY 脏数据分类 ORDER BY 数量 DESC;这一步看着不起眼但它决定了你的清洗策略。比如“包含全角数字”能通过替换函数修回来“包含字母”很可能只能置默认值“其他”里可能藏着不同换行符组合要单独拉出来看原始 HEX 编码。分类统计做得好后面写 UPDATE 逻辑时就不会眉毛胡子一把抓。3. 从易到难四种清洗手法的组合拳3.1 肉眼级垃圾空格、制表符、换行、回车最外层垃圾一般不可见肉眼看到的是“好像有个空格”但直接 TRIM 又不够。比如 Excel 表格里导入的字符串经常带\r\n换行手动敲进去的数据可能带制表符 ASCII 9。只写TRIM(amount)根本去不掉换行需要用嵌套 REPLACEUPDATE order_info SET amount TRIM( REPLACE( REPLACE( REPLACE( REPLACE(amount, CHAR(9), ), CHAR(10), ), CHAR(13), ) ) );CHAR(9)是制表符CHAR(10)是换行CHAR(13)是回车。TRIM 在最外层把普通空格去掉。这个写法可以处理 90% 的不可见字符。还有一类是不间断空格CHAR(160)它经常从网页端复制进表单TRIM 也认不出来。需要在后面再接一层替换UPDATE order_info SET amount REPLACE(amount, CHAR(160), ) WHERE amount LIKE CONCAT(%, CHAR(160), %);注意这些更新做的时候最好加上 WHERE 条件即使全表都要更新也可以先限定只更新包含对应字符的行减少无效写操作和锁范围。3.2 格式残留全角数字、千分位、货币符号、前导零清理完不可见字符后处理“看起来像数字但不是标准数字”的数据。全角数字本质上是不同的 Unicode 码点MySQL 里可以用一串 REPLACE 逐字替换。比如把全角-全部转成半角UPDATE order_info SET amount REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(amount, ,0),,1),,2),,3),,4), ,5),,6),,7),,8),,9);这种写法看着啰嗦却是 MySQL 里最稳妥的通用方案兼容 5.7 和 8.0不依赖正则扩展包。千分位逗号和货币符号类似直接替换掉UPDATE order_info SET amount REPLACE(REPLACE(amount, ,, ), , );人民币符号在 utf8mb4 里是一个字符传进 SQL 时注意客户端字符集不然会变成问号导致替换无效。前导零要不要去掉取决于业务语义。如果这个字段将来要做数值运算0123变成123是合理的如果它是仓库编码、员工编号这类“看起来是数字”的字符串前导零是有效格式不能乱去。所以这里必须先确认字段的业务含义再决定要不要用CAST(amount AS UNSIGNED)或者amount 0去格式化。3.3 不能恢复的垃圾怎么处理有些垃圾数据小偷小摸能修比如-、N/A、未知、1.2.3这种别想太多清洗策略只有两条要么置为业务默认值要么置为 NULL。具体选哪个取决于下游统计逻辑。如果下游是用SUM(amount)汇总NULL 会被忽略但不会报错0 则会参与求和可能把平均值拉低如果下游直接拼接字符串展示NULL 可能导致页面显示空。我一般建议“业务上不存在的值置 NULL业务上确实为 0 的值置 0”区分对待。批量置默认值的写法UPDATE order_info SET amount 0 WHERE amount IS NULL OR amount OR amount NOT REGEXP ^[0-9](\\.[0-9])?$;这里把空字符串、NULL、非数字统一刷成0适合金额字段“默认 0”的场景。如果表结构允许 NULL想保留语义就单独刷 NULLUPDATE order_info SET amount NULL WHERE amount OR amount N/A OR amount 未知;3.4 空字符串和NULL的统一收口清洗的最后一步是把空字符串和 NULL 两种“空状态”收敛成一种。因为业务查询里经常写WHERE amount IS NULL或WHERE amount 两边写法不一致查出来的结果就差一截。如果你希望统一为 NULLUPDATE order_info SET amount NULL WHERE amount ;如果你希望统一为空字符串UPDATE order_info SET amount WHERE amount IS NULL;我个人的习惯是尽量统一成 NULL因为 NULL 在 SQL 里有明确的“不存在”语义聚合函数会自动忽略分组统计也更准确。空字符串在 WHERE 比较和 JOIN 时可能有意外行为少用为上。这一轮做完再用最开始的验证 SQL 查一遍SELECT COUNT(*) AS 清洗后非法数据量 FROM order_info WHERE amount NOT REGEXP ^[0-9](\\.[0-9])?$ OR amount IS NULL;正常情况下这条查询应该返回 0。如果有残留多半是某些特殊字符没被覆盖到需要拉出数据看 HEX 编码后再单独处理。4. 清洗现场最容易翻车的几个环节4.1 血泪教训漏写WHERE条件一次全表覆盖清洗数据最经典的翻车操作不是正则写错而是 UPDATE 语句漏了 WHERE。比如你想把空字符串刷成 NULL写成了UPDATE order_info SET amount NULL;这条语句一执行全表金额全部变 NULL连原本合法的99.00也一起被清掉。等你想回滚时如果没备份只能哭。所以我的建议是所有清洗 UPDATE 先写 SELECT 验证行数再改成 UPDATE每次 UPDATE 后立刻用 COUNT 和 SUM 对拍。比如执行更新的同时记录一下影响行数ROW_COUNT()-- 先验证 SELECT COUNT(*) FROM order_info WHERE amount ; -- 再更新 UPDATE order_info SET amount NULL WHERE amount ; -- 马上回查 SELECT COUNT(*) FROM order_info WHERE amount IS NULL;看到影响行数和预期一致才继续下一步。养成这个习惯能避免绝大多数低级事故。4.2 大表UPDATE锁等待、回滚日志和分批方案几万行的小表随便 UPDATE没什么问题到了几百万行一个大事务更新全表会带来几个连锁反应插入和更新同一行的其他业务被锁阻塞连接堆积。长时间持有 undo 日志磁盘占用飙升。如果事务回滚恢复时间比 UPDATE 本身还长。所以大表清洗不要一把梭。按主键 ID 分批更新是最简单有效的方式。比如每 10000 行一改UPDATE order_info SET amount TRIM(amount) WHERE id BETWEEN 1 AND 10000 AND amount LIKE % %;写完一批立刻提交再继续下一批。批次大小看表大小和服务器负载调节一般 5000~20000 行比较稳妥。这种方案比起一条大 UPDATE锁粒度小得多出错时影响面也小。另外要提醒一句ALTER TABLE是隐式提交的把 clean 和 MODIFY 字段类型放同一个事务里不会同时回滚。所以清洗和改类型尽量分成两步操作改类型前做好独立备份。4.3 关联表JOIN断裂这颗雷防不胜防清洗 varchar 数值字段时最容易被忽略的是关联表。举个例子会员表的member_code是 varchar(20)存了0123订单表的member_code也是 varchar(20)但订单系统的数据从另一个服务同步过来存的却是123。你只看一张表会以为两边都有合法数据但两张表 JOIN 时关联不上。如果清洗时把会员表的0123改成123那么原本能关联的数据反而可能因为两边格式不统一出问题。反过来如果你清掉前导零后发现订单报表缺了一块第一反应就是去检查有没有关联表。所以清洗任何“编码型 varchar 数值字段”前先查一下这张表被哪些表引用字段值在关联场景里是否必须严格等值。如果需要保留现有关联关系清洗时要同步处理相关表或者先确定统一的“数值正则化规则”再全链路一起改。还有一种选择是保留前导零不处理只把纯垃圾字段清掉。4.4 清洗后的数据质量复盘手段清洗完成不等于收工还要做一道“数据质量复盘”-- 清洗前备份表里的合法数值有多少 SELECT COUNT(*), SUM(CAST(amount AS DECIMAL(12,2))) FROM order_info_bak_20250101 WHERE amount REGEXP ^[0-9](\\.[0-9])?$; -- 清洗后原表的情况 SELECT COUNT(*), SUM(CAST(amount AS DECIMAL(12,2))) FROM order_info WHERE amount REGEXP ^[0-9](\\.[0-9])?$;如果清洗后的 SUM 比备份里的 SUM 突然多了几万块说明某些垃圾数据被当成 0 之外的值处理了要重新检查替换逻辑。如果 SUM 少了也说明可能有正常值被误清成 0。我还会随机抽查 100 行人工对比一下新旧值确认没有“合法数字被改错”的情况。这一步很土但最有用。5. 治本字段类型改造与写入端防线5.1 数据洗干净后把字段改成真正的数值类型清洗只解决存量问题如果字段类型不变过两天新的脏数据又会进来。所以等数据验收通过后马上做类型改造。金额用 DECIMAL整数用 INT 或 BIGINT量级很大的时才考虑 BIGINT别为了省空间用对不准的 FLOAT 和 DOUBLE。以订单金额举例ALTER TABLE order_info MODIFY COLUMN amount DECIMAL(12,2) NOT NULL DEFAULT 0;这一步执行前要确保amount里已经不存在无法转换的值。如果哪行还藏着1.2.3ALTER 会直接报错Truncated incorrect DECIMAL value终止修改。真遇到这种情况就用我第 3 章的清洗逻辑再过一轮。改成数值类型后之前所有的排序、聚合、JOIN 陷阱都消失了。ORDER BY amount DESC能按数学大小排SUM(amount)不会再因为abc变成 0。如果字段本身是整数编码类比如会员等级、状态码改成TINYINT或INT即可如果是金额、利率这种精确数值必须用DECIMAL。5.2 应用层校验从源头不产生脏数据类型改造只能拦数据库这一层真正脏数据的入口还在应用层。我见过太多改造完字段类型第二天服务端接口又往下游写字符串“未知”进去然后数据库直接给你一个Incorrect decimal value: 未知的报错。所以要双管齐下。应用层的做法很简单接收参数时做类型校验比如 Java 里用BigDecimal接收金额参数Python 里用float()判断是否能转数值不能通过校验就返回参数错误而不是把垃圾写进库。这个校验要在每个写入入口都做包括新增接口、批量导入、定时任务。如果历史接口一时半会儿改不完我建议在 DAO 层加一个“写入前清洗”的拦截比如把所有非数字字符替换掉或者丢弃非法值。这种做法虽然不算干净但至少能保证数据库里不再积累垃圾。5.3 SQL_MODE与触发器的最后防线MySQL 自带的 SQL 模式里STRICT_TRANS_TABLES和NO_ZERO_DATE能对不合法数值做一部分拦截。开启STRICT_TRANS_TABLES后插入abc到 DECIMAL 字段会直接报错而不是被截断成 0。这层防线对 DML 直接操作数据库的人有效。查一下当前模式SELECT sql_mode;一般建议在配置里至少包含STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION。它会增加一部分写入失败率但失败总比脏数据进库强。如果不想改全局配置也可以对单个表加触发器在写入时强行校验DELIMITER // CREATE TRIGGER trg_order_info_before_insert BEFORE INSERT ON order_info FOR EACH ROW BEGIN IF NEW.amount IS NOT NULL AND NEW.amount REGEXP ^[0-9](\\.[0-9])?$ 0 THEN SET NEW.amount 0; END IF; END// DELIMITER ;不过触发器会增加写入开销而且逻辑分散在数据库层不好维护适合在应用层改造完成前的过渡期使用。最后再分享一个我做这类项目时的习惯清洗 varchar 数值字段永远先确认字段的业务语义再动 SQL。同样是0012在“数量”里可以丢前导零在“订单编号”里可能就是合法格式。备份、分类、清洗、复盘、改造类型五步走每一步都用 SELECT 先验证等线上数据稳定再抽时间把应用层的校验也补上。这样一次清洗之后后面基本不用再为同样的垃圾数据头疼。
