Oracle--date-processing for MySQL time types and null values

Because the date types in Oracle are found in Oracle---->MYSQL data Migration, the corresponding MySQL time types are incorrectly set to cause errors, especially when there is a null value

MySQL version 5.6.40 version

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 for the date type to insert the alarm, but can still be inserted, because the date type is only recorded (YYYY-MM)

Query OK, 1 row affected (0.01 sec)

4 time NULL Insert test, type of hour, insert 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 Auto-fill 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 0 to see if it can be plugged in.

Test the date type---------------
Third column Date type

mysql> INSERT INTO T1 values (null, ' 0 ', ' 0 ', ' 0 ', ' 0 '); Insertion 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 '); Inserting a null test
ERROR 1292 (22007): Incorrect date value: ' NULL ' for column ' date_2 ' at row 1

-------------Test the DateTime type--
Column four is a 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
Column v 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 the Oracle database is not the same as the date type of MySQL, and Oracle matches the datetime types in Yyyy-mm-dd hh:mi:ss and MySQL, while MySQL is yyyy-mm. When a null value exists, the time type of MySQL can be inserted using Zezalai, and date,datetime,timestamp can be inserted using NULL, but the current timestamp is inserted by default, even if the timestamp is null.

Oracle--date-processing for MySQL time types and null values