MySQL本地化查询设计：按优先级匹配用户区域对应内容

这种多区域适配+默认回退的需求在本地化系统里太普遍了！结合你的限制（不能显式指定列、优先保证正确性），我推荐用窗口函数+优先级排序的方案，逻辑清晰且性能不错，完全符合你的规则。

核心思路 #

我们需要给每个id的本地化条目按优先级打分：

• 1分：精确匹配目标语言+区域（最高优先级）
• 2分：仅语言匹配，区域不同
• 3分：系统默认区域（最低优先级）
  然后给每个id分组，取分数最低（优先级最高）的那一行即可。

MySQL 8.0+ 方案（推荐） #

利用ROW_NUMBER()窗口函数实现，代码简洁高效：

-- 设置目标区域和默认区域的变量，替换成你需要的locale
SET @target_lang = 'de';
SET @target_region = 'DE';
SET @default_lang = 'en';
SET @default_region = 'GB';

SELECT fl.*
FROM (
    SELECT 
        *,
        -- 按优先级排序，给每一行分配行号
        ROW_NUMBER() OVER (
            PARTITION BY id 
            ORDER BY 
                CASE
                    -- 优先级1：精确匹配语言+区域
                    WHEN language = @target_lang AND region = @target_region THEN 1
                    -- 优先级2：仅语言匹配
                    WHEN language = @target_lang THEN 2
                    -- 优先级3：系统默认
                    WHEN language = @default_lang AND region = @default_region THEN 3
                    -- 题目假设默认行必存在，所以这个分支不会触发
                    ELSE 4
                END ASC
        ) AS rn
    FROM foo_localised
) AS fl
WHERE rn = 1;

测试验证 #

• en_GB：所有精确匹配的行被选中，符合预期；
• en_US：id=1取en_US（精确匹配），id=2/3取en_GB（语言匹配+默认）；
• es_ES：id=1/2取精确匹配的es_ES，id=3取默认en_GB；
• de_DE：id=3取精确匹配的de_DE，id=1/2取默认en_GB；
  完全符合你给出的预期结果。

MySQL 5.x 兼容方案 #

如果你的环境是MySQL 5.x（不支持窗口函数），可以用关联子查询实现：

SET @target_lang = 'de';
SET @target_region = 'DE';
SET @default_lang = 'en';
SET @default_region = 'GB';

SELECT fl.*
FROM foo_localised fl
INNER JOIN (
    SELECT 
        id,
        -- 确定每个id要选的语言
        CASE
            WHEN EXISTS (SELECT 1 FROM foo_localised WHERE id = f.id AND language = @target_lang AND region = @target_region) THEN @target_lang
            WHEN EXISTS (SELECT 1 FROM foo_localised WHERE id = f.id AND language = @target_lang) THEN @target_lang
            ELSE @default_lang
        END AS selected_lang,
        -- 确定每个id要选的区域
        CASE
            WHEN EXISTS (SELECT 1 FROM foo_localised WHERE id = f.id AND language = @target_lang AND region = @target_region) THEN @target_region
            WHEN EXISTS (SELECT 1 FROM foo_localised WHERE id = f.id AND language = @target_lang) THEN (SELECT region FROM foo_localised WHERE id = f.id AND language = @target_lang LIMIT 1)
            ELSE @default_region
        END AS selected_region
    FROM foo f
) AS sel ON fl.id = sel.id AND fl.language = sel.selected_lang AND fl.region = sel.selected_region;

这个方案通过子查询先确定每个id应该匹配的语言和区域，再关联取出对应的行，同样满足不能显式指定列的要求。

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