MySQL中datetime类型后边跟的数字

问题描述

数据库中新建表的时候，需要用到datetime类型，起初sql语句都是开发人员设计好表格之后，提交专门的数据库人员来进行格式化，包括增加索引等。直到有一天，数据库人员不负责格式化了，忽然发现datetime后边也跟的有一个后缀：（3），去网上搜索了下一时竟也找不到令人满意的解释。如此过去了半年，有一天领导问到这个后缀是干嘛的，支支吾吾难以描述，于是再来研究下datetime。

问题解释

苦苦寻觅，终于找到一段英文，有了一个详细的解释，大概意思就是说这个后缀是用来定义毫秒的精度的。

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’, and the range for TIMESTAMP values is ‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999’. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see Section 11.3.6, “Fractional Seconds in Time Values”.

内心激动呀，必须码几行！

数据库实践

1.建表

CREATE TABLE test_datetime_suffix
(
   ID BIGINT NOT NULL,
   TIME1 DATETIME  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间1',
   TIME2 DATETIME (3) NOT NULL DEFAULT CURRENT_TIMESTAMP (3) ON UPDATE CURRENT_TIMESTAMP (3) COMMENT '时间2',
   TIME3 DATETIME (6) NOT NULL DEFAULT CURRENT_TIMESTAMP (6) ON UPDATE CURRENT_TIMESTAMP (6)COMMENT '时间3',
   TIME4 DATETIME (8) NOT NULL DEFAULT CURRENT_TIMESTAMP (8) ON UPDATE CURRENT_TIMESTAMP (8) COMMENT '时间4',
   PRIMARY KEY (ID)
)COMMENT '测试datetime类型后缀数字表';

从图中可以看到，后缀最大精度是6，超过就会报错。把TIME4这个字段删了，就可以成功建表了。

2.插入几条记录

从图中可以看到，TIME1没有毫秒值，TIME2毫秒值有3位，TIEM3有6位，恰好对应建表时datetime后缀中的数字。

后记

原来建表时清一色的：datetime (3)，导致数据库中存储的时间有3位毫秒值，展示到页面上时就会被测试人员怼回来要优化，都会调用MYSQL自带的DATEFORMAT函数进行格式化，现在才发现原来是这个毫秒值在搞鬼，如果不是对时间精度要求特别高，还是使用datetime不加任何后缀来得更爽些。