数据库多语言字段设计

核心问题：数据库里的商品名称、描述等内容字段，应该用哪种方式存储多语言版本？各方案的性能和维护成本如何？

真实场景

你的电商平台需要让 products 表的 name 和 description 字段支持中、英、日三种语言。商品基础数据（价格、库存、SKU）只有一份，但文字内容需要多语言版本。

四种设计方案对比

方案一：独立列（简单场景）

-- 适合语言数固定且少的场景
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
sku VARCHAR(100) NOT NULL UNIQUE,
price DECIMAL(10, 2) NOT NULL,
-- 多语言内容列
name_zh_cn VARCHAR(255) NOT NULL,
name_en_us VARCHAR(255),
name_ja_jp VARCHAR(255),
description_zh_cn TEXT NOT NULL,
description_en_us TEXT,
description_ja_jp TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);

// Prisma Schema
model Product {
id             String   @id @default(uuid())
sku            String   @unique
price          Decimal
nameZhCn       String
nameEnUs       String?
nameJaJp       String?
descriptionZhCn String
descriptionEnUs String?
descriptionJaJp String?
createdAt      DateTime @default(now())
}

// 查询
async function getProduct(id: string, locale: string) {
const product = await prisma.product.findUnique({ where: { id } });
if (!product) return null;
// 列名映射
const localeToField: Record<string, keyof typeof product> = {
'zh-CN': 'nameZhCn',
'en-US': 'nameEnUs',
'ja-JP': 'nameJaJp',
};
const nameField = localeToField[locale] ?? 'nameZhCn';
return {
id: product.id,
name: (product[nameField] as string) ?? product.nameZhCn, // fallback 到默认语言
price: product.price,
};
}

优点：查询简单，性能最好，索引直接加在列上。

缺点：每增加一种语言需要 ALTER TABLE，列数爆炸（5 个翻译字段 × 10 种语言 = 50 列）。

方案二：JSON 列（PostgreSQL JSONB）

-- PostgreSQL JSONB 列，支持索引查询
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
sku VARCHAR(100) NOT NULL UNIQUE,
price DECIMAL(10, 2) NOT NULL,
-- JSONB 存储所有翻译
translations JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 示例数据
INSERT INTO products (sku, price, translations) VALUES (
'SKU-001',
299.00,
'{
"zh-CN": {"name": "苹果手机", "description": "最新款苹果手机"},
"en-US": {"name": "iPhone", "description": "Latest iPhone model"},
"ja-JP": {"name": "アイフォン", "description": "最新のiPhone"}
}'
);
-- 按 locale 查询
SELECT
id,
sku,
price,
translations->>'zh-CN'->>'name' AS name,
translations->>'zh-CN'->>'description' AS description
FROM products
WHERE id = $1;
-- 动态 locale（参数化）
SELECT
id, sku, price,
translations->$2->>'name' AS name
FROM products
WHERE id = $1;
-- 为 JSONB 字段创建 GIN 索引（全文搜索用）
CREATE INDEX idx_products_translations ON products USING GIN (translations);
-- 按名称搜索（全语言）
SELECT * FROM products
WHERE translations @> '{"zh-CN": {"name": "苹果"}}';

// Prisma 中使用 Json 类型
model Product {
id           String   @id @default(uuid())
sku          String   @unique
price        Decimal
translations Json     @default("{}")
createdAt    DateTime @default(now())
}

// TypeScript 类型定义
interface ProductTranslation {
name: string;
description: string;
shortDescription?: string;
}
type ProductTranslations = Partial<Record<string, ProductTranslation>>;
async function getProduct(id: string, locale: string) {
const product = await prisma.product.findUnique({ where: { id } });
if (!product) return null;
const translations = product.translations as ProductTranslations;
const translation = translations[locale] ?? translations['zh-CN'];
return {
id: product.id,
name: translation?.name ?? '',
description: translation?.description ?? '',
price: product.price,
};
}
// 更新翻译
async function updateTranslation(
productId: string,
locale: string,
data: ProductTranslation
) {
await prisma.$executeRaw`
UPDATE products
SET translations = jsonb_set(
translations,
${`{${locale}}`}::text[],
${JSON.stringify(data)}::jsonb
)
WHERE id = ${productId}
`;
}

方案三：翻译关联表（大型系统推荐）

-- 主表：只含不需要翻译的字段
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
sku VARCHAR(100) NOT NULL UNIQUE,
price DECIMAL(10, 2) NOT NULL,
stock INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 翻译表：存储多语言内容
CREATE TABLE product_translations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
locale VARCHAR(10) NOT NULL,  -- 'zh-CN', 'en-US', 'ja-JP'
name VARCHAR(255) NOT NULL,
description TEXT,
short_description VARCHAR(500),
meta_title VARCHAR(70),     -- SEO
meta_description VARCHAR(160), -- SEO
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
-- 每个商品每种语言只有一条记录
UNIQUE(product_id, locale)
);
-- 索引
CREATE INDEX idx_product_translations_product_locale
ON product_translations(product_id, locale);
CREATE INDEX idx_product_translations_locale
ON product_translations(locale);
-- 全文搜索索引（中文需要额外扩展如 pg_jieba）
CREATE INDEX idx_product_name_search
ON product_translations USING GIN(to_tsvector('simple', name));

