多种方案实现根据语言参数动态返回对应语言名称

 

CREATE TABLE `main_data`  (
  `id` int(0) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


CREATE TABLE `main_data_i18n`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `data_id` int(0) NULL DEFAULT NULL,
  `locale` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `idx_data_locale`(`data_id`, `locale`) USING BTREE,
  CONSTRAINT `fk_main_data` FOREIGN KEY (`data_id`) REFERENCES `main_data` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

　　

 

要实现根据语言参数动态返回对应语言名称的功能，可以采用以下几种方案：

方案一：使用 CASE WHEN 动态选择语言

通过单个 SQL 查询，根据传入的语言参数返回对应的名称，若不存在则回退到 main_data.name。

 

SELECT 

    md.id,

    COALESCE(

        CASE 

            WHEN '${locale}' = 'en' THEN en.name

            WHEN '${locale}' = 'jp' THEN jp.name

            ELSE NULL

        END,

        md.name

    ) AS name

FROM main_data md

LEFT JOIN main_data_i18n en ON md.id = en.data_id AND en.locale = 'en'

LEFT JOIN main_data_i18n jp ON md.id = jp.data_id AND jp.locale = 'jp';

说明：

1. ${locale} 是外部传入的语言参数（如 en、jp）。
2. 使用 LEFT JOIN 同时关联英文和日文记录，通过 CASE WHEN 动态选择所需语言。
3. COALESCE 确保当指定语言不存在时，返回 main_data.name（中文）。

方案二：创建数据库视图（推荐）

预先创建包含所有语言版本的视图，查询时直接选择对应列。

 

CREATE VIEW main_data_localized AS

SELECT 

    md.id,

    md.name AS name_cn,

    COALESCE(en.name, md.name) AS name_en,

    COALESCE(jp.name, md.name) AS name_jp

FROM main_data md

LEFT JOIN main_data_i18n en ON md.id = en.data_id AND en.locale = 'en'

LEFT JOIN main_data_i18n jp ON md.id = jp.data_id AND jp.locale = 'jp';

查询时使用：

 

SELECT id, 

       CASE 

           WHEN '${locale}' = 'en' THEN name_en

           WHEN '${locale}' = 'jp' THEN name_jp

           ELSE name_cn

       END AS name

FROM main_data_localized;

优势：

1. 简化应用层查询逻辑，无需处理复杂的连接。
2. 新增语言时只需修改视图，无需改动业务代码。

方案三：存储过程（数据库层封装逻辑）

在数据库中创建存储过程，根据参数返回对应语言的名称。

 

DELIMITER $$

CREATE PROCEDURE GetLocalizedName(IN locale VARCHAR(5))

BEGIN

    SET @sql = CONCAT('

        SELECT 

            md.id,

            COALESCE(i18n.name, md.name) AS name

        FROM main_data md

        LEFT JOIN main_data_i18n i18n 

            ON md.id = i18n.data_id AND i18n.locale = ''', locale, '''

    ');

    PREPARE stmt FROM @sql;

    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;

调用方式：

 

CALL GetLocalizedName('en');  -- 返回英文名称

CALL GetLocalizedName('jp');  -- 返回日文名称

CALL GetLocalizedName('cn');  -- 返回中文名称（默认）

方案四：应用层动态拼接 SQL（简单实现）

在应用代码中根据语言参数动态生成查询语句。

Python 示例：

 

def get_localized_data(locale: str):

    query = f"""

    SELECT 

        md.id,

        COALESCE(i18n.name, md.name) AS name

    FROM main_data md

    LEFT JOIN main_data_i18n i18n 

        ON md.id = i18n.data_id AND i18n.locale = %s

    """

    params = (locale,)

    # 执行查询并返回结果

    # result = execute_query(query, params)

    # return result

性能优化建议

1. 索引优化：确保 main_data_i18n 表的 (data_id, locale) 组合索引存在（已在表结构中定义）。
2. 缓存机制：对于高频查询的多语言数据，可以在应用层添加缓存（如 Redis）。
3. 批量查询：避免逐条查询，尽量批量获取数据。

推荐方案

方案二（视图） 和 方案四（应用层动态拼接） 是较为常用的实现方式：

1. 视图：将多语言逻辑封装在数据库层，简化应用查询。
2. 应用层动态拼接：灵活性高，适合快速迭代的项目。

根据实际场景选择合适的方案，若需支持更多语言，只需扩展视图或调整 CASE WHEN 逻辑即可。