如何在一列中存储数组或多个值 [英] How to store array or multiple values in one column

问题描述

运行 Postgres 7.4(是的，我们正在升级)

Running Postgres 7.4 (Yeah we are in the midst of upgrading)

我需要将 1 到 100 个选定项目存储到数据库的一个字段中.98% 的情况下只会输入 1 个项目，而 2% 的情况下(如果那样)会有多个项目.

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.

这些项目只不过是文本描述，(截至目前)长度不超过 30 个字符.它们是用户选择的静态值.

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.

想知道用于存储所需数据的最佳列数据类型.我在想 BLOB，但不知道这是否有点矫枉过正.也许是 JSON?

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?

我也确实想到了 ENUM，但由于我们正在运行 Postgres 7.4，因此目前我无法真正做到这一点

Also I did think of ENUM but as of now I can't really do this since we are running Postgres 7.4

我还希望能够轻松识别输入的项目，因此无需映射或引用表格.

I also wanted to be able to easily identify the item(s) entered so no mappings or referencing tables.

推荐答案

您在这里有几个问题，所以我将分别解决它们:

You have a couple of questions here, so I'll address them separately:

我的一般规则是:不要.这是除了需要一个带有外键的第二个(或第三个)表的东西.当然，现在看起来似乎更容易了，但是如果用例出现在您需要单独查询这些项目的地方怎么办?这也意味着您有更多的惰性实例化选项，并且您在多个框架/语言中拥有更一致的体验.此外，您不太可能遇到连接超时问题(30,000 个字符很多).

My general rule is: don't. This is something which all but requires a 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).

您提到您正在考虑使用 ENUM.这些值是固定的吗?你提前认识他们吗?如果是这样，这将是我的结构:

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:

基表(您现在拥有的):

Base table (what you have now):

| id primary_key sequence
| -- other columns here.

物品表:

| id primary_key sequence
| descript VARCHAR(30) UNIQUE

地图表:

| base_id  bigint
| items_id bigint

Map 表会有外键，所以 base_id 映射到 Base 表，items_id 映射到 items 表.

Map table would have foreign keys so base_id maps to Base table, and items_id would map to the items table.

如果您想要一种从数据库中检索它的简单方法，请创建一个执行连接的视图.您甚至可以创建插入和更新规则，这样您实际上只处理一张表.

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.

如果你必须做这样的事情，为什么不使用一个字符划定的字符串呢?与 CSV、XML 或 JSON 相比，它需要更少的处理能力，而且会更短.

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.

就个人而言，我会使用 TEXT.将其设为 BLOB 听起来并没有什么好处，而且 TEXT 根据我的经验，如果您使用某种形式的 IDE，则更易于阅读.

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.

这篇关于如何在一列中存储数组或多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！