MySQL-datetime类型详解

研发反馈问题，数据库中 datetime 数据类型存储的值末尾会因四舍五入出现不一致数据，影响查询结果，比如：程序中自动获取带毫秒精度的日期 ’2019-03-05 01:53:55.63’，存入数据库后变成 ’2019-03-05 01:53:56’。

抛出问题：

具体情况看例子：

mysql> create table t(id int,dt datetime);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(1,’2019-03-05 01:53:55.63′);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+——+———————+
| id | dt |
+——+———————+
| 1 | 2019-03-05 01:53:56 |
+——+———————+
1 row in set (0.00 sec)

问题好理解，数据库自动对毫秒精度进行了四舍五入，取了个近似值。

解决问题：

问题也好解决：1. 修改字段类型，给 datetime 加上精度，改成 datetime(2)，这样就把后面的毫秒精度存进数据库了，也不会出现查询时数值错误；2. 如果毫秒精度实际意义不大，可以在程序中截断毫秒值，存入数据库的值直接精确到秒，这样数据库层面不需要修改。

mysql> create table t_m(id int,dt datetime(2));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_m values(1,’2019-03-05 01:53:55.63′);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_m;
+——+————————+
| id | dt |
+——+————————+
| 1 | 2019-03-05 01:53:55.63 |
+——+————————+
1 row in set (0.00 sec)

个人赞成第二种方法，因为架构定好后，不建议随便修改底层数据库，尽量从代码层满足需求，除非迫不得已。

问题延伸：

顺便说一下 datetime 数据类型。

1. 自动识别时间字符串

查看数据库默认日期类型格式：

mysql> show variables like ‘datetime_format’;
+—————–+——————-+
| Variable_name | Value |
+—————–+——————-+
| datetime_format | %Y-%m-%d %H:%i:%s |
+—————–+——————-+
1 row in set (0.00 sec)

默认格式是‘%Y-%m-%d %H:%i:%s’这样的，一般写入数据也是这种格式。

当写入其他时间字符串时，只有数据库能识别都会写入成功，且是想要的数据；如果识别不了的时间值，会显示成‘0000-00-00 00:00:00’。

mysql> insert into t values(3,’20191221010203′);

mysql> select * from t;
+——+———————+
| id | dt |
+——+———————+

mysql> insert into t values(4,’2019/12/21/1/21/3′);

mysql> select * from t;
+——+———————+
| id | dt |
+——+———————+

插入一个不存在的时间，会显示出‘0000-00-00 00:00:00’，比如 63 秒

mysql> insert into t values(4,’2019/12/21/1/21/63′);

mysql> select * from t;
+——+———————+
| id | dt |
+——+———————+

2. 查询时智能的补全模式

经常接到研发反馈，查一天的数据，查不出来，比如查询‘2019-12-21’日期的全部数据，很多人在 where 条件中写 where dt = ‘2019-12-21’，妄图查出一天的数据。如果 dt 类型是 date，会如常所愿；如果是 datetime 或其他类型，恐怕要大失所望了，因为 MySQL 会对 datetime 值自动补零。

例子：

表中有 3 条数据：

mysql> select * from t;
+——+———————+
| id | dt |
+——+———————+
| 3 | 2019-12-21 01:02:03 |
| 4 | 2019-12-21 01:21:03 |

查询‘2019-12-21’的数据：

mysql> select * from t where dt=’2019-12-21′;
Empty set (0.00 sec)

期望查到两条数据，实际啥都没查到，因为数据库自动根据类型补全了 0，实际查询语句成了：select * from t where dt=’2019-12-21 00:00:00′;

对于 datetime 类型的值，想要查询一天的数据，可以通过范围查询：between and（between and 是左右闭合区间，两边数值都能查到）。

mysql> select * from t where dt between ‘2019-12-21 00:00:00’ and ‘2019-12-21 23:59:59’;
+——+———————+
| id | dt |
+——+———————+
| 3 | 2019-12-21 01:02:03 |

3. 其他

其它一些东西，比如和 timestamp 比较之类的，网上比较多，自行搜索。