是否可以有一个包含多个值作为外键的 MySQL 列?

2022-01-15 00:00:00 foreign-keys mariadb mysql

我正在学习 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 ,

相关文章