如何使用SQL更新单列内指定分组值 为id字段补全99999前缀

你当前执行SQL触发报错的核心原因有两个：

• id列本身是整数类型，concat函数拼接后生成的是字符串，直接将字符串赋值给整数类型的字段会触发类型不匹配错误
• where条件中直接用整数类型的id和字符串前缀'99999%'做like匹配，大部分数据库的隐式转换逻辑会失效，不仅匹配结果不符合预期，也可能触发类型报错

方案1：保留id为整数类型（需提前确认数值范围足够） #

如果确认加前缀后的id不会超出当前整数类型的取值范围（例如32位int最大支持到2147483647，99999开头的9位整数不会超出该范围），可以选择以下两种写法：

数值计算写法（性能更高） #

直接用数值运算代替字符串拼接，适配所有支持数值计算的SQL数据库：

UPDATE tablename
-- 1000对应原id的最大位数，例如原id最大是3位就乘1000，原id最大是4位就乘10000，可按需调整
SET id = id + 99999 * 1000
-- 过滤条件直接用数值判断，避免类型转换，已经加过前缀的id必然大于99999000，可按需调整阈值
WHERE id < 99999000;

字符串转换写法 #

如果需要严格匹配前缀逻辑，可显式完成类型转换后再操作，不同数据库的写法差异如下：

-- MySQL写法
UPDATE tablename
SET id = CAST(CONCAT('99999', CAST(id AS CHAR)) AS UNSIGNED)
WHERE CAST(id AS CHAR) NOT LIKE '99999%';

-- PostgreSQL写法
UPDATE tablename
SET id = CAST(CONCAT('99999', CAST(id AS VARCHAR)) AS BIGINT)
WHERE CAST(id AS VARCHAR) NOT LIKE '99999%';

-- SQL Server写法
UPDATE tablename
SET id = CAST(CONCAT('99999', CAST(id AS VARCHAR)) AS BIGINT)
WHERE CAST(id AS VARCHAR) NOT LIKE '99999%';

方案2：修改id列为字符串类型（更推荐，避免后续数值溢出风险） #

如果后续id还会变长，或者有频繁的字符串匹配需求，建议先修改字段类型再更新，彻底解决类型冲突问题：

-- 第一步：修改id字段类型为足够长度的字符串，长度可按需调整
ALTER TABLE tablename MODIFY COLUMN id VARCHAR(20);
-- 第二步：执行更新，无类型冲突问题
UPDATE tablename
SET id = CONCAT('99999', id)
WHERE id NOT LIKE '99999%';

• 执行更新前务必先执行SELECT语句验证过滤条件是否正确，避免误更新数据：

  SELECT * FROM tablename WHERE [你使用的过滤条件]
  

• 生产环境操作前建议先备份全表数据

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