问题描述
数据库中新建表的时候,需要用到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不加任何后缀来得更爽些。