MySQL不同产品多语言翻译fallback基础包回退实现方案咨询

原查询失效原因 #

你之前的查询无法实现回退逻辑主要有两个问题：

• 第一个查询将languagetranslation.locale、languagetranslation.inscapexproductid的过滤条件写在WHERE子句中，会将LEFT JOIN隐式转为INNER JOIN，直接过滤掉高级包未配置的翻译条目
• 单独用UNION实现需要额外做去重处理，复杂度和性能都不如关联后优先取值的方案

最优实现方案 #

单语言查询（以en为例） #

SELECT 
  ld.languagekey,
  COALESCE(pt.translation, bt.translation) AS languagevalue,
  'en' AS locale,
  COALESCE(pt.inscapexproductid, bt.inscapexproductid) AS inscapexproductid
FROM languagedata ld
-- 关联基础包的英文翻译作为兜底
LEFT JOIN languagetranslation bt 
  ON ld.languageid = bt.languageid 
  AND bt.locale = 'en' 
  AND bt.inscapexproductid = 1
-- 关联高级包的英文翻译作为优先取值
LEFT JOIN languagetranslation pt 
  ON ld.languageid = pt.languageid 
  AND pt.locale = 'en' 
  AND pt.inscapexproductid = 2
-- 基础包为全量预置，过滤无有效翻译的无效key
WHERE bt.translation IS NOT NULL

COALESCE函数会按顺序返回第一个非NULL的参数值，这里实现了高级包自定义翻译优先，未配置则自动回退到基础包翻译的逻辑，返回结果无重复的languagekey

多语言同时查询（同时返回de、en两种语言结果） #

SELECT 
  ld.languagekey,
  COALESCE(pt.translation, bt.translation) AS languagevalue,
  l.locale,
  COALESCE(pt.inscapexproductid, bt.inscapexproductid) AS inscapexproductid
FROM languagedata ld
-- 生成需要支持的语言列表，避免漏返回locale
CROSS JOIN (SELECT 'de' AS locale UNION ALL SELECT 'en' AS locale) l
LEFT JOIN languagetranslation bt 
  ON ld.languageid = bt.languageid 
  AND bt.locale = l.locale 
  AND bt.inscapexproductid = 1
LEFT JOIN languagetranslation pt 
  ON ld.languageid = pt.languageid 
  AND pt.locale = l.locale 
  AND pt.inscapexproductid = 2
WHERE bt.translation IS NOT NULL

内容的提问来源于stack exchange，提问作者inscape experiences