Incorrect datetime value

Incorrect datetime value
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，最后导致前面插入插入的数据变为了不合法，所以才会出现上面总总问题。

另：

sql_mode中的 STRICT_TRANS_TABLES和STRICT_ALL_TABLES 区别

 

mysql的官方说明中提出：

严格模式控制MySQL如何处理非法或丢失的输入值。有几种原因可以使一个值为非法。例如，数据类型错误，不适合列，或超出范围。当新插入的行不包含某列的没有显示定义DEFAULT子句的值，则该值被丢失。

 

对于事务表，当启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式时，如果语句中有非法或丢失值，则会出现错误。语句被放弃并滚动。

 

对于非事务表，如果插入或更新的第1行出现坏值，两种模式的行为相同。语句被放弃，表保持不变。如果语句插入或修改多行，并且坏值出现在第2或后面的行，结果取决于启用了哪个严格选项：

 

· 对于STRICT_ALL_TABLES，MySQL返回错误并忽视剩余的行。但是，在这种情况下，前面的行已经被插入或更新。这说明你可以部分更新，这可能不是你想要的。要避免这点，最好使用单行语句，因为这样可以不更改表即可以放弃。

 

· 对于STRICT_TRANS_TABLES，MySQL将非法值转换为最接近该列的合法值并插入调整后的值。如果值丢失，MySQL在列中插入隐式 默认值。在任何情况下，MySQL都会生成警告而不是给出错误并继续执行语句。

 

我的例子分析：

1：如果sql语句操作的是事务表（innodb类型）的时候，STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式对数据的影响是一样的。

需要注意的是：

在事务中如果某个sql更新数据的语句报错，那么这条语句下面的未语句都不会执行，这条语句上面已经执行的语句会事务结束的时候commit

这是需要我们捕捉错误，让事务回滚。

 

例子如下：

CREATE TABLE `t1` (

  `f1` int(11) NOT NULL AUTO_INCREMENT,

  `f2` varchar(2) NOT NULL,

  UNIQUE KEY `f1` (`f1`)

) ENGINE=innodb AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

 

DELIMITER $$

 

DROP PROCEDURE IF EXISTS `pr_test`$$

 

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `pr_test`()

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK;END;

   START TRANSACTION;

INSERT INTO t1 (f2) VALUES ('cd');

INSERT INTO t1 (f2) VALUES ('abc');

INSERT INTO t1 (f2) VALUES ('de');

COMMIT;

    END$$

 

DELIMITER ;

 

2：如果sql语句操作的是事务表。

两个区别是

INSERT INTO t1 (f2) VALUES ('ab'),('acd');

如果是STRICT_ALL_TABLES

结果是:

f1    f2

1     ab

如果是STRICT_TRANS_TABLES

结果是:

f1    f2

1     ab

2     ac