MySQL存储过程中循环为created字段的日期时间追加秒数问题

问题描述 #

需要在InsertBulkData存储过程中实现每次循环时，为created字段的日期时间增加1秒，生成如下格式的按秒递增序列：

29/01/2023 13:43:56
29/01/2023 13:43:57
29/01/2023 13:43:58
29/01/2023 13:43:59
29/01/2023 13:44:00

当前存储过程代码无法实现该效果，核心问题出在created值的更新语句上。

问题分析 #

原代码存在三个关键错误：

• 类型选择不当：将created声明为TEXT类型，导致每次需要在字符串和日期类型间转换，容易出错。
• 格式符错误：DATE_FORMAT中使用%m表示分钟，但%m实际代表月份，分钟应该用%i，导致初始时间格式错误。
• 转换丢失时间：CONVERT(created, DATE)会将带时间的字符串转换为纯日期（丢失时分秒），后续加1秒只能基于当天00:00:00，无法实现秒级递增。

修正方案 #

方案1：使用DATETIME类型直接处理（推荐） #

将created改为DATETIME类型，直接操作日期时间值，避免字符串转换的麻烦：

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertBulkData`()
BEGIN    
    DECLARE counter INT DEFAULT 1;
    DECLARE id TEXT DEFAULT '';    
    DECLARE created DATETIME DEFAULT NOW(); -- 改为DATETIME类型，直接用当前时间初始化
    
    WHILE counter <= 1000 DO
        SET id = LPAD(counter, 9, 0);       
            
        INSERT INTO `ctech_discount_card`.`customer`
        (`ID`, `created`)
        VALUES (id, created);

        SET counter = counter + 1;
        SET created = DATE_ADD(created, INTERVAL 1 SECOND); -- 直接对日期时间加1秒
    END WHILE;
END

方案2：若需保留字符串格式 #

如果必须以指定字符串格式插入，需修正格式符和转换方式：

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertBulkData`()
BEGIN    
    DECLARE counter INT DEFAULT 1;
    DECLARE id TEXT DEFAULT '';    
    DECLARE created TEXT DEFAULT '';
    
    -- 初始设置：用%i表示分钟，格式正确的字符串
    SET created = DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i:%s'); 
       
    WHILE counter <= 1000 DO
        SET id = LPAD(counter, 9, 0);       
            
        INSERT INTO `ctech_discount_card`.`customer`
        (`ID`, `created`)
        VALUES (id, created);

        SET counter = counter + 1;
        -- 先将字符串转换为DATETIME，再加1秒，最后转回正确格式
        SET created = DATE_FORMAT(
            DATE_ADD(STR_TO_DATE(created, '%d/%m/%Y %H:%i:%s'), INTERVAL 1 SECOND),
            '%d/%m/%Y %H:%i:%s'
        );
    END WHILE;
END

关键修改说明 #

• 方案1中，使用DATETIME类型存储日期时间，通过DATE_ADD(created, INTERVAL 1 SECOND)直接实现秒级递增，效率更高且不易出错。
• 方案2中，用STR_TO_DATE将字符串转换为DATETIME类型后再加1秒，再通过DATE_FORMAT转回指定格式，同时修正了%i表示分钟的格式符错误。

内容的提问来源于stack exchange，提问作者Febrian Hafizuddin