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

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.