Rails——我应该使用哪种数据类型来存储datebase中的数组。

时间：2022-09-16 14:13:27

I'm receiving a response back from a GET api call which maps up 1:1 in my database. The response looks like so :

我收到了一个来自GET api调用的响应，它在我的数据库中映射了1:1。反应是这样的:

response =     {
      "status": "live",
      "templates": {},
      "responses": 0,
      "creator": "Marketing",
      "publish_url": "https://fluidsurveys.com/account/surveys/403975/publish/",
      "quota_start_date": null,
      "updated_at": "2014-01-18T16:05:20",
      "online_url": "http://fluidsurveys.com/surveys/marketing/tampa-bob-gilbertson-3084-nme-survey-emai/",
      "deploy_uri": "http://fluidsurveys.com/surveys/marketing/tampa-bob-gilbertson-3084-nme-survey-emai/",
      "settings_url": "https://fluidsurveys.com/account/surveys/403975/settings/",
      "groups": [],
      "quota_end_date": null,
      "offline_url": "http://fluidsurveys.com/surveys/marketing/tampa-bob-gilbertson-3084-nme-survey-emai/offline/",
      "responses_url": "https://fluidsurveys.com/account/surveys/403975/responses/",
      "style_url": "https://fluidsurveys.com/account/surveys/403975/theme/",
      "id": 403975,
      "upgrade_url": "https://fluidsurveys.com/account/surveys/403975/upgrade-version/",
      "name": "Bob Gilbertson 3084453  NME Survey Emai",
      "uri": "https://fluidsurveys.com/api/v2/surveys/403975/",
      "invites_url": "https://fluidsurveys.com/account/surveys/403975/publish/invites/",
      "created_at": "2014-01-17T21:47:52",
      "tags": [
        "austin",
        "nme"
      ],
      "preview_url": "http://fluidsurveys.com/surveys/marketing/tampa-bob-gilbertson-3084-nme-survey-emai/?TEST_DATA=",
      "report_url": "https://fluidsurveys.com/account/surveys/403975/reports/",
      "version": 4.0,
      "updated_at_text": "10:05 am",
      "edit_url": "https://fluidsurveys.com/account/surveys/403975/edit/"
    }

As you can see, there are a few arrays I'm getting back in this response. I would like to store response["tags"], for example, in my database on my Survey model.

正如您所看到的，在这个响应中我得到了一些数组。例如，我想在我的调查模型的数据库中存储响应[“标签”]。

However, when I try this, I get an error:

然而，当我尝试这个时，我得到了一个错误:

