MySQL JSON 类型 NOT NULL 竟无法约束 NULL 值插入？
MySQL JSON 类型 NOT NULL 竟无法约束 NULL 值插入？
作者：孙绪宗，新浪微博 DBA 团队工程师，主要负责 MySQL、PostgreSQL 等关系型数据库运维。
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
本文约 1200 字，预计阅读需要 3 分钟。
故障现象
业务人员执行了 DELETE 操作，导致数据误删除，需要数据回滚。通过工具回滚时，发现回滚生成的 SQL 执行时报错：
ERROR 3140 (22032) at line 38454 in file: 'rollback.9591.sql': Invalid JSON text: "The document is empty." at position 0 in value for column 'life_band_dig_query.search_stats'.
起初，以为是 SQL 里的反斜杠导致数据被截断了，sed 完再导入 SQL 发现还是有问题。SQL 文件不小，表也很宽，很难检查问题，但还是试着分析数据，发现反斜杠是对的。汗。。。
又通过报错仔细分析对应字段 search_stats 的值，发现 JSON 字段类型回滚生成的 SQL 竟然是 '' （空）值！
- 版本：MySQL 5.7.21
- SQL_MODE: ''
故障分析
正向解析 binlog 生成 SQL 看并无问题，binlog 里记录的确实是 '' （空）值。
但是通过查看线上表数据发现，在 有 JSON NOT NULL 类型的约束下，对应的回滚 SQL 主键值竟然是 NULL 值！ 这和 binlog 里记录的 '' （空）值不一致。
正常写入 NULL 值，会触发约束报错，猜测可能是历史上有过 DDL 操作。
故障复现
将 SQL_MODE 设置为 ''，退出重连。（PS：切记重连生效！）
MySQL [xuzong]> create table q(id int ,age varchar() default NULL);
Query OK, 0 rows affected (0.0107 sec)
MySQL [xuzong]> insert into q values(,NULL);
Query OK, 1 row affected (0.0040 sec)
MySQL [xuzong]> select * from q;
+----+------+
| id | age |
+----+------+
| 1 | NULL |
+----+------+
1 row in set (0.0020 sec)
MySQL [xuzong]> alter table q modify age json not null ;
Query OK, 1 row affected, 1 warning (0.0164 sec)
# 注意到这里有 Warning，感觉这种情况应该是 Error 才对。
Records: 1 Duplicates: 0 Warnings: 1
Warning (code 1265): Data truncated for column 'age' at row 1
# 到这里就发现不对劲了，不仅有 null 值，而且还由 NULL 大写变成了小写。
MySQL [xuzong]> select * from q;
+----+------+
| id | age |
+----+------+
| 1 | null |
+----+------+
1 row in set (0.0020 sec)
MySQL [xuzong]> show create table q \G
*************************** 1.row ***************************
Table: q
Create Table: CREATE TABLE`q` (
`id` int() DEFAULT NULL,
`age` json NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
rowinset (0.00 sec)
# 查一下试试，也能查出来
MySQL [xuzong]> select * from q WHERE JSON_EXTRACT(age, '$.age') IS NULL;
+----+------+
| id | age |
+----+------+
| 1 | null |
+----+------+
1 row in set (0.0019 sec)
# update 一下试试 binlog 是怎么记录的
MySQL [xuzong]> update q set id=where id=;
Query OK, 1 row affected (0.0023 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 用 5.7.21 的 mysqlbinlog 解析出来 binlog：null 值 update 的时候 binlog 会记录为 ''
### UPDATE `xuzong`.`q`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='' /* JSON meta=4 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='' /* JSON meta=4 nullable=0 is_null=0 */
问题处理
查到是字段兼容性问题就好处理了，方法如下：
新建一个库，建议一个同表结构的表，将 JSON 字段改写为 TEXT 字段，然后将回滚 SQL 导入。再 UPDATE 修改 '' （空）值为 {}，然后再修改为 JSON 字段。业务判断数据是否正确，INSERT ... SELECT ... 写入回原表。
这样就解决了。。。
后续测试
其他版本 MySQL 是否存在同样的问题？亲测 5.7.35 和 5.7.44 也同样存在，这里不再概述。
但是用 MySQL 8.0 的 mysqlbinlog 去解析 binlog，记录为 null。
测试 VARCHAT 类型
MySQL [xuzong]> create table j (id int , age varchar());
Query OK, 0 rows affected (0.01 sec)
MySQL [xuzong]> insert into j values (,NULL);
Query OK, 1 row affected (0.01 sec)
MySQL [xuzong]> select * from j;
+------+------+
| id | age |
+------+------+
| 1 | NULL |
+------+------+
1 row in set (0.01 sec)
MySQL [xuzong]> alter table j modify age varchar() not null;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 1
MySQL [xuzong]> show Warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'age' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
MySQL [xuzong]> select * from j;
+------+-----+
| id | age |
+------+-----+
| 1 | |
+------+-----+
1 row in set (0.00 sec)
这样看的 VARCHAR 会自动转换为空值。
SQL_MODE
# 再执行复现操作就报错了，所以还是建议线上开启严格模式的。
set global sql_mode=deafult;
用 ibd2sql 解析一下 ibd 文件，发现数据是正确的:
[root@nvm-22vdnhahrwnq37 ibd2sql-main]# python3 main.py ../mysql4223/xuzong/j.ibd --sql --ddl
CREATE TABLE IF NOT EXISTS `xuzong`.`j`(
`id` int NULL,
`age` json NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
INSERT INTO `xuzong`.`j` VALUES (2, '{}');
总结
- 线上修改表结构，一定要先备份数据，然后在测试环境上修改表结构，测试通过后再上线。
- 修改表结构时，一定要注意数据类型的变化，特别是 JSON、BLOB 等特殊类型的变化。
- 建议线上开启 SQL_MODE 严格模式，避免出现一些意想不到的问题。
- 本次问题不影响线上数据以及主从复制，理论上来说只是 MySQL 5.7 版本 mysqlbinlog 的问题，以及违反了非空约束。
本文关键字：#MySQL #延迟 #大事务
腾讯云开发者
