是否可以有一个包含多个值作为外键的 MySQL 列? [英] Is it possible to have a MySQL column containing multiple values as foreign keys?

问题描述

我正在学习 MySQL，并在 Fedora 19 中安装了 MariaDB.

I am learning MySQL and have MariaDB installed in Fedora 19.

我有一个场景，我需要一个列包含多个值，以减少列分配的可能冗余.

I have a scenario where I require a column to contain multiple values in order to reduce possible redundancy of column allocation.

在下面的示例中，是否可以让 log 表的 tags 列中的每个值引用tags 表?

In the example below, is it possible to have each value in the tags column of the log table reference the tag_id column in the tags table?

用户

user_id        |
1              |

活动

activitity_id  |
1

日志

user_id        |    activity_id    | tags
1              |    1              | 1,3,5 # multiple foreign keys?  

标签

tag_id         |
1              |
2              |
3              |
4              |
5              |

如果不可能，谁能提供基于上述数据场景的最可行解决方案的逻辑?

If it is not possible, could anyone provide the logic for the most feasible solution based on the data scenario above?

类似问题:

单个字段中是否有多个外键?

具有多个(条件)可能值的 MySQL 外键

可以引用一列作为多个外键

推荐答案

如果您不想组成一个中间人"表来链接两个表，您可以在字段中使用逗号分隔值，您只需查询时需要使用find_in_set mysql函数

If you do not wish to make up a "middle man" table for linking the two tables you can have a comma separated value in the field, you would just need to use the find_in_set mysql function when doing queries

使用 find_in_set

SELECT
   log.user_id, log.activity_id, log.tags,
   GROUP_CONCAT(tags.name) as taggedNames //This assumes there is a field called `name` in tags table
FROM
   log
LEFT JOIN tags
ON
   FIND_IN_SET(tags.tag_id,log.tags)
GROUP BY
   log.activity_id

GROUP_CONCAT 将一个字段组合在一起并用分隔符分隔它们，默认为 ,

GROUP_CONCAT will group together a field and separate them by a deliminator, default is ,

这篇关于是否可以有一个包含多个值作为外键的 MySQL 列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！