mysql的sql_mode关于date的两个参数值NO_ZERO_DATE和NO_ZERO_IN_DATE

喜欢ヅ旅行 2022-12-18 11:54 148阅读 0赞

**STRICT\_TRANS\_TABLES：**

在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制

**NO\_ZERO\_IN\_DATE：**

在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。

**NO\_ZERO\_DATE：**

在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告

实例验证：

1）设置sql\_mode

set session sql\_mode='STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE';

2）建表

create table t1(id int,age date);

insert into t1 values(1,'0000-00-00');  
Query OK, 1 row affected (0.00 sec)

insert into t1 values(1,'0000-12-00');  
ERROR 1292 (22007): Incorrect date value: '0000-12-00' for column 'age' at row 1  
 insert into t1 values(1,'0000-00-13');  
ERROR 1292 (22007): Incorrect date value: '0000-00-13' for column 'age' at row 1  
 insert into t1 values(1,'0000-12-13');  
Query OK, 1 row affected (0.00 sec)  
 insert into t1 values(1,'2000-00-00');  
ERROR 1292 (22007): Incorrect date value: '2000-00-00' for column 'age' at row 1  
 insert into t1 values(1,'2000-00-12');  
ERROR 1292 (22007): Incorrect date value: '2000-00-12' for column 'age' at row 1  
 insert into t1 values(1,'2000-12-00');  
ERROR 1292 (22007): Incorrect date value: '2000-12-00' for column 'age' at row 1

3）结论：

在STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE模式下，只要日期的月和日中含有0值都报错，但是‘0000-00-00’除外

4）重新设置sql\_mode

set session sql\_mode='STRICT\_TRANS\_TABLES,NO\_ZERO\_DATE';  
5）插入数据

insert into t1 values(1,'0000-00-00');  
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'age' at row 1

insert into t1 values(1,'0000-12-00');  
Query OK, 1 row affected (0.00 sec)

insert into t1 values(1,'0000-00-13');  
Query OK, 1 row affected (0.00 sec)

insert into t1 values(1,'0000-12-13');  
Query OK, 1 row affected (0.00 sec)

insert into t1 values(1,'2000-00-00');  
Query OK, 1 row affected (0.00 sec)

insert into t1 values(1,'2000-00-12');  
Query OK, 1 row affected (0.00 sec)

insert into t1 values(1,'2000-12-00');  
Query OK, 1 row affected (0.00 sec)

6）结论

在STRICT\_TRANS\_TABLES,NO\_ZERO\_DATE模式下，只有‘0000-00-00’报错

7）设置sql\_mode的值

set global sql\_mode='STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE';

insert into t1 values(1,'0000-00-00');  
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'age' at row 1  
 insert into t1 values(1,'0000-12-00');  
ERROR 1292 (22007): Incorrect date value: '0000-12-00' for column 'age' at row 1  
 insert into t1 values(1,'0000-00-13');  
ERROR 1292 (22007): Incorrect date value: '0000-00-13' for column 'age' at row 1  
 insert into t1 values(1,'0000-12-13');  
Query OK, 1 row affected (0.01 sec)

insert into t1 values(1,'2000-00-00');  
ERROR 1292 (22007): Incorrect date value: '2000-00-00' for column 'age' at row 1  
 insert into t1 values(1,'2000-00-12');  
ERROR 1292 (22007): Incorrect date value: '2000-00-12' for column 'age' at row 1  
 insert into t1 values(1,'2000-12-00');  
ERROR 1292 (22007): Incorrect date value: '2000-12-00' for column 'age' at row 1

8）总结

NO\_ZERO\_IN\_DATE 支持 0000-00-00 0000-01-01 （年月日都为0，月日都不为0）插入表

NO\_ZERO\_DATE 支持 1000-00-00 0000-01-00 0000-00-01（年月日中任何一个不为0）插入表，年月日全为0报错

NO\_ZERO\_IN\_DATE、NO\_ZERO\_DATE 同时设置 支持 0000-01-01 1000-01-01（只允许年为0）插入表