MySQL REGEXP与LEFT用法解析及旧SQL查询代码重构咨询

这段代码的作用是对分类表cat的标题字段title做拆分取值，最终生成两个互斥的字段：

• int_cat：如果标题以连续数字开头，就把开头的连续数字转为整数类型存储
• int_cat_string：如果标题不以数字开头，就把完整的原标题存在这个字段

涉及的两个关键字/函数作用：

• REGEXP：MySQL的正则匹配操作符，这里的正则规则里^代表匹配字符串的起始位置，[0-9]代表匹配单个数字字符，堆叠多个[0-9]就是校验字符串开头是否存在对应长度的连续数字。
• LEFT(str, length)：MySQL原生字符串截取函数，作用是从指定字符串的最左端开始，截取前length个字符返回。

原代码的判断逻辑是从最长的4位开头数字开始逐次降级匹配：先判断是不是开头有4个连续数字，是就截前4位；不满足就判断是不是3个，是就截前3位，以此类推直到1位；如果开头完全没有数字，就返回NULL。后续int_cat会把截出来的数字串转为有符号整数，int_cat_string则通过判断前面的匹配结果是否为NULL，决定是存原标题还是存NULL。

原代码最大的问题是完全重复写了两遍相同的CASE匹配逻辑，不仅代码冗长难维护，还会让数据库执行两次完全一样的正则校验，产生不必要的性能开销。可以根据使用的MySQL版本选择对应的优化方案：

方案1：MySQL 8.0及以上版本（推荐） #

直接用REGEXP_SUBSTR函数一次性提取开头的所有连续数字，不需要逐层判断长度，代码量减少70%以上，且只需要做一次正则匹配：

CAST(REGEXP_SUBSTR(cat.title, '^[0-9]+') AS SIGNED) AS int_cat,
IF(REGEXP_SUBSTR(cat.title, '^[0-9]+') IS NULL, cat.title, NULL) AS int_cat_string,

这里正则^[0-9]+的含义是匹配字符串开头的1个及以上连续数字，不管长度是1位、4位还是更长，都能一次性提取出来，比原来硬编码判断1-4位数字的逻辑兼容性更好，遇到开头超过4位数字的标题也不会截断出错。

方案2：兼容MySQL 5.x旧版本 #

如果是MySQL 5.7及更早不支持REGEXP_SUBSTR的版本，可以通过派生表提前计算一次匹配结果，避免重复写CASE逻辑，同时可以把重复的正则写法用量词简化（[0-9]{n}代表n个连续数字，和原来写n个[0-9]效果完全一致，可读性更高）：

CAST(num_prefix AS SIGNED) AS int_cat,
IF(num_prefix IS NULL, cat.title, NULL) AS int_cat_string
FROM (
    SELECT
        cat.*,
        CASE
            WHEN cat.title REGEXP "^[0-9]{4}" THEN LEFT(cat.title,4)
            WHEN cat.title REGEXP "^[0-9]{3}" THEN LEFT(cat.title,3)
            WHEN cat.title REGEXP "^[0-9]{2}" THEN LEFT(cat.title,2)
            WHEN cat.title REGEXP "^[0-9]" THEN LEFT(cat.title,1)
            ELSE NULL
        END AS num_prefix
    FROM cat
) t

注意：这个写法保留了原代码最多匹配4位开头数字的逻辑，如果业务上允许开头数字超过4位，建议升级MySQL版本采用方案1的写法，避免出现数字截断错误。

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