如何将存储过程的参数与正则表达式（REGEXP）进行匹配对比

问题背景 #

需要在存储过程中对输入参数id进行正则格式验证，尝试用REGEXP实现但存在语法或逻辑问题，原代码如下：

DELIMITER &&  
CREATE PROCEDURE ValidationSalaryChange(
    IN changeDate DATE,
    IN id VARCHAR(11)
))  
BEGIN  
    IF NOT (SELECT id REGEXP '^\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}$') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'error';
    ELSE IF (changeDate > NOW()) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'error'; 
    END IF;
END &&  
DELIMITER ;  

问题分析与修正 #

原代码存在几个关键问题，修正后即可正常实现正则验证：

1. 语法错误：参数列表末尾多了一个括号，需删除；ELSE IF应改为ELSEIF（MySQL中同一层级条件判断的正确写法，避免嵌套IF导致逻辑混乱）
2. 正则转义问题：MySQL字符串中，要匹配字面量的.需用\\.转义；若要兼容所有MySQL版本，用[0-9]代替\\d更稳妥
3. 冗余写法：无需用SELECT包裹参数，直接判断id NOT REGEXP '正则表达式'即可

修正后的完整代码 #

DELIMITER &&  
CREATE PROCEDURE ValidationSalaryChange(
    IN changeDate DATE,
    IN id VARCHAR(11)
)  
BEGIN  
    -- 验证id格式：三段1-3位数字，以点分隔
    IF id NOT REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'id格式错误';
    ELSEIF changeDate > NOW() THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '变更日期不能晚于当前时间';
    END IF;
END &&  
DELIMITER ;  

关键说明 #

• 正则表达式^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$的含义：
  • ^ 匹配字符串起始位置
  • [0-9]{1,3} 匹配1-3位数字
  • \\. 匹配字面量的点（MySQL字符串中反斜杠需转义，实际传给REGEXP的是\.）
  • $ 匹配字符串结束位置
• 自定义错误信息，替代模糊的error，更便于定位问题

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