MySQL如何基于同列值替换匹配规则用单条SQL更新目标行字段

核心实现逻辑 #

通过表自连接完成匹配更新：关联条件为带b的行去掉b后的c1值等于正常行的c1值，仅筛选需要更新的行缩小操作范围，避免全表扫描导致的性能问题。

各主流数据库单条SQL实现 #

注意将下方SQL中的your_table替换为实际表名。

MySQL 版本 #

UPDATE your_table t1
INNER JOIN your_table t2 
  ON REPLACE(t1.c1, 'b', '') = t2.c1
SET 
  t1.c2 = t2.c2,
  t1.c3 = t2.c3,
  t1.c4 = t2.c4
-- 仅筛选需要更新的行，大幅缩小扫描范围
WHERE t1.c1 LIKE '%b%'
  AND t1.c2 IS NULL 
  AND t1.c3 IS NULL 
  AND t1.c4 IS NULL;

PostgreSQL 版本 #

UPDATE your_table t1
SET 
  c2 = t2.c2,
  c3 = t2.c3,
  c4 = t2.c4
FROM your_table t2
WHERE REPLACE(t1.c1, 'b', '') = t2.c1
  AND t1.c1 LIKE '%b%'
  AND t1.c2 IS NULL 
  AND t1.c3 IS NULL 
  AND t1.c4 IS NULL;

SQL Server 版本 #

UPDATE t1
SET 
  t1.c2 = t2.c2,
  t1.c3 = t2.c3,
  t1.c4 = t2.c4
FROM your_table t1
INNER JOIN your_table t2
  ON REPLACE(t1.c1, 'b', '') = t2.c1
WHERE t1.c1 LIKE '%b%'
  AND t1.c2 IS NULL 
  AND t1.c3 IS NULL 
  AND t1.c4 IS NULL;

性能优化建议 #

• 如果b固定出现在c1字段末尾，可将关联条件替换为LEFT(t1.c1, LENGTH(t1.c1)-1) = t2.c1，比全字段REPLACE执行效率更高。
• 数据量较大的场景可给c1字段加普通索引，或给REPLACE(c1, 'b', '')创建函数索引，大幅提升关联匹配速度。
• 执行更新前可先将UPDATE语句改为SELECT查询验证匹配行数，避免误操作：

  SELECT COUNT(*) FROM your_table t1
  INNER JOIN your_table t2 ON REPLACE(t1.c1, 'b', '') = t2.c1
  WHERE t1.c1 LIKE '%b%' AND t1.c2 IS NULL AND t1.c3 IS NULL AND t1.c4 IS NULL;
  

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