TypeError (can't cast Array to string):

类型错误(不能将数组转换为字符串):

How can I solve this issue? Should I change the datatype from string to something else, or is there something else I need to do to allow arrays to be stored in a DB? Also, as a bonus, is storing arrays in a DB bad practice?

我如何解决这个问题?我应该将数据类型从字符串更改为其他类型，还是需要做一些其他事情来允许数组存储在DB中?另外，作为奖励，在DB中存储数组有什么不好的做法吗?

3 个解决方案

#1

1  

There are several options here:

这里有几个选项:

  1. Because it's tags we're dealing with, you could store them as comma-seperated values, while still having a String based column:

    因为它是我们要处理的标签，你可以将它们存储为逗号分隔的值，同时仍然有一个基于字符串的列:

    person.tags = response['tags'].join(',') # Stores "austin,nme"

  2. If you want to be able to query individual tags (and index them), it might be a good option to store tags individually. For this, you'll want to create a model Tag which has a many-to-many relationship to your Person class.

    如果您希望能够查询单个标记(并索引它们)，那么单独存储标签可能是一个不错的选择。为此，您将希望创建一个模型标记，该标记与Person类具有多对多关系。

  3. Last, you can serialize your array to YAML and store it as a string in the database:

    最后，可以将数组序列化为YAML，并将其作为字符串存储在数据库中:

    # When storing
person.tags = YAML::dump(response['tags'])

# When fetching from the database
tags = YAML::load(person.tags)

    You can ease your job by moving this logic to Model accessors. Let's say you keep your tags under a Person model:

    您可以通过将这个逻辑移到模型访问器来简化您的工作。假设你把标签放在一个人模型下面:

    class Person
  def tags=(value)
    self.tags = YAML::dump(value)
  end

  def tags
    YAML::load(super)
  end
end

EDIT: My favourite approach would be the second one, that's mainly because I like to keep my database clean and structured. It's not bad practice to store documents (arrays, json, blobs etc.) in the database, as long as you know what you're doing and why you are doing this. See the whole NoSQL vs structured database debate. The first option I suggested might cover your needs, without added complexity.

编辑:我最喜欢的方法是第二种，这主要是因为我喜欢保持我的数据库整洁和结构化。在数据库中存储文档(数组、json、blobs等)是不错的做法，只要您知道自己在做什么以及为什么要这样做。请参阅整个关于NoSQL vs结构化数据库的争论。我建议的第一个选项可以满足您的需求，而不会增加复杂性。

#2

1  

By default Rails doesn't allow arrays to be stored in databases. You have to use the serialize function in order to do that. I've never personally done it because I haven't had to, but you can find more information about it here: Storing arrays in database using ActiveRecord

默认情况下，Rails不允许数组存储在数据库中。为此，必须使用serialize函数。我个人从来没有这样做过，因为我没有必要这么做，但是您可以在这里找到关于它的更多信息:使用ActiveRecord在数据库中存储数组

#3

1  

Also, as a bonus, is storing arrays in a DB bad practice?

另外，作为奖励，在DB中存储数组有什么不好的做法吗?

Storing arrays in a db is denormalized, which is generally poor practice as it prevents accessing it in a relational, declarative way with sql. Best practice would be to store the elements of the array in an associated table, and write active record relationships for them like has_many and belongs_to.

在db中存储数组是非规范化的，这通常是糟糕的实践，因为它阻止使用sql以关系的、声明的方式访问数组。最佳实践是将数组的元素存储在关联的表中，并为它们编写活动记录关系，如has_many和belongs_to。

#1

1  

There are several options here:

这里有几个选项:

  1. Because it's tags we're dealing with, you could store them as comma-seperated values, while still having a String based column:

    因为它是我们要处理的标签，你可以将它们存储为逗号分隔的值，同时仍然有一个基于字符串的列:

    person.tags = response['tags'].join(',') # Stores "austin,nme"

  2. If you want to be able to query individual tags (and index them), it might be a good option to store tags individually. For this, you'll want to create a model Tag which has a many-to-many relationship to your Person class.

    如果您希望能够查询单个标记(并索引它们)，那么单独存储标签可能是一个不错的选择。为此，您将希望创建一个模型标记，该标记与Person类具有多对多关系。

  3. Last, you can serialize your array to YAML and store it as a string in the database:

    最后，可以将数组序列化为YAML，并将其作为字符串存储在数据库中:

    # When storing
person.tags = YAML::dump(response['tags'])

# When fetching from the database
tags = YAML::load(person.tags)

    You can ease your job by moving this logic to Model accessors. Let's say you keep your tags under a Person model:

    您可以通过将这个逻辑移到模型访问器来简化您的工作。假设你把标签放在一个人模型下面:

    class Person
  def tags=(value)
    self.tags = YAML::dump(value)
  end

  def tags
    YAML::load(super)
  end
end

EDIT: My favourite approach would be the second one, that's mainly because I like to keep my database clean and structured. It's not bad practice to store documents (arrays, json, blobs etc.) in the database, as long as you know what you're doing and why you are doing this. See the whole NoSQL vs structured database debate. The first option I suggested might cover your needs, without added complexity.

编辑:我最喜欢的方法是第二种，这主要是因为我喜欢保持我的数据库整洁和结构化。在数据库中存储文档(数组、json、blobs等)是不错的做法，只要您知道自己在做什么以及为什么要这样做。请参阅整个关于NoSQL vs结构化数据库的争论。我建议的第一个选项可以满足您的需求，而不会增加复杂性。

#2

1  

By default Rails doesn't allow arrays to be stored in databases. You have to use the serialize function in order to do that. I've never personally done it because I haven't had to, but you can find more information about it here: Storing arrays in database using ActiveRecord

默认情况下，Rails不允许数组存储在数据库中。为此，必须使用serialize函数。我个人从来没有这样做过，因为我没有必要这么做，但是您可以在这里找到关于它的更多信息:使用ActiveRecord在数据库中存储数组

#3

1  

Also, as a bonus, is storing arrays in a DB bad practice?

另外，作为奖励，在DB中存储数组有什么不好的做法吗?

Storing arrays in a db is denormalized, which is generally poor practice as it prevents accessing it in a relational, declarative way with sql. Best practice would be to store the elements of the array in an associated table, and write active record relationships for them like has_many and belongs_to.

在db中存储数组是非规范化的，这通常是糟糕的实践，因为它阻止使用sql以关系的、声明的方式访问数组。最佳实践是将数组的元素存储在关联的表中，并为它们编写活动记录关系，如has_many和belongs_to。

标签：

相关文章