MySQL DATETIME与TIMESTAMP精度差异及批量修正方法

-- 创建测试数据库
CREATE DATABASE mydb_1;
USE mydb_1;

-- 测试DATETIME的精度表现
CREATE TABLE test(
	-- 默认精度为0，仅存储秒
	datetime1 DATETIME,
	datetime2 DATETIME(3), -- 存储毫秒
	datetime3 DATETIME(5)  -- 存储微秒（实际保留前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');

-- 测试TIMESTAMP的精度表现
CREATE TABLE test11(
	datetime1 TIMESTAMP,
	datetime2 TIMESTAMP(3),
	datetime3 TIMESTAMP(5)
);

INSERT INTO test11 VALUES('2020-11-22 12:01:01.59999', '2020-11-22 12:01:01.59999', '2020-11-22 12:01:01.59999');
INSERT INTO test11 VALUES('2020-11-22 12:01:01.5', '2020-11-22 12:01:01.599', '2020-11-22 12:01:01.59999');
INSERT INTO test11 VALUES('2020-11-22 12:01:01.4', '2020-11-22 12:01:01.594', '2020-11-22 12:01:01.59994');

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 ( 128 ) -- 修改之后的字段类型，例如 'DATETIME(3)' 或 'TIMESTAMP(3)' NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
) 
BEGIN
    DECLARE tbl_name VARCHAR ( 64 );
    DECLARE col_name VARCHAR ( 64 );
    DECLARE i_done INT DEFAULT 0;
    DECLARE SQL_FOR_ALTER VARCHAR ( 1024 );
    
    -- 声明游标：筛选指定库、指定表类型（排除视图等）、指定字段名的记录
    DECLARE mycursor CURSOR FOR 
        SELECT C.TABLE_NAME, C.COLUMN_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS C
        INNER 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 T.TABLE_TYPE = 'BASE TABLE'
          AND C.COLUMN_NAME = from_col_name;
          
    -- 游标结束标记
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET i_done = 1;
    
    OPEN mycursor;
    
    read_loop: LOOP
        FETCH mycursor INTO tbl_name, col_name;
        IF i_done = 1 THEN 
            LEAVE read_loop;
        END IF;
        
        -- 构造动态SQL
        SET @sql_str = CONCAT('ALTER TABLE `', tbl_name, '` MODIFY COLUMN `', col_name, '` ', to_col_type);
        
        -- 预处理并执行
        PREPARE stmt FROM @sql_str;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- 可选：打印日志或添加异常处理机制以防止单条失败中断整个循环
    END LOOP;
    
    CLOSE mycursor;
END$$

DELIMITER ;

-- 调用示例：将所有 'create_time' 字段改为 DATETIME(3)
CALL batch_alter_column_type('mydb_1', 'create_time', 'DATETIME(3)');

-- 仅修改datetime1字段的精度，不改变列名
ALTER TABLE test MODIFY COLUMN datetime1 DATETIME(3);

-- 修改列名为datetime11，并同时将其精度改为DATETIME(2)
ALTER TABLE test CHANGE COLUMN datetime1 datetime11 DATETIME(2);