Incorrect datetime value
今天在开发库上给一个表添加字段时候，发现居然报错:
root@DB 06:14:42>ALTER TABLE `DB`.` user` ADD COLUMN `status_mode` TINYINT UNSIGNED AFTER ` test_id`;
ERROR 1292 (22007): Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘GMT_CLEANUP’ at row 2;
查找error的信息：
$perror 1292
错误：1292 SQLSTATE: 22007 (ER_TRUNCATED_WRONG_VALUE)
消息：截短了不正确的%s值: ‘%s’
这种解释有点让人不明白。
接着想到mysql中alter table add column运行时会对原表进行临时复制，在副本上进行更改，然后删除原表，再对新表进行重命名。那么报错的原因就是在ddl过程中copy原表，在copy表的过程中发现有表中GMT_CLEANUP的数据为’0000-00-00 00:00:00’，mysql认为该数据是不合法的数据：
root@DB06:39:56>select GMT_CLEANUP from user where GMT_CLEANUP like ‘%00%’ limit 2
-> ;
+———————+
| GMT_CLEANUP |
+———————+
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
+———————+
2 rows in set, 1 warning (0.00 sec)
第一个问题：那么为什么mysql认为0000-00-00 00:00:00是不正确的？
第二个问题0000-00-00 00:00:00是怎么被插入到数据库中的，应用有这个需求吗？
对于第一个问题，还是需要回到mysql中对日期时间的定义上，在官方文档上说明MySQL允许将’0000-00-00’保存为“伪日期”(如果不使用NO_ZERO_DATE SQL模式)。这在某些情况下比使用NULL值更方便(并且数据和索引占用的空间更小)。那么接下来，就是看看sql_mode中的参数了；
root@DB06:40:48>show variables like ‘sql_mode’;
+—————+——————————————————————————————————————————-+
| Variable_name | Value |
+—————+——————————————————————————————————————————-+
| sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+—————+——————————————————————————————————————————-+
1 row in set (0.00 sec)
参数中含有no_zero_date，
在严格模式，不要将 ‘0000-00-00’做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告。
NO_ZERO_IN_DATE
在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们日期插入’0000-00-00’。在非严格模式，可以接受该日期，但会生成警告。
问题可以解决了，改变sql_mode:将no_zero_date和no_zero_in_date去掉：
root@DB 07:05:21>set global sql_mode=’STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER’;
Query OK, 0 rows affected (0.00 sec)
root@DB 07:07:01>show variables like ‘%sql_mode%’;
+—————+——————————————————————————————————————————-+
| Variable_name | Value |
+—————+——————————————————————————————————————————-+
| sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+—————+——————————————————————————————————————————-+
1 row in set (0.00 sec)
虽然去掉了no_zero_date和no_zero_in_date，但在参数中还有这两个参数的存在，于是exit该会话，在查看参数的值依然无效：
root@DB 07:07:10>exit
Bye
[MM-Writable@dev ~]
$mysql -uroot DB
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23505133
Server version: 5.1.37-log Source distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
root@DB 07:07:41>show variables like ‘%sql_mode%’;
+—————+——————————————————————————————————————————-+
| Variable_name | Value |
+—————+——————————————————————————————————————————-+
| sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+—————+——————————————————————————————————————————-+
1 row in set (0.00 sec)
为什么会出现这种情况，是不是参数设置的不对，查看了其他库中sql_mode的参数，都没有设置，唯独这个库中sql_mode设置了，看来需要对sql_mode做详细的了解了：
简单说sql_mode是设置mysql应该支持哪些sql语法，以及哪种数据验证检查。这样可以更容易地在不同的环境中使用MySQL，并结合其它数据库服务器使用MySQL。可以通过用SET [SESSION|GLOBAL] sql_mode=’modes’语句设置sql_mode变量来更改SQL模式。设置 GLOBAL变量时需要拥有SUPER权限，并且会影响从那时起连接的所有客户端的操作。设置SESSION变量只影响当前的客户端。任何客户端可以随时更改自己的会话 sql_mode值。
当前数据库的sql_mode中有：
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
这些值，这是将sql_mode设为了：TRADITIONAL模式，所以只去除NO_ZERO_IN_DATE,NO_ZERO_DATE是不行的，还要去除TRADITIONAL；
root@DB 07:07:43>set global sql_mode=’STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER’;
Query OK, 0 rows affected (0.00 sec)
退出来后，重新登录：
root@DB 07:20:47>show variables like ‘%sql_mode%’;
+—————+————————————————————————————–+
| Variable_name | Value |
+—————+————————————————————————————–+
| sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER |
+—————+————————————————————————————–+
1 row in set (0.00 sec)
root@DB 07:20:48>ALTER TABLE `DB`.`ali_mall_user` ADD COLUMN `status_mode` TINYINT UNSIGNED AFTER `op_invest_id`;
Query OK, 106 rows affected (0.66 sec)
Records: 106 Duplicates: 0 Warnings: 0
已经看到可以修改表的结构了，现在mysql允许ddl了。
对于第二个问题：为什么会插入0000-00-00 00:00:00？
。严格模式允许日期使用“零”部分，例如’2004-04-00’或“零”日期。要想禁止，应在严格模式基础上，启用NO_ZERO_IN_DATE和NO_ZERO_DATE SQL模式。
。每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。
。无效DATETIME、DATE或者TIMESTAMP值被转换为相应类型的“零”值(‘0000-00-00 00:00:00’、’0000-00-00’或者00000000000000)。
从上面的sql_mode中可以看到在严格模式（启用STRICT_TRANS_TABLES或STRICT_ALL_TABLES模式）是可以插入：0000-00-00 00:00:00’，但是后面还启动了TRADITIONAL，TRADITIONAL中还有NO_ZERO_IN_DATE和NO_ZERO_DATE 模式，所以前期插入了0000-00-00 00:00:00数据，后面有改动了sql_mode，最后导致前面插入插入的数据变为了不合法，所以才会出现上面总总问题。
