In STRICT&NO_ ZERO_ DATE mode,When processing the missing date field through load data, you can insert 0000-00-00:00?
Posted by:
Gang Fan
Date: June 27, 2021 08:59AM
CREATE TABLE `t3` (
`id` int(11) NOT NULL,
`age` int(11) DEFAULT NULL,
`birthday` datetime NOT NULL,
PRIMARY KEY (`id`)
);
cat /tmp/t3.txt
1001,2
1002,3
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE
mysql> load data infile '/tmp/t3.txt' into table t3 fields terminated by ',' optionally enclosed by '\"' lines terminated by '\n'(id,age);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from t3;
+------+------+---------------------+
| id | age | birthday |
+------+------+---------------------+
| 1001 | 2 | 0000-00-00 00:00:00 |
| 1002 | 3 | 0000-00-00 00:00:00 |
+------+------+---------------------+
2 rows in set (0.01 sec)
E TABLE `t3` (
`id` int(11) NOT NULL,
`age` int(11) DEFAULT NULL,
`birthday` datetime NOT NULL,
PRIMARY KEY (`id`)
);
cat /tmp/t3.txt
1001,2
1002,3
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE
mysql> load data infile '/tmp/t3.txt' into table t3 fields terminated by ',' optionally enclosed by '\"' lines terminated by '\n'(id,age);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from t3;
+------+------+---------------------+
| id | age | birthday |
+------+------+---------------------+
| 1001 | 2 | 0000-00-00 00:00:00 |
| 1002 | 3 | 0000-00-00 00:00:00 |
+------+------+---------------------+
2 rows in set (0.01 sec)
??Shouldn't report a error?like this:
mysql> insert into t3 values(1003,4,'0000-00-00 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'birthday' at row 1