SQL新增id列插入值时数据错位至表底，求问题排查与解决

错误原因 #

你执行的INSERT INTO details(id) VALUES(...)语句，作用是向表中新增9条仅包含id字段的记录，而非给已存在的9行数据填充id值，这就是id出现在表底部新行的直接原因。

修复步骤 #

1. 清理错误插入的空行

现在表中存在9条无城市、州等核心信息的空行，先执行删除：

DELETE FROM details WHERE city IS NULL;

2. 给已有行填充id值

以下是三种常用解决方案：

方案一：设置自增主键（推荐）

既能批量给已有行赋值，还能让后续新增数据自动生成id：

• 第一步：给已有行批量设置初始id值（从111111开始递增）

SET @row_counter = 111110;
UPDATE details SET id = (@row_counter := @row_counter + 1);

• 第二步：将id设为自增主键

ALTER TABLE details MODIFY COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

方案二：手动指定每行id值

如果需要给每行分配特定id，用UPDATE配合唯一条件（避免重复city/state导致匹配错误）：

UPDATE details 
SET id = CASE 
    WHEN city = 'Albuquerque' THEN 111111
    WHEN city = 'New York' THEN 111112
    WHEN city = 'Pheonix' AND zip = 85006 THEN 111113
    WHEN city = 'Pheonix' AND zip = 85015 THEN 111114
    WHEN city = 'Seattle' THEN 111115
    WHEN city = 'Denver' AND zip = 80012 THEN 111116
    WHEN city = 'Austin' THEN 111117
    WHEN city = 'Denver' AND zip = 80033 THEN 111118
    WHEN city = 'Denver' AND zip = 80122 THEN 111119
END;

方案三：窗口函数批量赋值（适用于MySQL 8+、PostgreSQL等支持窗口函数的数据库）

按数据存储顺序（或指定顺序）批量赋值：

WITH numbered_rows AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num
    FROM details
)
UPDATE numbered_rows 
SET id = 111110 + row_num;

注：ORDER BY (SELECT NULL)表示按默认存储顺序排序，若需按特定字段排序，可替换为ORDER BY city, zip等。

内容的提问来源于stack exchange，提问作者Abhinav Arun Srivastava