在数据库中存储字符串数组

I have an object that I save to a database through ORM. The object has an array of Strings and the array length can vary per object.I want to know the standard practice for storing the array of String in the db (for example, should I store all the strings in one field as a csv, etc.) ?

我有一个通过ORM保存到数据库的对象。该对象有一个字符串数组,数组长度可以根据对象而变化。我想知道在数据库中存储String数组的标准做法(例如,我应该将所有字符串存储在一个字段中作为csv等。)?

1 个解决方案

#1

I guess you have a MySql, relational database. As a first approach, you have to think that inserting any kind of composed data (CSV, JSON, serialize()) in a field in a relational database, is something that you always should avoid. That was one of the first thing that i learn when studing databases at the university. This is because when you desing a database, your first approach should be Database normalization.

我猜你有一个MySql,关系数据库。作为第一种方法,您必须考虑在关系数据库的字段中插入任何类型的组合数据(CSV,JSON,serialize()),这是您应始终应避免的。这是我在大学学习数据库时学到的第一件事。这是因为当您设计数据库时,您的第一个方法应该是数据库规范化。

Denormalization is something that is commonly used when looking for perfomance. For doing this, you need to have a great experience in database (modeling, accesing, etc). This is something that experienced DBA and Business Intelligence professionals do, but its not anything you have to try if you dont really know what you are doing.

非规范化是在寻找性能时常用的东西。为此,您需要在数据库(建模,访问等)方面拥有丰富的经验。这是经验丰富的DBA和商业智能专业人士所做的事情,但如果你真的不知道自己在做什么,那么你不必尝试。

So, your goal is to desing a normalized database. Why this is against database normalization? well, we know that there are several "normal forms", that determine a table's degree of immunity against logical inconsistencies and anomalies. If you take a look at the definition of the First normal form

所以,你的目标是设计一个规范化的数据库。为什么这是针对数据库规范化的?好吧,我们知道有几种“正常形式”可以确定表格对逻辑不一致和异常的免疫程度。如果你看一下第一范式的定义

First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain.

第一范式(1NF)是关系数据库中关系的属性。如果每个属性的域仅包含原子值,并且每个属性的值仅包含该域中的单个值,则关系为第一范式。

So, when you save an array in a field, your database is not even in the first normal form.

因此,当您在字段中保存数组时,您的数据库甚至不是第一个普通形式。

Some of the practical reasons for not doing this are:

不这样做的一些实际原因是:

You cannot use JOIN

你不能使用JOIN

You cannot use indexes

您不能使用索引

Searching, filtering, sorting, is not easy

搜索,过滤,排序并不容易

The ability of making references is lost

失去了参考的能力

If you dont really know what you are doing, the performance in the application layer is worse.

如果您真的不知道自己在做什么,那么应用程序层的性能会更差。

It is true that some people (like Joomla does) store less important data of the entity, such as non-critical configuration values in a field. The best approach for this probably would be using serialize(). Here you have an explanation on when you can consider to do this. But again this is something you should only do if you really know what you are doing, and you really need it

确实有些人(像Joomla那样)存储了实体中不太重要的数据,例如字段中的非关键配置值。对此最好的方法可能是使用serialize()。在这里,您可以解释何时可以考虑这样做。但这又是你应该做的事情,如果你真的知道你在做什么,你真的需要它

If you want more references, you can read this:

如果您想要更多参考,可以阅读:

And also this SO answers:

而这个SO答案:

#1