MySQL之DATETIME与TIMESTAMP的时间精度问题

datetime与timestamp时间精度问题

• 默认时间精度与最大时间精度
• 更改数据库中所有指定字段的类型的存储过程（用于修正时间精度）

默认时间精度与最大时间精度

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

-- 创建数据库 CREATE DATABASE mydb_1;   -- 查看创建数据库建表语句（默认编码UTF8） SHOW CREATE DATABASE mydb_1;   -- 创建表 -- 测试datetime的精度 CREATE TABLE test(      -- 默认精度为0      -- Maximum is 6.      datetime1 DATETIME,      datetime2 DATETIME(3),      datetime3 DATETIME(5) ); INSERT INTO test VALUES ( "2020-11-22 12:01:01.59999" , "2020-11-22 12:01:01.59999" , "2020-11-22 12:01:01.59999" ); INSERT INTO test VALUES ( "2020-11-22 12:01:01.5" , "2020-11-22 12:01:01.599" , "2020-11-22 12:01:01.59999" ); INSERT INTO test VALUES ( "2020-11-22 12:01:01.4" , "2020-11-22 12:01:01.594" , "2020-11-22 12:01:01.59994" ); INSERT INTO test VALUES ( "2020-11-22 12:01:01.5" , "2020-11-22 12:01:01.5995" , "2020-11-22 12:01:01.599995" ); INSERT INTO test VALUES ( "2020-11-22 12:01:01.5" , "2020-11-22 12:01:01.5994" , "2020-11-22 12:01:01.599994" );     -- 创建表 -- 测试timestamp的精度 CREATE TABLE test11(      -- 默认精度为0      -- Maximum is 6.      datetime1 TIMESTAMP ,      datetime2 TIMESTAMP (3),      datetime3 TIMESTAMP (5) ); INSERT INTO test1 VALUES ( "2020-11-22 12:01:01.59999" , "2020-11-22 12:01:01.59999" , "2020-11-22 12:01:01.59999" ); INSERT INTO test1 VALUES ( "2020-11-22 12:01:01.5" , "2020-11-22 12:01:01.599" , "2020-11-22 12:01:01.59999" ); INSERT INTO test1 VALUES ( "2020-11-22 12:01:01.4" , "2020-11-22 12:01:01.594" , "2020-11-22 12:01:01.59994" ); INSERT INTO test1 VALUES ( "2020-11-22 12:01:01.5" , "2020-11-22 12:01:01.5995" , "2020-11-22 12:01:01.599995" ); INSERT INTO test1 VALUES ( "2020-11-22 12:01:01.5" , "2020-11-22 12:01:01.5994" , "2020-11-22 12:01:01.599994" );

更改数据库中所有指定字段的类型的存储过程（用于修正时间精度）

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

-- 结束符修改为$$ DELIMITER $$ DROP PROCEDURE IF   EXISTS batch_alter_column_type $$ CREATE PROCEDURE batch_alter_column_type (      sch_name VARCHAR ( 128 ), -- 库名称      from_col_name VARCHAR ( 32 ), -- 修改的字段      to_col_type VARCHAR ( 32 ) -- 修改之后的字段类型 ) BEGIN    -- 当前表名      DECLARE tbl_name VARCHAR ( 64 );    -- 当前字段名      DECLARE col_name VARCHAR ( 64 );      -- 游标结束标记      DECLARE i_done INT ( 1 );      -- 修改的SQL语句      DECLARE SQL_FOR_ALTER VARCHAR ( 1024 );      -- 声明游标，存储要修改表和字段      DECLARE mycursor CURSOR FOR         SELECT C.TABLE_NAME, C.COLUMN_NAME           FROM INFORMATION_SCHEMA.COLUMNS C           LEFT JOIN INFORMATION_SCHEMA.TABLES T           ON C.TABLE_NAME = T.TABLE_NAME           AND C.TABLE_SCHEMA = T.TABLE_SCHEMA         WHERE C.TABLE_SCHEMA = sch_name  -- 字符串类型转换         AND TABLE_TYPE   = 'BASE TABLE'             AND COLUMN_NAME  = from_col_name;      -- 游标中的内容执行完后将标记设置为1       DECLARE CONTINUE HANDLER FOR NOT FOUND SET i_done = 1;    -- 打开游标        OPEN mycursor;    -- 执行循环        Lp:LOOP          -- 取出游标中的值          FETCH mycursor INTO tbl_name,col_name;        -- 如果标记为1，退出循环          IF i_done = 1 THEN               LEAVE Lp;          END IF;          -- 构造修改语句          SET SQL_FOR_ALTER = CONCAT( "ALTER TABLE " , tbl_name, " MODIFY COLUMN " , col_name, " " , to_col_type );          -- 给局部变量赋值          SET @SQL = SQL_FOR_ALTER;          -- 预处理SQL语句          PREPARE stmt FROM @SQL;          -- 执行SQL语句          EXECUTE stmt;      END LOOP;      -- 释放游标      CLOSE mycursor; END $$ -- 调用存储过程 DELIMITER ; call batch_alter_column_type( 'mydb_1' , 'MODISTAMP' , 'datetime(3)' );

