为何不同带小数秒的DATETIME值插入时触发主键重复错误？

核心原因 #

你遇到的问题是因为创建表时datetime列未指定小数秒精度，MySQL默认会将datetime的小数精度设为0，这意味着所有带小数秒的时间值都会被自动截断掉小数部分。

所以2022-01-01 00:00:00.000000和2022-01-01 00:00:00.120000插入后都会被处理成2022-01-01 00:00:00，主键自然就重复了。

虽然官方文档提到DATETIME支持最多6位小数秒，但这个特性需要你在定义列时显式指定精度才会生效，否则默认不保留任何小数部分。

解决方法 #

修改表结构，给trade_date列加上小数秒精度（比如保留6位）：

ALTER TABLE dt_test MODIFY COLUMN trade_date datetime(6) NOT NULL;

修改后再执行插入操作，两个带不同小数秒的时间值就能正常插入，且会完整存储小数部分：

mysql> insert into dt_test select '2022-01-01 00:00:00.000000';
Query OK, 1 row affected (0.01 sec)

mysql> insert into dt_test select '2022-01-01 00:00:00.120000';
Query OK, 1 row affected (0.01 sec)

查询时也能看到完整的时间值：

mysql> select * from dt_test;
+----------------------------+
| trade_date                 |
+----------------------------+
| 2022-01-01 00:00:00.000000 |
| 2022-01-01 00:00:00.120000 |
+----------------------------+

内容的提问来源于stack exchange，提问作者zhuguowei