在数据库中存储字符串数组 [英] Storing an Array of Strings in a database

问题描述

我有一个对象，该对象通过ORM保存到数据库中.该对象具有一个String数组，每个对象的数组长度可以变化.我想了解将String数组存储在db中的标准做法(例如，我是否应该将所有字符串作为csv存储在一个字段中，等等)?

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.) ?

推荐答案

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

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.

去规范化是寻找性能的常用方法.为此，您需要具有丰富的数据库经验(建模，访问等).这是经验丰富的DBA和商业智能专业人员所做的事情，但是如果您真的不知道自己在做什么，则不必尝试任何操作.

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.

因此，您的目标是设计规范化的数据库.为什么这违反数据库规范化?好吧，我们知道有几种正常形式"可以确定表对逻辑不一致和异常的免疫程度.如果您看一下第一个范式

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

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

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.

因此，当您将数组保存到字段中时，数据库甚至没有采用第一种普通形式.

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:

• 您不能使用JOIN
• 您不能使用索引
• 搜索，过滤，排序并不容易
• 失去参考的能力
• 如果您真的不知道自己在做什么，那么应用程序层的性能会更差.

确实，有些人(如Joomla一样)存储了实体的不太重要的数据，例如字段中的非关键配置值.最好的方法可能是使用 serialize(). 此处您对何时可以考虑这样做的解释.但是，只有当您真的知道自己在做什么并且确实需要它时，才应该这样做.

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

如果您想获得更多参考，可以阅读以下内容:

If you want more references, you can read this:

• https://dba.stackexchange.com/questions/4622/when-should-you -denormalize
• http://searchdatamanagement.techtarget.com/definition/denormalization

• http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.intro%2Fsrc%2Ftpc%2Fdb2z_denormalizationforperformance.htm
• https://dba.stackexchange.com/questions/4622/when-should-you-denormalize
• http://searchdatamanagement.techtarget.com/definition/denormalization

这也是答案:

• https://stackoverflow.com/a/4310112/2357411
• https://stackoverflow.com/a/16132444/2357411
• https://stackoverflow.com/a/5341286/2357411
• https://stackoverflow.com/a/10399902/2357411
• https://stackoverflow.com/a/17371729/2357411

• https://stackoverflow.com/a/4310112/2357411
• https://stackoverflow.com/a/16132444/2357411
• https://stackoverflow.com/a/5341286/2357411
• https://stackoverflow.com/a/10399902/2357411
• https://stackoverflow.com/a/17371729/2357411

这篇关于在数据库中存储字符串数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！