Oracle-date- corresponding to mysql time type and null value handling
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.
