如何在数据库视图中最优使用多语言功能？

1. 用带参数的表值函数替代视图 #

视图本身不支持参数，但可以用表值函数模拟带参数的视图体验，直接根据传入的@LanguageId过滤翻译数据，彻底避免冗余行：

CREATE FUNCTION dbo.GetBaseDataByLanguage(@LanguageId INT)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        a.ID, 
        b.Text AS TypeText, 
        b.TranslateId AS TypeTranslateId,
        c.Text AS CategoryText, 
        c.TranslateId AS CategoryTranslateId,
        d.Text AS SourceText,
        d.TranslateId AS SourceTranslateId
    FROM base a
    LEFT JOIN enumB b ON a.TypeId = b.Id AND b.LanguageId = @LanguageId
    LEFT JOIN enumC c ON a.CategoryId = c.Id AND c.LanguageId = @LanguageId
    LEFT JOIN enumD d ON a.SourceId = d.Id AND d.LanguageId = @LanguageId
)

使用时直接传入语言ID调用：

SELECT * FROM dbo.GetBaseDataByLanguage(1)

这种方式既保留了类似视图的便捷性，又能精准返回目标语言的数据，同时可以直接在结果上执行LIKE模糊查询。

2. 固定语言场景下用多列存储视图 #

如果系统支持的语言数量固定且不多，可以把每种语言的翻译值作为独立列存入视图，避免行数膨胀：

CREATE VIEW vw_BaseMultiLang
AS
SELECT 
    a.ID,
    -- 英文翻译列
    (SELECT Text FROM enumB WHERE Id = a.TypeId AND LanguageId = 1) AS TypeText_En,
    (SELECT Text FROM enumC WHERE Id = a.CategoryId AND LanguageId = 1) AS CategoryText_En,
    (SELECT Text FROM enumD WHERE Id = a.SourceId AND LanguageId = 1) AS SourceText_En,
    -- 中文翻译列
    (SELECT Text FROM enumB WHERE Id = a.TypeId AND LanguageId = 2) AS TypeText_Cn,
    (SELECT Text FROM enumC WHERE Id = a.CategoryId AND LanguageId = 2) AS CategoryText_Cn,
    (SELECT Text FROM enumD WHERE Id = a.SourceId AND LanguageId = 2) AS SourceText_Cn,
    -- 保留翻译ID
    b.TranslateId AS TypeTranslateId,
    c.TranslateId AS CategoryTranslateId,
    d.TranslateId AS SourceTranslateId
FROM base a
LEFT JOIN enumB b ON a.TypeId = b.Id
LEFT JOIN enumC c ON a.CategoryId = c.Id
LEFT JOIN enumD d ON a.SourceId = d.Id

应用层查询时直接针对对应语言列做LIKE操作，比如：

SELECT * FROM vw_BaseMultiLang WHERE TypeText_Cn LIKE '%测试%'

3. 保留全语言视图（应急方案） #

如果必须使用普通视图，可以关联所有语言数据并添加LanguageId列，应用层查询时先过滤语言再做模糊匹配：

CREATE VIEW vw_BaseAllLang
AS
SELECT 
    a.ID,
    b.LanguageId,
    b.Text AS TypeText, 
    b.TranslateId AS TypeTranslateId,
    c.Text AS CategoryText, 
    c.TranslateId AS CategoryTranslateId,
    d.Text AS SourceText,
    d.TranslateId AS SourceTranslateId
FROM base a
LEFT JOIN enumB b ON a.TypeId = b.Id
LEFT JOIN enumC c ON a.CategoryId = c.Id
LEFT JOIN enumD d ON a.SourceId = d.Id

查询示例：

SELECT * FROM vw_BaseAllLang 
WHERE LanguageId = 2 
AND TypeText LIKE '%测试%'

这种方式会产生较多冗余数据，仅适合语言数量极少的场景，否则会影响查询性能。

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