MySQL拆分表字段LOT_LOCATION并正确更新原表的方法

• 未提前新增Zone Attribute列就执行更新，会导致列不存在、赋值失败
• MySQL的UPDATE语句SET子句是从左到右按顺序赋值，你之前的写法先修改了LOT_LOCATION的值，后面计算Zone Attribute时读取的是已经被去掉Z后缀的LOT_LOCATION，自然找不到Z字符，返回空值
• 原有SELECT逻辑存在疏漏：SUBSTRING_INDEX(LOT_LOCATION, 'Z', -1)仅返回Z后面的内容，不包含Z本身，无法得到预期的Z1/ZU格式后缀

• 第一步：给表新增Zone Attribute列，字段类型选择和LOT_LOCATION匹配的字符串类型，默认值设为空字符串

ALTER TABLE skynet_msa.Lab_WIP_History
ADD COLUMN `Zone Attribute` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'Z字符开头的区域后缀';

• 第二步：执行数据更新，推荐分两次更新避免赋值顺序干扰，逻辑更稳定

-- 先填充Zone Attribute值，此时LOT_LOCATION仍为原始值
UPDATE skynet_msa.Lab_WIP_History
SET `Zone Attribute` = IF(
    LOCATE('Z', LOT_LOCATION) = 0,
    '',
    CONCAT('Z', SUBSTRING_INDEX(LOT_LOCATION, 'Z', -1))
);

-- 再更新LOT_LOCATION，截断Z及后面的后缀
UPDATE skynet_msa.Lab_WIP_History
SET LOT_LOCATION = IF(
    LOCATE('Z', LOT_LOCATION) = 0,
    LOT_LOCATION,
    SUBSTRING_INDEX(LOT_LOCATION, 'Z', 1)
);

如果希望单次UPDATE完成，必须调整赋值顺序，先计算Zone Attribute再修改LOT_LOCATION：

UPDATE skynet_msa.Lab_WIP_History
SET
    `Zone Attribute` = IF(LOCATE('Z', LOT_LOCATION) = 0, '', CONCAT('Z', SUBSTRING_INDEX(LOT_LOCATION, 'Z', -1))),
    LOT_LOCATION = IF(LOCATE('Z', LOT_LOCATION) = 0, LOT_LOCATION, SUBSTRING_INDEX(LOT_LOCATION, 'Z', 1));

执行以下查询核对拆分结果：

SELECT LOT_LOCATION, `Zone Attribute` FROM skynet_msa.Lab_WIP_History;

返回结果符合预期：

注意：如果你的LOT_LOCATION中可能存在多个Z字符（比如XXXZ123Z45），上述逻辑会以最后一个Z为拆分点，如果你需要以第一个Z为拆分点，调整LOCATE和SUBSTRING_INDEX的匹配规则即可。

内容的提问来源于stack exchange，提问作者Gracella Q Sumarlin