oracle --date-对应 mysql 时间类型的以及空值的处理

2024-07-24 12:35:52

字体：大中小

来源：转载

供稿：网友

　　因为在做Oracle---->mysql的数据迁移的时候，发现Oracle中的date类型，对应的mysql的时间类型设置不当容易引起错误，特别是存在空值的时候

　　mysql 版本 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)

　　可以插入当前的时间

　　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)

　　提示date类型插入告警，但是依旧可以插入进去，因为date类型只记录年月（yyyy-mm）

　　Query OK, 1 row affected (0.01 sec)

　　4个时间空值插入测试，time类型，插入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 自动填充为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)

　　接着全部插入0，看是否能够插入进去

　　测试date类型---------------
　　第三列为date类型

　　mysql> insert into t1 values(null,'0','0','0','0'); 插入 0
　　ERROR 1292 (22007): Incorrect date value: '0' for column 'date_2' at row 1

　　mysql> insert into t1 values(null,'0','','0','0'); 插入‘ ’测试，留空白，测试插入
　　ERROR 1292 (22007): Incorrect date value: '' for column 'date_2' at row 1

　　mysql> insert into t1 values(null,'0','null','0','0'); 插入 null 测试
　　ERROR 1292 (22007): Incorrect date value: 'null' for column 'date_2' at row 1
　　-------------测试datetime类型--
　　第四列为datetime类型
　　mysql> insert into t1 values(null,'0',null,'0','0');
　　ERROR 1292 (22007): Incorrect datetime value: '0' for column 'datetime_3' at row 1
　　插入null成功
　　---------测试timestamp类型
　　第五列为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)

　　插入null 成功

　　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)

　　总结： Oracle数据库的date类型和mysql的date类型是不一样的，Oracle为yyyy-mm-dd hh:mi:ss和mysql中的datetime类型匹配，而 mysql 为 yyyy-mm 。当在存在空值的时候，mysql的time 类型可以使用0零来插入，而date，datetime，timestamp可以使用null 来插入，但是timestamp即使为null，也会默认插入当前时间戳。

（编辑：武林网）

上一篇：什么影响了MySQL性能

下一篇：mysql主从拷贝一主一从