MySQL 保存多语言数据的最佳数据库结构是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2227985/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's the best database structure to keep multilingual data?
提问by Thiago Belem
Possible Duplicate:
Schema for a multilanguage database
可能的重复：
多语言数据库的架构
Here's an example:
下面是一个例子：
[ products ]
id (INT)
name-en_us (VARCHAR)
name-es_es (VARCHAR)
name-pt_br (VARCHAR)
description-en_us (VARCHAR)
description-es_es (VARCHAR)
description-pt_br (VARCHAR)
price (DECIMAL)
The problem:every new language will need modify the table structure.
问题：每种新语言都需要修改表结构。
Here's another example:
这是另一个例子：
[ products-en_us ]
id (INT)
name (VARCHAR)
description (VARCHAR)
price (DECIMAL)
[ products-es_es ]
id (INT)
name (VARCHAR)
description (VARCHAR)
price (DECIMAL)
The problem:every new language will need the creation of new tables and the "price" field is duplicated in every table.
问题：每种新语言都需要创建新表，并且每个表中的“价格”字段都是重复的。
Here's another example:
这是另一个例子：
[ languages ]
id (INT)
name (VARCHAR)
[ products ]
id (INT)
price (DECIMAL)
[ translation ]
id (INT, PK)
model (VARCHAR) // product
field (VARCHAR) // name
language_id (INT, FK)
text (VARCHAR)
The problem:hard?
问题：难？
采纳答案by Gipsy King
Your third example is actually the way the problem is usually solved. Hard, but doable.
你的第三个例子实际上是通常解决问题的方式。很难，但可行。
Remove the reference to product from the translation table and put a reference to translation where you need it (the other way around).
从翻译表中删除对产品的引用，并将对翻译的引用放在您需要的地方（反之亦然）。
[ products ]
id (INT)
price (DECIMAL)
title_translation_id (INT, FK)
[ translation ]
id (INT, PK)
neutral_text (VARCHAR)
-- other properties that may be useful (date, creator etc.)
[ translation_text ]
translation_id (INT, FK)
language_id (INT, FK)
text (VARCHAR)
As an alternative (not especially a good one) you can have one single field and keep all translations there merged together (as XML, for example).
作为替代方案（不是特别好），您可以拥有一个字段并将所有翻译合并在一起（例如，作为 XML）。
<translation>
<en>Supplier</en>
<de>Lieferant</de>
<fr>Fournisseur</fr>
</translation>
回答by Gipsy King
Similar to method 3:
类似于方法3：
[languages]
id (int PK)
code (varchar)
[products]
id (int PK)
neutral_fields (mixed)
[products_t]
id (int FK)
language (int FK)
translated_fields (mixed)
PRIMARY KEY: id,language
So for each table, make another table (in my case with "_t" suffix) which holds the translated fields.
When you SELECT * FROM products, simply ... LEFT JOIN products_t ON products_t.id = products.id AND products_t.language = CURRENT_LANGUAGE.
因此，对于每个表，创建另一个表（在我的情况下带有“_t”后缀），其中包含已翻译的字段。当你SELECT * FROM products，简直了... LEFT JOIN products_t ON products_t.id = products.id AND products_t.language = CURRENT_LANGUAGE。
Not that hard, and keeps you free from headaches.
没那么难，让你免于头痛。
回答by Clément
In order to reduce the number of JOIN's, you could keep separate the translated and non translated in 2 separate tables :
为了减少 JOIN 的数量，您可以在 2 个单独的表中分别保留已翻译和未翻译的内容：
[ products ]
id (INT)
price (DECIMAL)
[ products_i18n ]
id (INT)
name (VARCHAR)
description (VARCHAR)
lang_code (CHAR(5))
回答by holygeek
At my $DAYJOB we use gettext for I18N. I wrote a plugin to xgettext.plthat extracts all English text from the database tables and add them to the master messages.pot.
在我的 $DAYJOB 中，我们对 I18N 使用 gettext。我为xgettext.pl编写了一个插件，它从数据库表中提取所有英文文本并将它们添加到主 messages.pot。
It works very well - translators deal with only one file when doing translation - the po file. There's no fiddling with database entries when doing translations.
它工作得很好 - 翻译人员在进行翻译时只处理一个文件 - po 文件。进行翻译时无需摆弄数据库条目。
回答by Tio
[languages] id (int PK) code (varchar)
[语言] id (int PK) 代码 (varchar)
[products]
id (int PK)
name
price
all other fields of product
id_language ( int FK )
I actually use this method, but in my case, it's not in a product point of view, for the various pages in my CMS, this work's quite well.
我其实是用这个方法的，但是在我的情况下，不是从产品的角度来看，对于我的CMS中的各个页面来说，这个工作还是相当不错的。
If you have a lot of products it might be a headache to update a single one in 5 or 6 languages... but it's a question of working the layout.
如果你有很多产品，用 5 或 6 种语言更新一个产品可能会很头疼……但这是一个如何布局的问题。
回答by chf
What about fourth solution?
第四个解决方案呢？
[ products ]
id (INT)
language (VARCHAR 2)
name (VARCHAR)
description (VARCHAR)
price (DECIMAL)
*translation_of (INT FK)*
*Translation_of* is FKof it self. When You add default language *translation_of* is set to Null. But when you add second language *translation_of* takes primary produkt language id.
*Translation_of* 是它自己的FK。添加默认语言时 *translation_of* 设置为 Null。但是当您添加第二语言时 *translation_of* 需要主要产品语言 ID。
SELECT * FROM products WHERE id = 1 AND translation_of = 1
In that case we get all translations for product with id is 1.
在这种情况下，我们会得到 id 为 1 的产品的所有翻译。
SELECT * FROM products WHERE id = 1 AND translation_of = 1 AND language = 'pl'
We get only product in Polish translation. Without second table and JOINS.
我们只获得波兰语翻译产品。没有第二个表和连接。
回答by AntonioCS
Have many to many relationship.
有多对多的关系。
You have your data table, languages table and a data_language table.
您有数据表、语言表和 data_language 表。
In the data_language table you have
在 data_language 表中你有
id, data_id, language_id
id、data_id、语言_id
I think that might work best for your.
我认为这可能最适合您。
回答by Petr Peller
We use this concept for our webiste (600k views per day) and (maybe surprisingly) it works. Sure along with caching and query optimalization.
我们将这个概念用于我们的网站（每天 60 万次浏览）并且（可能令人惊讶）它有效。当然还有缓存和查询优化。
[attribute_names]
id (INT)
name (VARCHAR)
[languages_names]
id (INT)
name (VARCHAR)
[products]
id (INT)
attr_id (INT)
value (MEDIUMTEXT)
lang_id (INT)
