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

问题描述

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

在下面的例子中，是否有可能在<$ c $ 标签列中的每个值c> log 表引用标记表中的 tag_id 列。

用户

  user_id | 
 1 | 
  

活动

  activitity_id | 
 1 
  

log

  user_id | activity_id |标签
 1 | 1 | 1,3,5＃多个外键？ 
  

标记

  tag_id | 
 1 | 
 2 | 
 3 | 
 4 | 
 5 |如果不可能，任何人都可以根据数据场景为最可行的解决方案提供逻辑以上？  
 
 
 相似的问题：   .com / questions / 7094170 / are-multiple-foreign-keys-in-a-single-field-possible>是否有多个外键在单个字段中？ 
 
 
 
 具有多个（条件）可能值的MySQL外键 
 
 
 
 可以引用一列作为多个外键      

如果你不希望组成一个中间人表连接两个表，你可以在字段中有一个逗号分隔值，你只需要使用 find_in_set mysql函数进行查询时使用find_in_set

$ b

  SEL ECT 
 log.user_id，log.activity_id，log.tags，
 GROUP_CONCAT（tags.name）作为taggedNames //假设标签表中有一个名为`name`的字段
 FROM 
 log 
 LEFT JOIN标记
 ON 
 FIND_IN_SET（tags.tag_id，log.tags）
 GROUP BY 
 log.activity_id 
  

GROUP_CONCAT会将一个字段组合在一起，并通过一个分隔符将它们分开，默认为，

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.

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?

users

user_id        |
1              |

activities

activitity_id  |
1

log

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

tags

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?

Similar Questions:

Are multiple foreign keys in a single field possible?

MySQL foreign key having multiple (conditional) possible values

it is possible to reference one column as multiple foreign keys

解决方案

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

USING 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 will group together a field and separate them by a deliminator, default is ,

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