目錄

• datetime與timestamp時(shí)間精度問(wèn)題
• 默認(rèn)時(shí)間精度與最大時(shí)間精度
• 更改數(shù)據(jù)庫(kù)中所有指定字段的類(lèi)型的存儲(chǔ)過(guò)程（用于修正時(shí)間精度）
• MySQL中選datetime還是timestamp呢？
• 1. 基本區(qū)別
• 2. 其他特性
• 3. 什么場(chǎng)景下用什么類(lèi)型合適呢？
• 4.BIGINT使用（占8字節(jié)）
• 總結(jié)

datetime與timestamp時(shí)間精度問(wèn)題

• 默認(rèn)時(shí)間精度與最大時(shí)間精度
• 更改數(shù)據(jù)庫(kù)中所有指定字段的類(lèi)型的存儲(chǔ)過(guò)程（用于修正時(shí)間精度）

默認(rèn)時(shí)間精度與最大時(shí)間精度

-- 創(chuàng)建數(shù)據(jù)庫(kù)CREATE DATABASE mydb_1;-- 查看創(chuàng)建數(shù)據(jù)庫(kù)建表語(yǔ)句（默認(rèn)編碼UTF8）SHOW CREATE DATABASE mydb_1;-- 創(chuàng)建表-- 測(cè)試datetime的精度CREATE TABLE test(	-- 默認(rèn)精度為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");-- 創(chuàng)建表-- 測(cè)試timestamp的精度CREATE TABLE test11(	-- 默認(rèn)精度為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");

更改數(shù)據(jù)庫(kù)中所有指定字段的類(lèi)型的存儲(chǔ)過(guò)程（用于修正時(shí)間精度）

-- 結(jié)束符修改為$$DELIMITER $$DROP PROCEDURE IF	EXISTS batch_alter_column_type $$CREATE PROCEDURE batch_alter_column_type ( 	sch_name VARCHAR ( 128 ), -- 庫(kù)名稱(chēng)	from_col_name VARCHAR ( 32 ), -- 修改的字段	to_col_type VARCHAR ( 32 ) -- 修改之后的字段類(lèi)型) BEGIN  -- 當(dāng)前表名	DECLARE tbl_name VARCHAR ( 64 );  -- 當(dāng)前字段名	DECLARE col_name VARCHAR ( 64 );	-- 游標(biāo)結(jié)束標(biāo)記	DECLARE i_done INT ( 1 );	-- 修改的SQL語(yǔ)句	DECLARE SQL_FOR_ALTER VARCHAR ( 1024 );	-- 聲明游標(biāo)，存儲(chǔ)要修改表和字段	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  -- 字符串類(lèi)型轉(zhuǎn)換	   AND TABLE_TYPE   = "BASE TABLE"	 		 AND COLUMN_NAME  = from_col_name;	-- 游標(biāo)中的內(nèi)容執(zhí)行完后將標(biāo)記設(shè)置為1  	DECLARE CONTINUE HANDLER FOR NOT FOUND SET i_done = 1;  -- 打開(kāi)游標(biāo)		OPEN mycursor;  -- 執(zhí)行循環(huán)		Lp:LOOP			-- 取出游標(biāo)中的值		FETCH mycursor INTO tbl_name,col_name;	  -- 如果標(biāo)記為1，退出循環(huán)		IF i_done = 1 THEN 			 LEAVE Lp;		END IF;		-- 構(gòu)造修改語(yǔ)句		SET SQL_FOR_ALTER = CONCAT( "ALTER TABLE ", tbl_name, " MODIFY COLUMN ", col_name, " ", to_col_type );		-- 給局部變量賦值		SET @SQL = SQL_FOR_ALTER;		-- 預(yù)處理SQL語(yǔ)句		PREPARE stmt FROM @SQL;		-- 執(zhí)行SQL語(yǔ)句		EXECUTE stmt;	END LOOP;	-- 釋放游標(biāo)	CLOSE mycursor;END$$-- 調(diào)用存儲(chǔ)過(guò)程DELIMITER ;call batch_alter_column_type("mydb_1","MODISTAMP", "datetime(3)");

使用ALTER修改表的字段

• CHANGE：可修改表列名稱(chēng)和屬性
• MODIGY：只可修改表列的屬性

-- 修改test表中datetime1字段屬性為DATETIME(3)ALTER TABLE test MODIFY COLUMN datetime1 DATETIME(3);-- 修改test表中datetime1字段名稱(chēng)為datetime11，屬性為DATETIME(2)ALTER TABLE test CHANGE datetime1 datetime11 DATETIME(2);

MySQL中選datetime還是timestamp呢？

