MySQL之DATETIME与TIMESTAMP的时间精度问题怎么解决

2023-07-05 05:55

短信预约 -IT技能 免费直播动态提醒

本篇内容介绍了“MySQL之DATETIME与TIMESTAMP的时间精度问题怎么解决”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

datetime与timestamp时间精度问题

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

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

-- 创建数据库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");

MySQL之DATETIME与TIMESTAMP的时间精度问题怎么解决

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

-- 结束符修改为$$DELIMITER $$DROP PROCEDURE IFEXISTS 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：只可修改表列的属性

-- 修改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. 其他特性

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

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

时间计算：

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