开发者

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

目录
  • datetime与timestamp时间精度问题
    • 默认时间精度与最大时间精度
    • 更改数据库中所有指定字段的类型的存储过程(用于修正时间精度)
  • mysql中选datetime还是timestamp呢?
    • 1. 基本区别
    • 2. 其他特性
    • 3. 什么场景下用什么类型合适呢?
    • 4.BIGINT使用(占8字节)
  • 总结

    datetime与timestamp时间精度问题

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

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

    -- 创建数据库
    CREATE DATABASE mydb_1;
    
    -- 查看创建数据库建表语句(默认编码UTF8)
    SHOW CREATE DATABASE mydb_1;
    
    -- 创建表
    -- 测试datetime的精度
    CREATE TABLE test(
    	-- 默http://www.devze.com认精度为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-1编程1-22 12:01:01.599994");
    
    
    -- 创建表
    -- 测试timestamp的精度
    CREATE TABLE test11(
    	-- 默认精度为0
    	-- Maximum is 6.
    	datetime1 TIMESTAMP,
    	datetime2android 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");
    

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

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

    -- 结束符修改为$$
    DELIMITER $$
    DROP PROCEDURE IF	EXISTS BATch_alter_column_type $php$
    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:只可修改表列的属性
    -- 修改test表中datetime1字段属性为DATETIME(3)
    ALTER TABLE test MODIFY COLUMN datetime1 DATETIME(3);
    
    -- 修改test表中datetime1字段名称为datetime11,属性为DATETIME(2)
    ALTER TA开发者_SparkBLE test CHANGE datetime1 datetime11 DATETIME(2);
    

    MySQL中选datetime还是timestamp呢?

    1. 基本区别

    类型所占字节格式范围
    TIMESTAMP4字节YYYY-MM-DD HH:MM:SS1970-01-01 00:00:01utc到2038-01-19 03:14:07utc
    DATETIME5字节YYYY-MM-DD HH:MM:SS1000-01-01 00:00:00到9999-12-31 23:59:59
    DATE3字节YYYY-MM-DD1000-01-01到9999-12-31
    TIME3字节HH:MM:SS-838:59:59到838:59:59
    YEAR1字节YYYY1901到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性能最好。

    总结

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。

    0

    上一篇:

    下一篇:

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    最新数据库

    数据库排行榜