MySQL如何实现单列表值与另一列多值组的匹配关联查询

字段约定 #

两张表中name、sname字段均为varchar类型，id字段为int类型。

测试数据 #

• Table1

• Table2

需求 #

关联两张表，匹配Table1name字段去掉.txt后缀的前缀值，与Table2sname字段存储的类数组字符串中的元素，返回所有命中的记录。

原有查询问题 #

原SQL无法返回正确结果，原因有两点：

• 匹配逻辑写反：把Table1处理后的单值拼接成完整单元素数组格式，再去模糊匹配Table2的sname，仅当sname是只包含该值的单元素数组时才能匹配，无法命中多元素数组场景。
• 存在字段名笔误：将Table2的sname字段错写为scname。

SELECT
  t1.id,
  t1.name,
  t2.id AS t2_id,
  t2.sname
FROM table1 t1
INNER JOIN table2 t2
-- 匹配时给前缀加上单引号，避免test1误匹配test11这类同前缀不同值的场景
ON t2.sname LIKE CONCAT('%''', SUBSTRING_INDEX(t1.name, '.', 1), '''%');

上述语句执行后实际返回3条匹配记录：

你之前给出的预期结果存在笔误：Table2中不存在id=11、id=22的记录，且id=2对应的Table1记录name为test2.txt，并非test1.txt。如果业务要求仅返回指定的2条记录，可根据实际过滤规则增加WHERE条件即可。

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