如何在同表复制数据时显式插入从指定值开始自增的PK主键列

解决方案 #

MySQL 环境实现（适配你当前的SQL语法风格） #

借助MySQL用户会话变量即可实现逐行递增赋值，完整可运行SQL如下：

-- 初始化计数器，取值为你需要的第一个PK值减1
SET @pk_counter = 687476;

INSERT INTO `switch_person` (PK, `PTPK`, `EmployeeNamePK`, `SwitchTime`, `Half`, `date`, `Month`, `Year`, `SwitchDate`, `hoursWorked`, `LeadBy`, `Info`, `EmpDaysStatusId`, `TaskPK`, `TaskAssignCompletionId`, `SpendDays`, `LeaveId`, `Comments`)
SELECT 
  (@pk_counter := @pk_counter + 1) AS PK,
  `PTPK`, 
  `EmployeeNamePK`, 
  `SwitchTime`, 
  1, 
  `date`, 
  `Month`, 
  `Year`, 
  `SwitchDate`, 
  `hoursWorked`, 
  `LeadBy`, 
  `Info`, 
  `EmpDaysStatusId`, 
  `TaskPK`, 
  `TaskAssignCompletionId`, 
  `SpendDays`, 
  `LeaveId`, 
  `Comments`
FROM switch_person
WHERE SwitchDate = '2021-08-17'
AND Half = 2;

注意事项 #

• 执行前请确认687477及后续连续区间的PK值没有被表中现有数据占用，避免触发主键冲突报错
• 如果你不需要严格控制PK起始值，也可以先修改表的自增起始值：ALTER TABLE switch_person AUTO_INCREMENT = 687477;，插入时不指定PK字段，数据库会自动按自增规则赋值

其他数据库通用兼容写法 #

如果是PostgreSQL、SQL Server等支持窗口函数的数据库，可以用ROW_NUMBER()实现，不需要依赖会话变量：

INSERT INTO switch_person (PK, PTPK, EmployeeNamePK, SwitchTime, Half, date, Month, Year, SwitchDate, hoursWorked, LeadBy, Info, EmpDaysStatusId, TaskPK, TaskAssignCompletionId, SpendDays, LeaveId, Comments)
SELECT 
  687476 + ROW_NUMBER() OVER (ORDER BY PK) AS PK,
  PTPK, 
  EmployeeNamePK, 
  SwitchTime, 
  1, 
  date, 
  Month, 
  Year, 
  SwitchDate, 
  hoursWorked, 
  LeadBy, 
  Info, 
  EmpDaysStatusId, 
  TaskPK, 
  TaskAssignCompletionId, 
  SpendDays, 
  LeaveId, 
  Comments
FROM switch_person
WHERE SwitchDate = '2021-08-17'
AND Half = 2;

内容的提问来源于stack exchange，提问作者Muhammad Faizan Khan