Shulou(Shulou.com)06/01 Report--

Because when doing Oracle---->mysql data migration, it is easy to cause errors if the date type in Oracle and the corresponding mysql time type are improperly set, especially when there is a null value.

mysql version 5.6.40

mysql> desc t1;

+-------------+-----------+------+-----+-------------------+-----------------------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+-----------+------+-----+-------------------+-----------------------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| time_1 | time | YES | | NULL | |

| date_2 | date | YES | | NULL | |

| datetime_3 | datetime | YES | | NULL | |

| timestamp_4 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

+-------------+-----------+------+-----+-------------------+-----------------------------+

5 rows in set (0.00 sec)

You can insert the current time.

mysql> insert into t1 values(null,now(),now(),now(),now());

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;

+-------+------+--------------------------------------------------------------------------+

| Level | Code | Message |

+-------+------+--------------------------------------------------------------------------+

| Note | 1292 | Incorrect date value: '2018-05-11 11:18:41' for column 'date_2' at row 1 |

+-------+------+--------------------------------------------------------------------------+

1 row in set (0.00 sec)

Prompt date type insert alarm, but still can be inserted, because date type only records year and month (y-mm)

Query OK, 1 row affected (0.01 sec)

4 time null insertion test, time type, insertion 0

mysql> insert into t1 values (null,'0','2018-01-01','2018-01-01 12:12:12','2018-10-10 00:00:00');

Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;

+----+----------+------------+---------------------+---------------------+

| id | time_1 | date_2 | datetime_3 | timestamp_4 |

+----+----------+------------+---------------------+---------------------+

| 1 | 22:21:23 | 2018-05-08 | 2018-05-08 22:21:23 | 2018-05-08 22:21:23 |

| 2 | 22:21:54 | 2018-05-08 | 2018-05-08 22:21:54 | 2018-05-08 22:21:54 |

| 3 | 00:00:00 | 2018-01-01 | 2018-01-01 12:12:12 | 2018-10-10 00:00:00 |time_1 is autopopulated to 00:00:00

| 4 | 00:00:00 | 2018-01-01 | 2018-01-01 12:12:12 | 2018-10-10 00:00:00 |

+----+----------+------------+---------------------+---------------------+

4 rows in set (0.00 sec)

Then insert all zeroes to see if they fit in.

Test date type---------------

The third column is date type

mysql> insert into t1 values(null,'0',' 0','0'); insert 0

ERROR 1292 (22007): Incorrect date value: '0' for column 'date_2' at row 1

mysql> insert into t1 values(null,'0',' 0','0'); insert' 'test, leave blank, test insert

ERROR 1292 (22007): Incorrect date value: '' for column 'date_2' at row 1

mysql> insert into t1 values(null,'0','null',' 0','0'); insert null test

ERROR 1292 (22007): Incorrect date value: 'null' for column 'date_2' at row 1

------------Test datetime type--

The fourth column is datetime type

mysql> insert into t1 values(null,'0',null,'0','0');

ERROR 1292 (22007): Incorrect datetime value: '0' for column 'datetime_3' at row 1

Insert null succeeded

-------Test timestamp type

The fifth column is a timestamp.

mysql> insert into t1 values(null,'0',null,null,'0');

ERROR 1292 (22007): Incorrect datetime value: '0' for column 'timestamp_4' at row 1

mysql> insert into t1 values(null,'0',null,null,null);

Query OK, 1 row affected (0.00 sec)

Insert null succeeded

mysql> select * from t1;

+----+----------+------------+---------------------+---------------------+

| id | time_1 | date_2 | datetime_3 | timestamp_4 |

+----+----------+------------+---------------------+---------------------+

| 1 | 22:21:23 | 2018-05-08 | 2018-05-08 22:21:23 | 2018-05-08 22:21:23 |

| 2 | 22:21:54 | 2018-05-08 | 2018-05-08 22:21:54 | 2018-05-08 22:21:54 |

| 3 | 00:00:00 | 2018-01-01 | 2018-01-01 12:12:12 | 2018-10-10 00:00:00 |

| 4 | 00:00:00 | 2018-01-01 | 2018-01-01 12:12:12 | 2018-10-10 00:00:00 |

| 5 | 00:00:00 | NULL | NULL | 2018-05-08 22:33:22 |

+----+----------+------------+---------------------+---------------------+

5 rows in set (0.00 sec)

Summary: The date type of Oracle database is different from the date type of mysql. Oracle is y-mm-dd hh:mi:ss, which matches the datetime type in mysql, while mysql is y-mm. When there is a null value, the time type of mysql can be inserted with 0 zeros, while date, datetime, timestamp can be inserted with null, but timestamp will be inserted by default even if it is null.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.