在5.0.45-community-nt-log中能正常运行的SQL,在5.6.21中不正常。
1. 1查看数据库版本
mysql> select database(), version();
+------------+-------------------------+
| database() | version() |
+------------+-------------------------+
| test | 5.0.45-community-nt-log |
+------------+-------------------------+
1 row in set
1.2.创建表test_datatime，指定sendTime列为datatime类型，默认为空。
mysql> CREATE TABLE `test_datatime` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`sendTime` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected
1.3. 尝试插入'' 到sendTime列，在mysql5.0.45中SQL能正常执行。
mysql> INSERT INTO `test_datatime` (`id`, `sendTime`) VALUES (5, now());
Query OK, 1 row affected
mysql> INSERT INTO `test_datatime` (`id`, `sendTime`) VALUES (6,'' );
Query OK, 1 row affected
mysql> select * from test_datatime;
+----+---------------------+
| id | sendTime |
+----+---------------------+
| 5 | 2014-10-30 16:55:02 |
| 6 | 0000-00-00 00:00:00 |
+----+---------------------+
2 rows in set
2. 1查看数据库版本
mysql> select database(), version();
+------------+-------------------------------------------+
| database() | version() |
+------------+-------------------------------------------+
| lw | 5.6.21-enterprise-commercial-advanced-log |
+------------+-------------------------------------------+
1 row in set
2.2.创建表test_datatime，指定sendTime列为datatime类型，默认为空。
mysql> CREATE TABLE `test_datatime` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`sendTime` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected
2.3. 尝试插入'' 到sendTime列，在mysql5.6.21中SQL报错1292 - Incorrect datetime value
mysql> INSERT INTO `test_datatime` (`id`, `sendTime`) VALUES (5, now());
Query OK, 1 row affected
mysql> INSERT INTO `test_datatime` (`id`, `sendTime`) VALUES (6,'' );
1292 - Incorrect datetime value: '' for column 'sendTime' at row 1
mysql> select * from test_datatime;
+----+---------------------+
| id | sendTime |
+----+---------------------+
| 5 | 2014-10-30 16:04:01 |
+----+---------------------+
1 row in set
由于这种改变，当Mysql由低版本升级为较高的版本时，要做全面的测试。同时项目中的驱动包，也要同步更新。