如何在db中存储数组

I have to store an array in one field of a db. I know how to do that technically. My questions is, which version makes sense to do that.

我必须在db的一个字段中存储一个数组。我知道怎么做。我的问题是，哪个版本更有意义。

First a short introduction: I have categories in a table and need translations for each category for all languages. To start, I have 2 languages (later I will have more). Ok, let's say my category is car and the structure is like that:

首先是一个简短的介绍:我有一个表的类别，需要为所有语言的每个类别翻译。首先，我有两种语言(稍后我会有更多)。假设我的类别是car结构是这样的

id    name    translation

My first idea was:

我的第一个想法是:

id    name    translation
1     car     array('eng' => 'car', 'deu' => 'Auto')

The second idea was:

第二个想法是:

id    name    translation
1     car     'eng' => 'car', 'deu' => 'Auto'

The third idea was:

第三个想法是:

id    name    translation
1     car     'eng'='car','deu'='Auto'

At the end, the script reads the translation cell and loads into an array and returns the value for the given key 'eng' or 'deu' or whatever it comes later.

最后，脚本读取转换单元格并加载到一个数组中，并返回给定键“eng”或“deu”的值，或者后面的其他值。

I use PHP / MySQL

我使用PHP / MySQL

Thank you for any pros and cons.

谢谢你的赞成和反对。

Ivo

伊

4 个解决方案

#1

Try adjusting your database structure. This might be ideal:

尝试调整数据库结构。这可能是理想:

id    name    language    word
1     car     eng         car
2     car     deu         Auto

Primary Key (`name`,`language`)
Unique Key (`id`) (auto_increment)

In this way you can have as many (or as few) languages as you like.

这样你就可以拥有尽可能多的语言。

#2

You need to go about this completely different. Add another table, category_translation.

你需要做一个完全不同的事情。添加另一个表,category_translation。

You'd have category:

你会有类别:

id    name

and language:

和语言:

id    name

and category_translation:

和category_translation:

id    lang_id    category_id    translation

This way you'll have things beautifully separated.

这样你就能把东西完美地分开。

#3

you should have a translation table, with the language being one of the columns. id, name, language, value 1, car, eng, car 2, car, deu, Auto ...

您应该有一个翻译表，语言是其中的一列。id, name, language, value 1, car, eng, car 2, car, deu, Auto…

if you only have a single set of values, you can keep everything in this table. if you are looking up multiple values from different sources you can either add a lookup key to this table, or use the name as your lookup value (though indexing on text will not be as good as indexing on integer IDs)

如果只有一组值，则可以将所有值保存在该表中。如果您正在查找来自不同来源的多个值，您可以向该表添加查找键，或者使用名称作为查找值(尽管在文本上的索引不如在整数id上的索引好)

#4

Please do not store an array in your database in one column. At least serialize if it you have to do it

请不要将数组存储在数据库中的一个列中。如果必须要序列化，至少要序列化

serialize($array)

#1