如何创建使用正则表达式或LIKE进行输入验证的SQL存储过程？

本文共计1096个文字，预计阅读时间需要5分钟。

功能上，但存在明显界限——LIKE+仅支持简单通配符（%、_、[abc]），无法表达“至少一位数字+两位字母或邮箱格式这类逻辑。它适合做前缀校验（如WHERE @phone LIKE '1[3-9]%'或IN ('A01', 'B02')），但一旦规则变得复杂，就会写成冗长且嵌套的AND+LIKE字符串，可读性差，且漏判率高。”

实操建议：

• 用户手机号校验优先用 LEN(@phone) = 11 AND @phone LIKE '1[3-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'，比正则快，也兼容所有 SQL Server 版本
• 避免用 LIKE '%@input%' 做模糊校验——这会绕过所有索引，且不属于“输入校验”，属于查询逻辑
• 如果字段允许为空，校验前先用 ISNULL(@input, '') 统一处理，否则 NULL LIKE 'a%' 返回 UNKNOWN，导致校验逻辑失效

SQL Server 2017+ 怎么安全调用 STRING_SPLIT 和 REGEXP_LIKE？

SQL Server **没有原生 REGEXP_LIKE** ——这是 Oracle/MySQL 的函数。很多人搜到的“SQL Server 正则”方案，实际是靠 CLR 集成或调用外部脚本，生产环境禁用居多。真正可用的替代是：SQL Server 2017+ 的 STRING_SPLIT（用于分隔符校验）配合 CHECK CONSTRAINT 或存储过程内联判断；或用 PATINDEX 模拟部分正则能力。

例如邮箱基础校验：

IF PATINDEX('%[A-Za-z0-9._%+-]%@%[A-Za-z0-9.-]%.[A-Za-z]%', @email) = 0 OR LEN(@email) > 254 THROW 50000, 'Invalid email format', 1;

注意：PATINDEX 不支持量词（如 +、{2,}），[A-Za-z0-9._%+-] 只表示“其中一个字符”，不代表“至少一个”。所以它只能筛掉明显非法值，不能替代完整正则。

MySQL 存储过程中怎么用 REGEXP 做严格校验？

MySQL 原生支持 REGEXP（8.0+ 推荐用 REGEXP_LIKE，语义更清晰），但在存储过程中直接使用要注意两点：一是模式字符串需转义反斜杠，二是空值和 NULL 行为不一致。

典型写法：

IF @phone IS NULL OR @phone NOT REGEXP '^1[3-9][0-9]{9}$' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Phone number format error'; END IF;

关键细节：

• ^ 和 $ 必须加上，否则 '1234567890123' 也会匹配 '^1[3-9][0-9]{9}$'（因为子串匹配）
• MySQL 的 REGEXP 默认不区分大小写，字母范围写成 [a-zA-Z] 是冗余的；如需区分，加 BINARY 修饰：BINARY @str REGEXP '^[A-Z]+'
• 在 CREATE PROCEDURE 中，REGEXP 是表达式，不能直接当语句用——必须包在 IF、WHERE 或赋值语句中

PostgreSQL 存储过程里为什么推荐用 ~ 而不是 REGEXP_MATCHES？

PostgreSQL 的 ~ 操作符（区分大小写）和 ~*（不区分）性能更好、语法更紧凑，适合存储过程内快速判断。而 REGEXP_MATCHES 主要用于提取子组，校验场景属于杀鸡用牛刀。

例如身份证号校验（18位，末位可能是 X）：

IF NOT (id_card ~ '^\d{17}[\dXx]$') THEN RAISE EXCEPTION 'Invalid ID card format'; END IF;

容易踩的坑：

• PostgreSQL 正则默认是“子串匹配”，'abc123' ~ '\d+' 返回 true，所以务必加 ^ 和 $ 锚定全字符串
• X 和 x 必须显式写出：[\dXx]，不能写 \d|X|x（OR 在字符类外才生效）
• 如果参数来自用户输入，先用 TRIM(both from @input) 去首尾空格——空格常被忽略，但会导致正则失败

跨数据库移植时最麻烦的不是语法，而是正则引擎差异：MySQL 用 POSIX ERE，PostgreSQL 用 ARE（高级正则），SQL Server 基本靠绕。真要统一校验逻辑，不如把正则校验提到应用层做。