用左联接从两个表(非关系表)中获取行的MySQL

Question

我有以下两张表

table_article

id  subject     tags
---------------------
1   subject-1   2,4,5
2   subject-2   3,5
3   subject-3   1,2
4   subject-4   2,3,4
5   subject-5   3

table_tags

id  tag_name
---------------------
1   php
2   jQuery
3   css
4   mysql
5   java

我想要得到的结果

id => 1, subject => subject-1, tag_names => jQuery,mysql,java
id => 2, subject => subject-2, tag_names => css,java
id => 3, subject => subject-3, tag_names => php,jQuery

下面是我当前的尝试，它只返回第一个标记(例如，2，而不是第1行的2,4,5 )

 1    SELECT
 2        table_article.id,
 3        table_article.subject,
 4        GROUP_CONCAT(table_tags.tag_name) AS tag_names
 5    FROM
 6        table_article
 7    LEFT JOIN
 8        table_tags
 9    ON
10        (table_tags.tag_id IN (table_article.tags))
11    GROUP BY
12        table_article.id
13    LIMIT
14        3

结果是

id => 1, subject => subject-1, tag_names => jquery
id => 2, subject => subject-2, tag_names => css
id => 3, subject => subject-3, tag_names => php

这个问题发生在第10行 -> IN (table_article.tags)上。

我只是想不出该怎么解决这个问题，谁能帮上忙吗？

Answer 1

在这种情况下，我会使用IN，它不能工作，用FIND_IN_SET(table_tags.tag_id, table_article.tags) > 0替换它，你会没事的。尽管你真的应该让这一切正常化。

Answer 2

不能使用碰巧包含逗号的字符串作为离散值的列表。

换句话说，这是：

ON table_tags.tag_id IN (2,4,5)

与此不同：

ON table_tags.tag_id IN ('2,4,5')

像'2,4,5‘这样的字符串的数值是初始数字部分，第一个非数字字符之后的余数被忽略。所以字符串' 2 ,4,5‘的数值是2，它不会是一个错误，但是它不会得到您想要的结果，这与逗号分隔列表中的任何一个值都是匹配的。

MySQL有一个内置函数FIND_IN_SET()，它可以理解包含逗号分隔值的字符串。函数返回匹配值的位置，如果没有找到匹配值，则返回0。

ON FIND_IN_SET(table_tags.tag_id, '2,4,5') > 0

但这不能使用索引，它迫使您运行一个表扫描，这将损害您的性能。为了明确起见，我不建议在联接条件下使用此函数。

答案是：不要将标签存储在逗号分隔的列表中。

就像@Martin建议的那样，将每行一个标签存储在一个单独的表中。这样，您可以使用=查找正确的标记，甚至可以索引列以获得更好的性能。

Answer 3

我以前没有见过IN in ON (没有说它无效)，但是我会做ON table_tags.tag_id = table_article.tags)

所以你最终得到了多行

subject-1, query
subject-1, css
subject 2, query

然后，GROUP BY将压缩表，GROUP_CONCAT将获取所有缺失的标记。