使用INSERT ... ON DUPLICATE KEY时如何避免自增ID跳号？

问题背景 #

我现在使用以下SQL语句，通过ON DUPLICATE KEY UPDATE实现重复键时更新行：

$stmt = $dbCon->prepare("INSERT INTO videos_rating (videos_rating_video_fk, " . 
                        " videos_rating_user_fk, " . 
                        " videos_rating_rating) " . 
                        " VALUES (:video_id, " . 
                        " :user_id, " . 
                        " :video_rating) " . 
                        " ON DUPLICATE KEY UPDATE videos_rating_rating = :video_rating");

脚本功能正常，但遇到了自增列id跳号的问题：

• 初始表为空，给某视频评分后生成ID为1的行；
• 再次给同一视频评分时触发更新，此时无问题；
• 但其他用户给新视频评分时，新行的ID会从3开始而非2，最终表数据类似：

id | videos_rating_user_fk | videos_rating_rating
1  | 1                     | 4
3  | 2                     | 5

我知道ID不需要“美观”，但跳号（比如30→51→82这类）让人困扰，还担心最终会达到UNSIGNED BIGINT的上限。没找到同类问题，若有相关讨论也烦请告知。

原因分析 #

这是MySQL（假设你使用的是MySQL）的正常设计行为：当执行INSERT ... ON DUPLICATE KEY UPDATE时，MySQL会先尝试插入新行，此时会为自增列预分配一个值；之后发现唯一键冲突，转而执行更新操作，但已经预分配的自增值不会被回滚——这就导致后续插入新行时，自增值会跳过已经预分配但未使用的数字，出现跳号。

解决方案 #

1. 先查询再执行对应操作（彻底解决跳号） #

这是最直接的方案，虽然多了一次查询，但能完全避免自增跳号问题：

// 先检查当前用户是否已对该视频评过分
$checkStmt = $dbCon->prepare("SELECT 1 FROM videos_rating WHERE videos_rating_video_fk = :video_id AND videos_rating_user_fk = :user_id");
$checkStmt->bindParam(':video_id', $videoId);
$checkStmt->bindParam(':user_id', $userId);
$checkStmt->execute();

if ($checkStmt->rowCount() > 0) {
    // 已存在评分，执行更新
    $updateStmt = $dbCon->prepare("UPDATE videos_rating SET videos_rating_rating = :video_rating WHERE videos_rating_video_fk = :video_id AND videos_rating_user_fk = :user_id");
    $updateStmt->bindParam(':video_id', $videoId);
    $updateStmt->bindParam(':user_id', $userId);
    $updateStmt->bindParam(':video_rating', $videoRating);
    $updateStmt->execute();
} else {
    // 不存在评分，执行插入
    $insertStmt = $dbCon->prepare("INSERT INTO videos_rating (videos_rating_video_fk, videos_rating_user_fk, videos_rating_rating) VALUES (:video_id, :user_id, :video_rating)");
    $insertStmt->bindParam(':video_id', $videoId);
    $insertStmt->bindParam(':user_id', $userId);
    $insertStmt->bindParam(':video_rating', $videoRating);
    $insertStmt->execute();
}

⚠️ 注意：如果你的系统处于高并发场景，一定要在查询和更新/插入操作之间添加事务或者行级锁，避免出现竞态条件（比如两个请求同时检测到不存在评分，都执行插入导致唯一键冲突）。

2. 调整InnoDB自增锁模式（效果有限） #

MySQL的innodb_autoinc_lock_mode参数控制自增锁的行为，默认值是2（交错模式），这种模式为了提升插入性能，会批量预分配自增值，容易加剧跳号。你可以将其修改为0（传统模式）：

-- 临时生效，重启MySQL后失效
SET GLOBAL innodb_autoinc_lock_mode = 0;

或者在my.cnf/my.ini中添加以下配置实现永久生效：

innodb_autoinc_lock_mode = 0

不过这个方案有明显缺点：传统模式会使用表级自增锁，会降低批量插入的性能，而且即使修改了这个参数，INSERT ... ON DUPLICATE KEY UPDATE的插入尝试仍然会预分配自增值后回滚，所以不一定能彻底解决你的问题，更适合批量插入导致的跳号场景。

3. 接受跳号（成本最低的长期方案） #

虽然跳号看起来不够“美观”，但UNSIGNED BIGINT的上限是18446744073709551615——即使每次更新都跳一个号，要达到这个上限需要的操作次数是天文数字，几乎不可能在实际业务中发生。而且自增ID的核心作用是作为主键唯一标识行，连续与否完全不影响业务逻辑。如果没有强制要求，这其实是最省心的方案。

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