如何在MySQL中批量查询含无效正则表达式的数据行？

我有一张表，其中某列存储用于匹配其他表数据行的正则表达式。执行如下查询：

SELECT st.id
FROM some_table st
WHERE '1234' REGEXP st.regexp;

只要该列的正则表达式合法或为null，查询就能正常运行；但如果存在无效正则表达式，整个查询会触发错误3685而失败。由于MySQL没有内置的VALID_REGEXP()校验函数，无法直接定位问题行。目前在插入/更新时通过SELECT '' REGEXP 'regexp-to-test'做校验，但仍可能有无效表达式混入百万级数据中，逐行排查不现实。请问如何用单条查询找出所有含无效正则表达式的行？

你可以通过创建自定义函数封装正则有效性校验逻辑，利用MySQL的异常处理捕获正则匹配时的语法错误，以此判断表达式是否合法，进而用单条查询定位问题行。

1. 创建正则校验函数 #

DELIMITER //
CREATE FUNCTION IS_VALID_REGEXP(p_regex VARCHAR(255)) RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' RETURN FALSE; -- 捕获正则语法错误
    SET @dummy = ('test' REGEXP p_regex);
    RETURN TRUE;
END //
DELIMITER ;

这个函数会尝试用测试字符串匹配传入的正则表达式，一旦触发语法错误就返回FALSE，合法则返回TRUE。你可以根据实际存储的正则长度调整VARCHAR(255)的取值。

2. 执行查询找出无效正则行 #

SELECT id, regexp
FROM some_table
WHERE regexp IS NOT NULL AND IS_VALID_REGEXP(regexp) = FALSE;

这条查询会直接筛选出所有非空且正则表达式无效的记录，无需逐行排查。

补充说明 #

• 该方法适用于MySQL 8.0.19及以上版本，低版本可能需要调整异常处理语法。
• 百万级数据下，函数执行会有一定性能开销，但远高于手动排查。

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