MySQL中REGEXP匹配异常：关联表查询时数值超10001结果不符

看起来你遇到的核心问题是：用bar表字段存储的正则表达式匹配foo表数据时，结果和直接写死正则的情况不一致，尤其是当test_value包含0（比如10001）时出现不符合预期的匹配。咱们一步步拆解原因和解决方案：

1. 最可能的根源：正则字段存在隐藏字符或尾部空格 #

你提到把CONCAT(bar.test_reg)换成硬编码的'^([1-9]*)$'就正常，这说明存储在bar.test_reg里的内容和你预期的不一致。常见的两种情况：

• 如果test_reg字段是CHAR类型，MySQL会自动填充空格到字段定义的长度，比如定义成CHAR(20)的话，存储的正则会变成^([1-9]*)$ （尾部带空格），直接破坏了正则的匹配逻辑；
• 字段里可能混入了换行符、制表符这类看不见的隐藏字符。

验证方法 #

执行这条SQL，对比存储的正则和硬编码正则的十六进制值，就能快速确认是否有差异：

SELECT 
  HEX(test_reg) AS stored_reg_hex,
  HEX('^([1-9]*)$') AS expected_reg_hex
FROM bar WHERE id='test';

如果两个值不一样，说明存储的正则确实存在脏数据。

2. 快速修复方案 #

如果是空格问题，直接在查询里用TRIM()去掉首尾空格即可：

SELECT * 
FROM foo 
RIGHT JOIN bar ON foo.id=bar.id 
WHERE test_value NOT REGEXP TRIM(bar.test_reg);

如果是隐藏字符，需要先清理bar表的脏数据：

UPDATE bar 
SET test_reg = TRIM(REPLACE(REPLACE(test_reg, '\n', ''), '\t', '')) 
WHERE id='test';

3. 长期优化建议 #

• 修改字段类型：把test_reg从CHAR改成VARCHAR，彻底避免MySQL自动填充空格的问题；
• 修正正则表达式：你当前的^([1-9]*)$允许匹配空字符串（*表示0次或多次），如果你的需求是匹配至少一个1-9的数字（不含0），应该改成^[1-9]+$（+表示1次或多次），这样更贴合实际业务逻辑。

验证测试 #

你可以先单独测试存储的正则是否能正确匹配10001：

SELECT '10001' REGEXP test_reg FROM bar WHERE id='test';

如果返回1（表示匹配），那肯定是存储的正则有问题；如果返回0（不匹配），再检查关联查询的逻辑是否有误。

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