将数组保存在数据库中或创建不同的列 [英] Save array in database or make different columns

问题描述

我正在创建一个项目，我将让某人从 20 个类别中选择最多 3 个类别.因为它总是最多 3 个类别，所以在数据库中再创建 3 个列(category_1、category_2)对我来说并不重要, category_3).

I'm creating a project where I will let somebody choose maximum 3 categories from 20. Because it's always going to be maximum 3 categories, it's not that much for me to just create 3 more columns in the database (category_1, category_2, category_3).

我想象在插入时，不是保存在 3 个不同的列中，而是将数组保存到类别"列中.

I imagine in the time of insert, instead of saving in 3 different columns, to just save the array into a "categories" column.

我的问题是，在获取信息的那一刻.如果我说搜索具有 1 个特定类别的每条记录，什么会更快?为了它查看 1 列是一个数组(我想像一个 LIKE 或类似的东西)还是查看 3 列?

My question is, in the moment of getting the information. If I do let's say a search for each record that has 1 specific category, what would be faster? For it to look into 1 column that is an array ( I imagine with a LIKE or something like that ) or to look in 3 columns?

我希望我的问题很清楚.谢谢.

I hope my question is clear. Thanks.

推荐答案

不要在数据库列中存储逗号分隔的列表(数组").使用交叉引用表.

Don't store a comma-separated list ("array") in a database column. Use a cross-reference table.

(如果您想在数据库中强制执行不超过 3 个类别"的条件，您可以在交叉引用表中添加一个附加列 category_number.理想情况下，您将使用检查将该列限制为值 1、2 和 3，但 MySQL 不支持检查约束；相反，您可以将 category_number 列设为另一个表 permitted_category_number 的外键其中有三行.)

(If you want to enforce the "no more than 3 categories" condition in the database, you can add an additional column category_number to the cross-reference table. Ideally you would then use a check constraint to restrict that column to the values 1, 2 and 3, but MySQL does not support check constraints; instead you might make the category_number column a foreign key to another table permitted_category_number which has three rows.)

这篇关于将数组保存在数据库中或创建不同的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！