-- 查询单个商品的翻译
SELECT
p.id, p.sku, p.price, p.stock,
COALESCE(t.name, def.name) AS name,
COALESCE(t.description, def.description) AS description
FROM products p
-- 目标语言翻译
LEFT JOIN product_translations t
ON p.id = t.product_id AND t.locale = $2
-- Fallback：默认语言翻译
LEFT JOIN product_translations def
ON p.id = def.product_id AND def.locale = 'zh-CN'
WHERE p.id = $1;

// Prisma Schema
model Product {
id           String               @id @default(uuid())
sku          String               @unique
price        Decimal
stock        Int                  @default(0)
translations ProductTranslation[]
createdAt    DateTime             @default(now())
}
model ProductTranslation {
id               String   @id @default(uuid())
productId        String
locale           String
name             String
description      String?
shortDescription String?
metaTitle        String?
metaDescription  String?
product          Product  @relation(fields: [productId], references: [id], onDelete: Cascade)
createdAt        DateTime @default(now())
updatedAt        DateTime @updatedAt
@@unique([productId, locale])
@@index([productId, locale])
}

// 查询
async function getProduct(id: string, locale: string) {
const product = await prisma.product.findUnique({
where: { id },
include: {
translations: {
where: {
locale: { in: [locale, 'zh-CN'] }, // 目标语言 + fallback
},
},
},
});
if (!product) return null;
// 优先使用目标语言，fallback 到 zh-CN
const translation =
product.translations.find(t => t.locale === locale) ??
product.translations.find(t => t.locale === 'zh-CN');
return {
id: product.id,
name: translation?.name ?? '',
description: translation?.description ?? '',
price: product.price,
};
}
// 批量查询（商品列表）
async function getProducts(locale: string, page: number, pageSize: number) {
const products = await prisma.product.findMany({
skip: (page - 1) * pageSize,
take: pageSize,
include: {
translations: {
where: { locale: { in: [locale, 'zh-CN'] } },
},
},
orderBy: { createdAt: 'desc' },
});
return products.map(p => {
const t = p.translations.find(t => t.locale === locale)
?? p.translations.find(t => t.locale === 'zh-CN');
return { ...p, name: t?.name ?? '', description: t?.description ?? '' };
});
}

方案四：混合方案（生产推荐）

主表保留默认语言字段（性能优先），关联表存储其他语言翻译：

CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
sku VARCHAR(100) NOT NULL UNIQUE,
price DECIMAL(10, 2) NOT NULL,
-- 默认语言（主语言）直接存在主表，避免 JOIN
name VARCHAR(255) NOT NULL,       -- 默认：zh-CN
description TEXT,                  -- 默认：zh-CN
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 只存储非默认语言的翻译
CREATE TABLE product_translations (
product_id UUID REFERENCES products(id) ON DELETE CASCADE,
locale VARCHAR(10) NOT NULL CHECK (locale != 'zh-CN'), -- 排除默认语言
name VARCHAR(255) NOT NULL,
description TEXT,
PRIMARY KEY (product_id, locale)
);

async function getProduct(id: string, locale: string) {
if (locale === 'zh-CN') {
// 默认语言：直接从主表查，无需 JOIN
return prisma.product.findUnique({ where: { id } });
}
// 非默认语言：JOIN 翻译表
const product = await prisma.product.findUnique({
where: { id },
include: {
translations: {
where: { locale },
},
},
});
if (!product) return null;
const translation = product.translations[0];
return {
...product,
name: translation?.name ?? product.name,       // fallback 到默认语言
description: translation?.description ?? product.description,
};
}

翻译完整度追踪

-- 查看各语言的翻译完成率
SELECT
locale,
COUNT(*) AS translated_count,
(SELECT COUNT(*) FROM products) AS total_products,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM products), 1) AS completion_rate
FROM product_translations
GROUP BY locale
ORDER BY locale;
-- 找出缺少某语言翻译的商品
SELECT p.id, p.sku, p.name
FROM products p
LEFT JOIN product_translations t
ON p.id = t.product_id AND t.locale = 'en-US'
WHERE t.product_id IS NULL;

常见问题

Q：搜索时如何同时搜索所有语言？

-- PostgreSQL 全文搜索跨语言
SELECT p.*, t.name, t.locale
FROM products p
JOIN product_translations t ON p.id = t.product_id
WHERE t.name ILIKE '%apple%'  -- 简单 LIKE
OR t.name ILIKE '%苹果%'
ORDER BY p.created_at DESC;

Q：MySQL 支持 JSON 列方案吗？

A：MySQL 5.7+ 支持 JSON 类型，但性能和功能不如 PostgreSQL JSONB（无 GIN 索引）。MySQL 推荐使用翻译关联表方案。

Q：如何处理翻译不完整的情况？

A：查询时始终 COALESCE 到默认语言，并在管理后台显示"未翻译"标记。可以建立翻译任务队列，自动通知翻译团队补充缺失翻译。

下一节：数据库里的内容字段多语言设计好了，API 返回的错误信息和发送的邮件也需要本地化。

→ 错误信息与邮件模板多语言