MySQL实现字段匹配数组至少一个值的查询方法

前置信息说明 #

涉及两张业务表结构与样例数据如下：

• Users（用户表）
  • 字段：ID（用户主键ID）、Name（用户名）
  • 样例数据：ID=1对应用户Jacks，ID=2对应用户Sanaz
• UserInterests（用户兴趣表）
  • 字段：User_ID（关联用户表ID）、Interest（单条兴趣标签）
  • 样例数据：User_ID=1的兴趣标签为Sport、Music、Movie；User_ID=2的兴趣标签为Music、Football

本次传入的筛选参数为兴趣数组，匹配规则为用户兴趣命中数组中至少1个标签即符合筛选要求：

$interests = ['Music', 'Science', 'Computer'];

按样例数据推演，两个用户都带有Music标签，最终应返回Jacks、Sanaz两条用户记录。

可用SQL方案 #

方案1：IN子查询写法（逻辑直观，易维护） #

先从兴趣表筛选出所有命中目标标签的用户ID，再关联查询用户信息，加DISTINCT去重，避免单个用户命中多个标签时返回重复记录：

SELECT DISTINCT u.*
FROM Users u
WHERE u.ID IN (
    SELECT User_ID
    FROM UserInterests
    WHERE Interest IN ('Music', 'Science', 'Computer')
);

生产环境如果用预处理语句（如PDO、MyBatis），不要直接拼接SQL字符串，按数组元素个数生成对应占位符绑定参数即可，防范SQL注入。

方案2：JOIN关联写法（大数据量下性能更好） #

通过内连接直接关联两张表做标签匹配，同样加去重逻辑：

SELECT DISTINCT u.*
FROM Users u
INNER JOIN UserInterests ui 
  ON u.ID = ui.User_ID
WHERE ui.Interest IN ('Music', 'Science', 'Computer');

两种写法执行后返回结果一致：

常见踩坑提示 #

不要用LIKE '%关键词%'的模糊匹配实现标签筛选：一是全表模糊匹配性能极差，二是会出现误匹配，比如筛选Music时，带有MusicProduction标签的用户也会被错误命中，不符合精确标签匹配的业务要求。

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