MySQL之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");

-- 結束符修改為$$
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)');

-- 修改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);