MySQL如何判断同表同一行某字段是否包含另一字段值并更新

场景说明 #

现有license执照表，包含2个核心字段：

• type：执照类型
• number：执照编号
  按照业务规则，number字段需存储执照票面印刷的完整编号，格式为类型前缀+数字编号。当前存量数据库中部分数据符合规则，部分数据缺失前缀，样例数据如下：

表中第3、4条为符合规则的正确数据，需求为筛选出前2条缺失类型前缀的异常数据，将number字段更新为拼接type前缀的标准格式。

报错情况 #

最初尝试用如下SQL筛选异常数据时，执行抛出#1064语法错误：

Select *
from licenses
WHERE number NOT LIKE type+'%'

已检索到的公开方案均为判断某列值是否存在于另一列的全表数据中，无法实现同一行内判断number字段是否包含当前行type字段值的逻辑。

错误原因与正确写法 #

报错根因 #

MySQL中+运算符仅支持数值加法运算，不支持字符串拼接，直接写type+'%'时，数据库会尝试将两个字段转为数值做加法，既不符合语法预期也无法实现前缀匹配的逻辑。字符串拼接需使用CONCAT()函数完成。

筛选异常数据的正确SQL #

SELECT *
FROM licenses
WHERE number NOT LIKE CONCAT(type, '%');

批量更新异常数据的SQL #

执行更新前必须先运行上述SELECT语句，确认筛选出的异常数据符合预期，避免误改。

UPDATE licenses
SET number = CONCAT(type, number)
WHERE number NOT LIKE CONCAT(type, '%');

执行完成后，所有缺失前缀的编号都会自动补全对应行的type前缀，和存量正确数据格式保持一致。

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