MySQL+PHP插入新行时实现rowNum列按时间列顺次调整

前提说明：由于dateTime是MM-DD-YYYY HH:ii格式的varchar字段，所有时间比较操作必须先通过STR_TO_DATE函数转换为日期类型，避免字符串排序逻辑错误。

1. 核心逻辑说明 #

• 先判断待插入时间是否为当前表内最大值：是则直接取最大rowNum+1插入
• 否则找到第一个时间大于待插入时间的行的rowNum作为新行的rowNum，再将所有时间大于待插入时间的已有行的rowNum统一+1，最后插入新行
• 全程使用事务保证操作原子性，避免并发插入导致数据混乱

2. 完整SQL执行流程（可直接嵌入PHP逻辑） #

首先开启事务：

START TRANSACTION;

2.1 定义待插入的时间参数（PHP中可替换为变量传入） #

SET @insert_time = '09-17-2021 15:21';
SET @insert_time_dt = STR_TO_DATE(@insert_time, '%m-%d-%Y %H:%i');

2.2 查询目标rowNum #

SELECT 
  IF(MAX(STR_TO_DATE(`dateTime`, '%m-%d-%Y %H:%i')) < @insert_time_dt,
    MAX(rowNum) + 1,
    (SELECT rowNum FROM student WHERE STR_TO_DATE(`dateTime`, '%m-%d-%Y %H:%i') > @insert_time_dt ORDER BY STR_TO_DATE(`dateTime`, '%m-%d-%Y %H:%i') ASC LIMIT 1)
  ) INTO @target_rowNum
FROM student;

2.3 更新所有时间晚于待插入时间的行的rowNum #

UPDATE student 
SET rowNum = rowNum + 1 
WHERE STR_TO_DATE(`dateTime`, '%m-%d-%Y %H:%i') > @insert_time_dt;

2.4 插入新行（其余字段自行补充） #

INSERT INTO student (`dateTime`, rowNum, 其他字段名)
VALUES (@insert_time, @target_rowNum, 其他字段值);

2.5 提交事务 #

COMMIT;

如果任意步骤出错，直接执行ROLLBACK;回滚即可。

3. PHP代码集成示例（PDO版本） #

// 待插入参数
$insertTime = '09-17-2021 15:21';
// 其他字段值自行补充
$otherField = 'xxx';

try {
    $pdo = new PDO('mysql:host=你的主机;dbname=你的库名;charset=utf8', '账号', '密码');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 开启事务
    $pdo->beginTransaction();
    
    // 计算目标rowNum
    $stmt = $pdo->prepare("
        SELECT 
          IF(MAX(STR_TO_DATE(`dateTime`, '%m-%d-%Y %H:%i')) < STR_TO_DATE(?, '%m-%d-%Y %H:%i'),
            MAX(rowNum) + 1,
            (SELECT rowNum FROM student WHERE STR_TO_DATE(`dateTime`, '%m-%d-%Y %H:%i') > STR_TO_DATE(?, '%m-%d-%Y %H:%i') ORDER BY STR_TO_DATE(`dateTime`, '%m-%d-%Y %H:%i') ASC LIMIT 1)
          ) as target_rowNum
        FROM student
    ");
    $stmt->execute([$insertTime, $insertTime]);
    $targetRowNum = $stmt->fetchColumn();
    
    // 更新后续rowNum
    $stmt = $pdo->prepare("
        UPDATE student 
        SET rowNum = rowNum + 1 
        WHERE STR_TO_DATE(`dateTime`, '%m-%d-%Y %H:%i') > STR_TO_DATE(?, '%m-%d-%Y %H:%i')
    ");
    $stmt->execute([$insertTime]);
    
    // 插入新行
    $stmt = $pdo->prepare("
        INSERT INTO student (`dateTime`, rowNum, 其他字段名)
        VALUES (?, ?, ?)
    ");
    $stmt->execute([$insertTime, $targetRowNum, $otherField]);
    
    // 提交事务
    $pdo->commit();
} catch (Exception $e) {
    // 出错回滚
    $pdo->rollBack();
    throw $e;
}

性能优化提示 #

如果表数据量较大，建议新增一个生成列存储转换后的datetime类型值并加索引，避免每次查询都全表转换计算，提升性能：

ALTER TABLE student 
ADD COLUMN date_time_dt DATETIME AS (STR_TO_DATE(`dateTime`, '%m-%d-%Y %H:%i')) STORED,
ADD INDEX idx_date_time_dt (date_time_dt);

后续所有时间比较操作直接使用date_time_dt字段即可。

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