为何存在疑似语法问题的MySQL UPDATE语句仍可运行？

我最近写了一个多表关联的SQL UPDATE语句，用来脱敏旧客户数据，其中处理英国邮编的SET子句写了这段代码：

oh.postcode = IF(oh.country = 'United Kingdom', IF(@cPos:=LOCATE(' ', TRIM(oh.postcode) > 0), SUBSTRING(UPPER(TRIM(oh.postcode)), 0, @cPos - 1), TRIM(REVERSE(SUBSTRING(REVERSE(UPPER(TRIM(oh.postcode))), 4)))), LEFT(LTRIM(oh.postcode), 2)),

（这里oh是表的别名）

我原本预期正确的写法应该是：

IF(@cPos:=LOCATE(' ', TRIM(oh.postcode)) > 0,

甚至加上括号明确优先级：

IF( ( @cPos:=LOCATE(' ', TRIM(oh.postcode)) ) > 0,

因为LOCATE的第二个参数不应该是布尔表达式，而且需要用括号确保变量@cPos赋值的是字符串偏移量，而不是布尔运算的结果。但奇怪的是原语句居然能运行，我想搞清楚这里的语法和关联性规则。

编辑补充：原语句的实际行为 #

经Damien_The_Unbeliever解答后发现，原语句里按空格拆分邮编的逻辑根本没有执行，始终在执行截取邮编末尾3位的分支——只是最终结果看起来和预期一致而已。同时我也想知道实现这个邮编脱敏需求的正确方式。

编辑2：验证正确逻辑的测试代码 #

后来我写了这段测试代码，发现它能正常运行：

SET @pc = ' W1s 3NW'; 
SELECT IF(@cPos:=LOCATE(' ', TRIM(@pc)), SUBSTRING(UPPER(TRIM(@pc)), 1, @cPos - 1), TRIM(REVERSE(SUBSTRING(REVERSE(UPPER(TRIM(@pc))), 4))));

这时候我才意识到自己忽略了MySQL字符串索引是1-based的特性。

问题分析与正确实现 #

1. 原错误语句的语法逻辑

原语句里的TRIM(oh.postcode) > 0是把修剪后的邮编字符串和数字0做比较：在MySQL中，字符串和数字比较时会尝试把字符串转成数字，失败的话会被当作0。所以TRIM(oh.postcode) > 0的结果是布尔值（1或0），然后这个布尔值被当作LOCATE的第二个参数——LOCATE会把它转换成字符串'1'或'0'，去在第一个参数（空格字符）里查找，结果自然是0（找不到）。

所以@cPos:=LOCATE(' ', ...)得到的是0，IF语句就会走else分支，也就是截取末尾3位的逻辑，这就是为什么原语句能“正常运行”但逻辑没生效。

2. 正确的英国邮编脱敏实现

英国邮编的格式通常是「 outward code + inward code 」，用空格分隔，脱敏时一般保留outward code即可。结合MySQL的特性，正确的写法可以是：

oh.postcode = CASE 
    WHEN oh.country = 'United Kingdom' THEN
        CASE
            -- 找到空格分隔符，取前半部分（outward code）
            WHEN LOCATE(' ', TRIM(oh.postcode)) > 0 THEN
                UPPER(SUBSTRING(TRIM(oh.postcode), 1, LOCATE(' ', TRIM(oh.postcode)) - 1))
            -- 没有空格的异常格式，截取前几位或者按规则处理
            ELSE
                UPPER(LEFT(TRIM(oh.postcode), 4))
        END
    -- 非英国邮编，保留前2位脱敏
    ELSE
        UPPER(LEFT(LTRIM(oh.postcode), 2))
END

如果想复用LOCATE的结果避免重复计算，也可以用我测试过的变量赋值写法，但要注意优先级：

oh.postcode = IF(oh.country = 'United Kingdom', 
    IF( (@cPos:=LOCATE(' ', TRIM(oh.postcode))) > 0,
        UPPER(SUBSTRING(TRIM(oh.postcode), 1, @cPos - 1)),
        UPPER(TRIM(REVERSE(SUBSTRING(REVERSE(TRIM(oh.postcode)), 4))))
    ),
    UPPER(LEFT(LTRIM(oh.postcode), 2))
)

这里给@cPos:=LOCATE(...)加上括号，确保先完成赋值再和0比较，同时利用MySQL中LOCATE找不到时返回0的特性，也可以简化成IF(@cPos:=LOCATE(' ', TRIM(oh.postcode)), ...)，因为0在布尔判断中会被当作false。

另外要注意MySQL的SUBSTRING是1-based索引，所以用SUBSTRING(str,1,length)而不是0开头的索引。

内容的提问来源于stack exchange，提问作者a.cornforth