Why different datatime value, but still duplicate

user_4428471 0
Viewed 41

Mysql Doc

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is 'YYYY-MM-DD hh:mm:ss[.fraction]', the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999',

CREATE TABLE `dt_test` (
  `trade_date` datetime NOT NULL,
  PRIMARY KEY (`trade_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

mysql> insert into dt_test select '2022-01-01 00:00:00.120000';
ERROR 1062 (23000): Duplicate entry '2022-01-01 00:00:00' for key 'dt_test.PRIMARY'

I thought 2022-01-01 00:00:00.000000 is different with 2022-01-01 00:00:00.120000, so both should insert successfully. But actually only could insert one? What is the reason?

mysql
1 Answers

This behavior is specified in https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html. The value you try to insert is rounded when the value doesn't "fit":

  • Inserting a TIME, DATE, or TIMESTAMP value with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding.

You will not get any errors or warnings:

No warning or error is given when such rounding occurs. This behavior follows the SQL standard.

This means that even if you specify 2022-01-01 00:00:00.120000 as the value, it is rounded as 2022-01-01 00:00:00 in a DATETIME(0) column. This also happens with the value 2022-01-01 00:00:00.000000, which will result in a duplicate key error message.

Interestingly, the sentence

In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded.

on https://dev.mysql.com/doc/refman/8.0/en/datetime.html is misleading as the fraction part is not stored (or it is stored, but you can't use it in any way).

user_2869340
Related