mysql in 字符串数组,在数据库中存储字符串数组

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

解决方案

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.

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

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:

You cannot use 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

If you want more references, you can read this:

And also this SO answers: