如何基于名称前缀跨记录复制lat/long字段值并更新空值？

• 避免重复更新与无效值传递
  原语句若同组存在多条带-TS后缀的记录，会导致同一目标记录被多次更新；若这些-TS记录坐标不一致，还会出现结果不确定的问题。同时如果-TS记录本身的坐标是空值/NULL，也会把无效值同步给其他记录。建议先通过子查询聚合每组唯一的有效坐标：

  UPDATE tab AS t1
  JOIN (
      SELECT 
          SUBSTRING_INDEX(Name, '-', 1) AS group_prefix,
          lat,
          `long`
      FROM tab
      WHERE Name LIKE '%-TS' 
        AND lat IS NOT NULL AND lat != ''
        AND `long` IS NOT NULL AND `long` != ''
      GROUP BY group_prefix -- 确保每组仅取一条有效坐标记录
  ) AS t2 ON SUBSTRING_INDEX(t1.Name, '-', 1) = t2.group_prefix
  SET t1.lat = t2.lat,
      t1.`long` = t2.`long`
  WHERE t1.Name NOT LIKE '%-TS'
    AND (t1.lat IS NULL OR t1.lat = '' OR t1.`long` IS NULL OR t1.`long` = '');
  

• 仅更新需要修正的记录
  新增条件过滤掉已经有有效坐标的非-TS记录，减少不必要的数据库写入操作，提升执行效率。

• 先验证再执行
  正式更新前，先运行以下查询语句，确认待更新记录的旧值与新值是否符合预期，避免误操作：

  SELECT 
      t1.ID, 
      t1.Name, 
      t1.lat AS old_lat, 
      t2.lat AS new_lat, 
      t1.`long` AS old_long, 
      t2.`long` AS new_long
  FROM tab AS t1
  JOIN (
      SELECT 
          SUBSTRING_INDEX(Name, '-', 1) AS group_prefix,
          lat,
          `long`
      FROM tab
      WHERE Name LIKE '%-TS' 
        AND lat IS NOT NULL AND lat != ''
        AND `long` IS NOT NULL AND `long` != ''
      GROUP BY group_prefix
  ) AS t2 ON SUBSTRING_INDEX(t1.Name, '-', 1) = t2.group_prefix
  WHERE t1.Name NOT LIKE '%-TS'
    AND (t1.lat IS NULL OR t1.lat = '' OR t1.`long` IS NULL OR t1.`long` = '');
  

• 大数据量场景的索引优化
  如果表中数据量较大，SUBSTRING_INDEX函数的计算会拖慢查询速度。可以新增一个存储计算列并添加索引：

  ALTER TABLE tab ADD COLUMN group_prefix VARCHAR(100) AS (SUBSTRING_INDEX(Name, '-', 1)) STORED;
  CREATE INDEX idx_group_prefix ON tab(group_prefix);
  

  之后更新语句中的JOIN条件可以直接用t1.group_prefix = t2.group_prefix，大幅提升匹配效率。

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