MYSQL 连接逗号分隔查询

2021-12-17 00:00:00 join database mysql

我四处寻找，一无所获.

I have searched around and came up with nothing.

我有 2 个表，不必为每篇显示我需要以某种方式加入它们的帖子查询数据库.

I have 2 tables and to not have to query the database for every post that shows i need to join them somehow.

我想从具有 post 表中 pics 字段的 id 的 pics 表中获取 url.现在我的问题是:pics 字段是一个逗号分隔的列表"(4,1 或 32,4,32,2)，因为每个帖子通常有不止一张图片.

I want to get the url from the pics table that have the id of the pics field in posts table. Now heres my problem: the pics field is a commma separated "list" (4,1 or 32,4,32,2), because every post usually have more than one picture.

表设置:

帖子:

 id | header | text | pics
| 1     xxx     xxx    3,1     
| 2     xxx     xxx    2,10,4     
| 3     xxx     xxx    16,17,18,19     
| 4     xxx     xxx    11,12,13

图片:

id | name | url
| 1   xxx   xxx    
| 2   xxx   xxx        
| 3   xxx   xxx          
| 4   xxx   xxx          
| 10  xxx   xxx         
| 11  xxx   xxx         
| 12  xxx   xxx                  
| 13  xxx   xxx          
| 16  xxx   xxx          
| 17  xxx   xxx        
| 18  xxx   xxx

推荐答案

我强烈建议您修复当前的数据库结构，以免将数据存储在逗号分隔的列表中.您应该按照如下方式构建表格:

I strongly advise that you fix your current database structure so you are not storing the data in a comma separated list. You should structure your tables similar to the following:

CREATE TABLE posts
    (`id` int, `header` varchar(3), `text` varchar(3))
;

CREATE TABLE pics
    (`id` int, `name` varchar(3), `url` varchar(3))
;

CREATE TABLE post_pics
    (`post_id` int, `pic_id` int)
;

然后您可以通过加入表格轻松获得结果:

Then you can easily get a result by joining the tables:

select p.id,
  p.header,
  p.text,
  c.name,
  c.url
from posts p
inner join post_pics pp
  on p.id = pp.post_id
inner join pics c
  on pp.pic_id = c.id;

参见 SQL Fiddle 和演示.

如果你不能改变你的表，那么你应该能够使用FIND_IN_SET进行查询:

If you cannot alter your table, then you should be able to query using FIND_IN_SET:

select p.id, p.header, p.text, p.pics,
  c.id c_id, c.name, c.url
from posts p
inner join pics c
  on find_in_set(c.id, p.pics)

参见SQL Fiddle with Demo.

编辑，如果您希望数据显示为逗号分隔的列表，那么您可以使用GROUP_CONCAT.

Edit, if you want the data displayed as a comma-separated list then you can use GROUP_CONCAT.

查询 1:

select p.id,
  p.header,
  p.text,
  group_concat(c.name separator ', ') name,
  group_concat(c.url separator ', ') url
from posts p
inner join post_pics pp
  on p.id = pp.post_id
inner join pics c
  on pp.pic_id = c.id
group by p.id, p.header, p.text;

参见SQL Fiddle with Demo

查询 2:

select p.id, p.header, p.text, p.pics,
  group_concat(c.name separator ', ') name,
  group_concat(c.url separator ', ') url
from posts p
inner join pics c
  on find_in_set(c.id, p.pics)
group by p.id, p.header, p.text, p.pics;

参见SQL Fiddle with Demo

相关文章