使用ALTER修改表的字段

• CHANGE：可修改表列名称和属性
• MODIGY：只可修改表列的属性

?

1 2 3 4 5

-- 修改test表中datetime1字段属性为DATETIME(3) ALTER TABLE test MODIFY COLUMN datetime1 DATETIME(3);   -- 修改test表中datetime1字段名称为datetime11，属性为DATETIME(2) ALTER TABLE test CHANGE datetime1 datetime11 DATETIME(2);

MySQL中选datetime还是timestamp呢？

1. 基本区别

类型	所占字节	格式	范围
TIMESTAMP	4字节	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01utc到2038-01-19 03:14:07utc
DATETIME	5字节	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00到9999-12-31 23:59:59
DATE	3字节	YYYY-MM-DD	1000-01-01到9999-12-31
TIME	3字节	HH:MM:SS	-838:59:59到838:59:59
YEAR	1字节	YYYY	1901到2155

注：MySQL 5.6.4 之前，占 8 个字节 ，之后版本，占 5 个字节。

2. 其他特性

1. TIMESTAMP是以utc格式存储，会自动检索当前时区对时间进行转换，而DATETIME不会。

2. 存入null时，TIMESTAMP会自动存储当前时间，而DATETIME存储null值。

3. 时间计算：

DATETIME翻译为汉语即"时间戳"，它是当前时间到 Unix元年(1970 年 1 月 1 日 0 时 0 分 0 秒)的秒数。对于某些时间的计算，如果是以 DATETIME 的形式会比较困难，假如我是 1994-1-20 06:06:06 出生，现在的时间是 2016-10-1 20:04:50 ，那么要计算我活了多少秒钟， DATETIME还需要函数进行转换，但是 TIMESTAMP 直接相减就行。

3. 什么场景下用什么类型合适呢？

1.需要跨时区计算时间用 或者 需要自动更新时间的TIMESTAMP

计算一架从北京飞往纽约的飞机的飞行时间。这个场景中，如果使用 TIMESTAMP 来存时间，起飞和降落时间的值，都会被转换成 UTC 时间，所以它们直接相减即可获得结果。但如果使用 DATATIME 格式存时间，还需要进行转换，才可以完成，容易出错。

2.记录创建修改时间 或者 时间范围大于2038 用DATETIME

DATATIME作为记录时间，现在都已经2022年了，很快就到2038年啦，使用DATATIME不需要担心超过范围。

当然在两者都满足使用的情况下，所占字节越小越好，TIMESTAMP比DATATIME好。

4.BIGINT使用（占8字节）

还有一种情况，即不用TIMESTAMP也不用DATATIME，而是用BIGINT。存储自纪元以来的毫秒数（如果使用的是 Java，则用 System.currentTimeMillis() 获取当前时间）

这样有几个优点：

1. 可以在迁移数据库时避免因为数据类型差异。比如MySQL的DATETIME类型和Oracle的DATETIME类型之间可能存在差异，timestamp类型的精度可能也存在差异，MySQL的timestamp精度不是一开始就支持毫秒精度的。

2. 没有时区问题。无论是哪个时区，因为开始计算的时间不同，无论当前时间如何，跨度是一致的。也没有timestamp和datatime的范围问题。是对timestamp的补充。

3. InnoDB存储引擎下，通过时间范围查找，性能bigint > datetime > timestamp，通过时间排序，性能bigint > timestamp > datetime。综合来讲，bigint性能最好。

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。

原文链接：https://kylee.blog.csdn.net/article/details/110727997