mysql对datetime数据类型操作_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 |

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

| 3 | 2019-12-21 01:02:03 |

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

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

mysql> select * from t;

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

| id | dt |

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

| 4 | 2019-12-21 01:21:03 |

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

插入一个不存在的时间，会显示出‘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 |

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

| 4 | 0000-00-00 00:00:00 |

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

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 |

| 4 | 0000-00-00 00:00:00 |

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

查询‘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 |

| 4 | 2019-12-21 01:21:03 |

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

3.其他

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