SQL关联两张表保留无匹配NULL值：动物表与映射表正则匹配问题

问题排查 #

• 问题1：使用内连接JOIN，未匹配到标签的动物行会被直接过滤，无法返回NULL值，需要改为左连接LEFT JOIN。
• 问题2：正则匹配逻辑写反，regexp_contains第一个参数应为待检查的原字符串（动物名称），第二个参数为匹配规则，你当前的代码把两个参数逻辑搞反了，自然匹配不到任何结果。
• 问题3：正则边界规则有漏洞，你用的[^a-zA-Z]要求标签前后必须有非字母字符，但如果动物名刚好等于标签值（比如动物名为Cat，标签也为Cat），首尾没有额外字符就会匹配失败，需要调整为支持字符串首尾边界的规则。

正确实现代码 #

SELECT a.*, m.label
FROM `animals` a
LEFT JOIN `animals_label` m
ON REGEXP_CONTAINS(
  LOWER(a.animals),
  LOWER(CONCAT('(^|[^a-zA-Z])', m.label, '([^a-zA-Z]|$)'))
)

如果使用MySQL方言，替换为对应的REGEXP语法即可：

SELECT a.*, m.label
FROM `animals` a
LEFT JOIN `animals_label` m
ON LOWER(a.animals) REGEXP LOWER(CONCAT('(^|[^a-zA-Z])', m.label, '([^a-zA-Z]|$)'))

如果需要单个动物对应一行、多个标签合并展示，可以调整为分组聚合逻辑：

SELECT a.*, GROUP_CONCAT(m.label SEPARATOR ',') AS labels
FROM `animals` a
LEFT JOIN `animals_label` m
ON REGEXP_CONTAINS(
  LOWER(a.animals),
  LOWER(CONCAT('(^|[^a-zA-Z])', m.label, '([^a-zA-Z]|$)'))
)
GROUP BY a.id, a.animals -- 按animals表的主键字段分组即可

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