背景:
先说下背景情况,最近遇到了一个坑爹的事情,有个数据从A库到B库之后,某个字段数据不一致,原来的数据是值null,到新库之后变成了‘’, 这就奇怪了?于是查看了一下表结构,在A库源库的表结构的定义为了 xxx varchar(32) default null ,而在目标库的表结构为 xxx varchar(32) not null default ''类型,按理说如果对于null空值应该是写不进去的才对,于是进行了一些列的坑爹排查....
排查:
在本地库上试验了多次,如果是not null的话,怎么也写不进去,都会报出can not be null的错误?这是为什么呢...于是先复现这个场景
在本地建一张测试表,其中name字段的属性no t null ,并且设置了默认值 ‘'
mysql> show create table user;
+-------+------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL,
`name` varchar(128) NOT NULL DEFAULT '' COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------+
然后我们往其中写入几条语句,得到的结果如下,首先我们写入一个空值,发现不允许为null,写不进去,报错了
mysql> insert into user values(1,null);
ERROR 1048 (23000): Column 'name' cannot be null
然后我们又写入几条语句,这次没有附带name字段,mysql默认给一个default值,就是''
mysql> insert into user(id) values (1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | |
+----+------+
1 row in set (0.00 sec)
mysql> select * from user where name='';
+----+------+
| id | name |
+----+------+
| 1 | |
+----+------+
1 row in set (0.00 sec)
这就奇怪了 ,明明带有null值的是写不进去的,为啥会出现在目标库中呢?起初我们怀疑是,mysql的sql_mode导致的,sql_mode有严格模式和非严格模式,这里我们看下了一下当前数据的sql_mode
mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
此时,猜想会不会是sql_mode是严格模式导致的,于是我们把严格模式改成了非严格模式,接着在写入条sql
mysql> set sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+------------------------+
| @@sql_mode |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.01 sec)
我们同样执行了上面这条sql,发现还是写不进去...
mysql> insert into user values(33,null);
ERROR 1048 (23000): Column 'name' cannot be null
Inserting NULL
into a column that has been declared NOT NULL
.For multiple-row INSERT
statements or INSERT INTO ... SELECT
statements, the column is set to the implicit default value for the column data type.This is 0
for numeric types, the empty string (''
) for string types, and the “zero” value for date and time types. INSERT INTO ... SELECT
statements are handled the same way as multiple-row inserts because the server does not examine the result set from the SELECT
to see whether it returns a single row. (For a single-row INSERT
, no warning occurs when NULL
is inserted into a NOT NULL
column. Instead, the statement fails with an error.)
官方文档中有说明,如果是对于多行插入,这设置一个默认值,报错为warning,如果是单行写入,则报错为error。。。得到这个解释之后,似乎明白了什么。。。于是测试了起来了。。
按照官方文档的说明,我们测试了一下。。。
mysql> insert into user values(3,null),(4,null),(5,null);
Query OK, 3 rows affected, 1 warning (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 1
查询结果如下,如官方文档所言,如果是多条批量写入的话,则会默认设置一个默认值。。到这里总算是找到原因了。。。
mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
+----+------+
5 rows in set (0.00 sec)
mysql> select * from user where name = '';
+----+------+
| id | name |
+----+------+
| 2 | |
| 3 | |
| 4 | |
| 5 | |
+----+------+
4 rows in set (0.00 sec)
由于当前的sql_mode是非严格模式,于是猜想在严格模式下面是否可以,我们把数据库设置成严格模式
mysql> set sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into user values(111,null),(112,null),(113,'dbcd');
ERROR 1048 (23000): Column 'name' cannot be null
当然,后来我又测试一下ignore关键字,发现也是可以写进去的,去查了一下官方文档,ignore除了可以过滤掉主键或者唯一键冲突之外,同时对于null这种和not null这种约束也会被过滤掉,感兴趣的网友可以查看一下官方的文档。
结果:
经过上面的一系列的排查,总算找到的问题原因所在,一个很小的问题,后面涉及到的知识和因素却一点都不少,当然这里也多谢一些大牛的帮助,才能顺利的定位到问题,这里对排查过程做一个小的总结。