企业官网建设流程全解析

做后台开发的这些年，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 并且抛 Warning；MAX(amount)是按字符串规则取最大值，而不是真正的数值最大值；AVG(amount)更是被字符串中的脏字符拉偏。更隐蔽的是 WHERE 条件参与比较时，'100' < '20'会返回真，因为字符串比较是逐字符比大小，'1'小于'2'，这跟数字比较完全不是一个逻辑。

还有一类坑出现在 JOIN 上。两张表分别用 varchar 存同一个“业务编号”，一张表的编号是'0123'，另一张是'123'，字符串等值匹配时'0123'不等于'123'，关联结果直接少一批。这个问题在后面清洗时会特别明显，我在第 4 章会单独讲。

1.3 垃圾数据的真实长相

Varchar 数值字段里的垃圾数据，种类比我预想的多。我整理过一组线上表常见的脏数据，基本逃不出下面这些：

光看这些还不够，实际数据里还会混着'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 字段返回的是 NULL，NULL 不会被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)根本去不掉换行，需要用嵌套 REPLACE：

UPDATE 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，想保留语义，就单独刷 NULL：

UPDATE order_info SET amount = NULL WHERE amount = '' OR amount = 'N/A' OR amount = '未知';

3.4 空字符串和NULL的统一收口

清洗的最后一步，是把空字符串和 NULL 两种“空状态”收敛成一种。因为业务查询里经常写WHERE amount IS NULL或WHERE amount = ''，两边写法不一致，查出来的结果就差一截。

如果你希望统一为 NULL：

UPDATE 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.3'，ALTER 会直接报错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 先验证，等线上数据稳定，再抽时间把应用层的校验也补上。这样一次清洗之后，后面基本不用再为同样的垃圾数据头疼。