具有逗号分隔值的单个MySQL字段 [英] Single MySQL field with comma separated values

问题描述

可能重复:
mysql是多列数组

Possible Duplicate:
mysql is array in multiple columns

我有两个表:

帖子表

PostID | Gallery
  1    | 4,7,8,12,13
  2    | 1,2,3,4
  3    | 5,8,9
  4    | 3,6,11,14

图库"中的值是图像"表中的主键:

The values in Gallery are the primary keys in the Images table:

图片表

ImageID | FileName
   1    | something.jpg
   2    | lorem.jpg
   3    | ipsum.jpg
   4    | what.jpg
   5    | why.jpg

之所以这样做，而不是仅将PostID键添加到图像"表中，是因为这些图像可以与许多不同的帖子相关联.我想我可以为关系添加另一个表，但是就我要添加到其中的jQuery脚本而言，逗号分隔的值更易于使用.

The reason I do this instead of just adding a PostID key to the Images table is because those images can be associated with a lot of different posts. I suppose I could add another table for the relationships, but the comma-separated value is easier to work with as far as the jQuery script I am using to add to it.

如果我在需要与PostID 3关联的图像的页面上，我可以运行哪种查询来为其输出所有文件名?

If I'm on a page that requires the images associated with PostID 3, what kind of query can I run to output all of the FileNames for it?

推荐答案

您可以使用以下解决方案:

You can use this solution:

SELECT b.filename
FROM posts a
INNER JOIN images b ON FIND_IN_SET(b.imageid, a.gallery) > 0
WHERE a.postid = 3

SQLFiddle

但是，您应该真正规范化设计，并在帖子和图片之间使用交叉引用表.这将是表示N:M(多对多)关系的最好，最有效的方法.不仅检索效率更高，而且将大大简化更新和删除图像关联.

However, you should really normalize your design and use a cross-reference table between posts and images. This would be the best and most efficient way of representing N:M (many-to-many) relationships. Not only is it much more efficient for retrieval, but it will vastly simplify updating and deleting image associations.

...但是就我要添加到其中的jQuery脚本而言，逗号分隔的值更易于使用.

...but the comma-separated value is easier to work with as far as the jQuery script I am using to add to it.

即使您用交叉引用表正确地表示了N:M关系，您仍然可以获得CSV格式的imageid:

Even if you properly represented the N:M relationship with a cross-reference table, you can still get the imageid's in CSV format:

假设您有一个包含主键字段(postid，imageid)的posts_has_images表:

Suppose you have a posts_has_images table with primary key fields (postid, imageid):

您可以使用GROUP_CONCAT()获取每个postid的imageid的CSV:

You can use GROUP_CONCAT() to get a CSV of the imageid's for each postid:

SELECT postid, GROUP_CONCAT(imageid) AS gallery
FROM posts_has_images
GROUP BY postid

这篇关于具有逗号分隔值的单个MySQL字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！