1. 基本區(qū)別

類(lèi)型所占字節(jié)格式范圍TIMESTAMP4字節(jié)YYYY-MM-DD HH:MM:SS1970-01-01 00:00:01utc到2038-01-19 03:14:07utcDATETIME5字節(jié)YYYY-MM-DD HH:MM:SS1000-01-01 00:00:00到9999-12-31 23:59:59DATE3字節(jié)YYYY-MM-DD1000-01-01到9999-12-31TIME3字節(jié)HH:MM:SS-838:59:59到838:59:59YEAR1字節(jié)YYYY1901到2155

注：MySQL 5.6.4 之前，占 8 個(gè)字節(jié) ，之后版本，占 5 個(gè)字節(jié)。

2. 其他特性

1. TIMESTAMP是以u(píng)tc格式存儲(chǔ)，會(huì)自動(dòng)檢索當(dāng)前時(shí)區(qū)對(duì)時(shí)間進(jìn)行轉(zhuǎn)換，而DATETIME不會(huì)。

2. 存入null時(shí)，TIMESTAMP會(huì)自動(dòng)存儲(chǔ)當(dāng)前時(shí)間，而DATETIME存儲(chǔ)null值。

3. 時(shí)間計(jì)算：

DATETIME翻譯為漢語(yǔ)即"時(shí)間戳"，它是當(dāng)前時(shí)間到 Unix元年(1970 年 1 月 1 日 0 時(shí) 0 分 0 秒)的秒數(shù)。對(duì)于某些時(shí)間的計(jì)算，如果是以 DATETIME 的形式會(huì)比較困難，假如我是 1994-1-20 06:06:06 出生，現(xiàn)在的時(shí)間是 2016-10-1 20:04:50 ，那么要計(jì)算我活了多少秒鐘， DATETIME還需要函數(shù)進(jìn)行轉(zhuǎn)換，但是 TIMESTAMP 直接相減就行。

3. 什么場(chǎng)景下用什么類(lèi)型合適呢？

1.需要跨時(shí)區(qū)計(jì)算時(shí)間用 或者 需要自動(dòng)更新時(shí)間的TIMESTAMP

計(jì)算一架從北京飛往紐約的飛機(jī)的飛行時(shí)間。這個(gè)場(chǎng)景中，如果使用 TIMESTAMP 來(lái)存時(shí)間，起飛和降落時(shí)間的值，都會(huì)被轉(zhuǎn)換成 UTC 時(shí)間，所以它們直接相減即可獲得結(jié)果。但如果使用 DATATIME 格式存時(shí)間，還需要進(jìn)行轉(zhuǎn)換，才可以完成，容易出錯(cuò)。

2.記錄創(chuàng)建修改時(shí)間 或者 時(shí)間范圍大于2038 用DATETIME

DATATIME作為記錄時(shí)間，現(xiàn)在都已經(jīng)2022年了，很快就到2038年啦，使用DATATIME不需要擔(dān)心超過(guò)范圍。

當(dāng)然在兩者都滿(mǎn)足使用的情況下，所占字節(jié)越小越好，TIMESTAMP比DATATIME好。

4.BIGINT使用（占8字節(jié)）

還有一種情況，即不用TIMESTAMP也不用DATATIME，而是用BIGINT。存儲(chǔ)自紀(jì)元以來(lái)的毫秒數(shù)（如果使用的是 Java，則用 System.currentTimeMillis() 獲取當(dāng)前時(shí)間）

這樣有幾個(gè)優(yōu)點(diǎn)：

1. 可以在遷移數(shù)據(jù)庫(kù)時(shí)避免因?yàn)閿?shù)據(jù)類(lèi)型差異。比如MySQL的DATETIME類(lèi)型和Oracle的DATETIME類(lèi)型之間可能存在差異，timestamp類(lèi)型的精度可能也存在差異，MySQL的timestamp精度不是一開(kāi)始就支持毫秒精度的。

2. 沒(méi)有時(shí)區(qū)問(wèn)題。無(wú)論是哪個(gè)時(shí)區(qū)，因?yàn)殚_(kāi)始計(jì)算的時(shí)間不同，無(wú)論當(dāng)前時(shí)間如何，跨度是一致的。也沒(méi)有timestamp和datatime的范圍問(wèn)題。是對(duì)timestamp的補(bǔ)充。

3. InnoDB存儲(chǔ)引擎下，通過(guò)時(shí)間范圍查找，性能bigint > datetime > timestamp，通過(guò)時(shí)間排序，性能bigint > timestamp > datetime。綜合來(lái)講，bigint性能最好。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持。