关于一次mysql的列属性not null的坑爹排查

背景：

       先说下背景情况，最近遇到了一个坑爹的事情，有个数据从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

这到底是为什么呢...,于是硬着 头皮去查mysql的官方文档...终于发现了一点端倪....

Inserting NULL into a column that has been declared NOT NULL.For multiple-row INSERT statements or INSERT INTO ... SELECTstatements, 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这种约束也会被过滤掉，感兴趣的网友可以查看一下官方的文档。

结果：

      经过上面的一系列的排查，总算找到的问题原因所在，一个很小的问题，后面涉及到的知识和因素却一点都不少，当然这里也多谢一些大牛的帮助，才能顺利的定位到问题，这里对排查过程做一个小的总结。