在 MySQL 中使用子查询进行语言环境回退

Question

我有一个托管在线商店产品，支持各种数据类型的自定义字段。切入主题，任何人都可以通过添加到定义为的表来向任何记录添加额外的键/值对：

`storeId` varchar(20) NOT NULL,
`locale` varchar(100) NOT NULL,
`table` varchar(30) NOT NULL,
`id` varchar(50) NOT NULL,
`key` varchar(30) NOT NULL,
`value` mediumblob NOT NULL,
PRIMARY KEY (`storeId`, `locale`, `table`, `id`, `key`)

在欧洲，很多人希望支持多种语言，因此每个键可以有针对不同区域设置的多个值。每个商店都有一个默认区域设置（由商店所有者指定）和一个活动区域设置（由客户选择）。

我试图组合在一起的查询的行为如下：

• storeId、table 和 id 都是已知的，活动区域设置和默认语言环境。
• 如果某个键的值对于活动区域设置不可用，则返回默认区域设置的值。
• 如果默认区域设置的值也不可用，则返回任何其他区域设置的值。

我想出了一个非常尴尬的查询，但达到了预期的结果。假设商店 ID 为 1，表为 Products，ID 为 gadget1；默认区域设置为 en_AU，活动区域设置为 de_DE。这就是我们要查询的内容：

SELECT * FROM 
    (SELECT `key`, `value` 
    FROM nf_CustomFields 
    WHERE `storeId` = 1
        AND `table` = 'Products'
        AND `id` = 'gadget1'
    ORDER BY `locale`='de_DE' DESC, 
        `locale`='en_AU' DESC
    ) 
AS a 
GROUP BY `key`;

这可行，但它很丑陋并且似乎效率低下。我知道有更好的方法，但我无法确定。

有人能建议更好的东西吗？

非常感谢！

Answer 1

您可以尝试对自身进行 LEFT JOINing nf_CustomFields ，然后使用 COALESCE 忽略 LEFT JOIN 在没有本机语言行时将产生的 NULL。像这样的查询（未经测试并且可能不起作用）：

select coalesce(nl.`key`,   def.`key`),
       coalesce(nl.`value`, def.`value`)
from nf_CustomFields nl
left outer join nfCustomFields def
     on nl.`store`  = def.`store`
    and nl.`table`  = def.`table`
    and nl.`id`     = def.`id`
    and nl.`locale` = 'de_DE'
 where def.`locale` = 'en_AU'
   and def.`id`     = KNOWN
   and def.`store`  = KNOWN
   and def.`table`  = KNOWN

当然，KNOWN 会被替换为您的已知值，并且您将使用真正的动态值来代替 'de_DE'<当然是 /code> 和 'en_AU' 。