SQL 如何在一列中存储数组或多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6360739/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to store array or multiple values in one column
提问by Phill Pafford
Running Postgres 7.4 (Yeah we are in the midst of upgrading)
运行 Postgres 7.4（是的，我们正在升级）
I need to store from 1 to 100 selected items into one field in a database. 98% of the time it's just going to be 1 item entered, and 2% of the time (if that) there will be multiple items.
我需要将 1 到 100 个选定项目存储到数据库中的一个字段中。98% 的情况下只会输入 1 个项目，2% 的情况下（如果那样）会有多个项目。
The items are nothing more than a text description, (as of now) nothing more than 30 characters long. They are static values the user selects.
这些项目只不过是一个文本描述，（截至目前）长度不超过 30 个字符。它们是用户选择的静态值。
Wanted to know the optimal column data type used to store the desired data. I was thinking BLOB but didn't know if this is a overkill. Maybe JSON?
想知道用于存储所需数据的最佳列数据类型。我在想 BLOB，但不知道这是否有点矫枉过正。也许JSON？
Also I did think of ENUM but as of now I can't really do this since we are running Postgres 7.4
我也确实想到了 ENUM 但到目前为止我真的不能这样做，因为我们正在运行 Postgres 7.4
I also wanted to be able to easily identify the item(s) entered so no mappings or referencing tables.
我还希望能够轻松识别输入的项目，因此无需映射或引用表。
回答by cwallenpoole
You have a couple of questions here, so I'll address them separately:
你在这里有几个问题，所以我将分别解决它们：
I need to store a number of selected items in one field in a database
我需要在数据库的一个字段中存储许多选定的项目
My general rule is: don't. This is something which all but requiresa second table (or third) with a foreign key. Sure, it may seem easier now, but what if the use case comes along where you need to actually query for those items individually? It also means that you have more options for lazy instantiation and you have a more consistent experience across multiple frameworks/languages. Further, you are less likely to have connection timeout issues (30,000 characters is a lot).
我的一般规则是：不要。这几乎需要第二个（或第三个）带有外键的表。当然，现在看起来似乎更容易了，但是如果用例出现在您需要单独查询这些项目的地方怎么办？这也意味着您有更多的惰性实例化选项，并且您在多个框架/语言中拥有更一致的体验。此外，您不太可能遇到连接超时问题（30,000 个字符很多）。
You mentioned that you were thinking about using ENUM. Are these values fixed? Do you know them ahead of time? If so this would be my structure:
您提到您正在考虑使用 ENUM。这些值是固定的吗？你提前认识他们吗？如果是这样，这将是我的结构：
Base table (what you have now):
基表（您现在拥有的）：
| id primary_key sequence
| -- other columns here.
Items table:
物品表：
| id primary_key sequence
| descript VARCHAR(30) UNIQUE
Map table:
地图表：
| base_id bigint
| items_id bigint
Map table would have foreign keys so base_id maps to Base table, and items_id would map to the items table.
Map 表会有外键，所以 base_id 映射到 Base 表，items_id 映射到 items 表。
And if you'd like an easy way to retrieve this from a DB, then create a view which does the joins. You can even create insert and update rules so that you're practically only dealing with one table.
如果您想要一种从数据库中检索它的简单方法，请创建一个执行连接的视图。您甚至可以创建插入和更新规则，以便您实际上只处理一张表。
What format should I use store the data?
我应该使用什么格式存储数据？
If you have to do something like this, why not just use a character delineated string? It will take less processing power than a CSV, XML, or JSON, and it will be shorter.
如果你必须做这样的事情，为什么不只使用一个字符划定的字符串呢？与 CSV、XML 或 JSON 相比，它将需要更少的处理能力，并且会更短。
What column type should I use store the data?
我应该使用什么列类型来存储数据？
Personally, I would use TEXT. It does not sound like you'd gain much by making this a BLOB, and TEXT, in my experience, is easier to read if you're using some form of IDE.
就个人而言，我会使用TEXT. 听起来好像通过将它设置为 a 并没有什么好处BLOB，而且TEXT根据我的经验，如果您使用某种形式的 IDE，则更易于阅读。
回答by Denis de Bernardy
Well, there is an array typein recent Postgres versions (not 100% about PG 7.4). You can even index them, using a GIN or GIST index. The syntaxes are:
好吧，在最近的 Postgres 版本中有一个数组类型（不是 100% 关于 PG 7.4）。您甚至可以使用 GIN 或 GIST 索引对它们进行索引。语法是：
create table foo (
bar int[] default '{}'
);
select * from foo where bar && array[1] -- equivalent to bar && '{1}'::int[]
create index on foo using gin (bar); -- allows to use an index in the above query
But as the prior answer suggests, it will be better to normalize properly.
但正如先前的答案所暗示的那样，正确地标准化会更好。
