多言語対応のDB設計パターン比較｜翻訳テーブルのDDLと選び方

多言語対応の設計とは、「どの文言をどこに持つか」を先に決めることです。画面の固定文言はリソースファイルに、利用者や運用者が登録するデータは翻訳テーブルに持つ、と線を引くと、残りの判断がほぼ決まります。

この記事では、DB側の持ち方を4パターンに分け、DDL・未翻訳時のフォールバックSQL・制約の付け方を、同じ題材（社内の備品予約システムの備品マスタ）で比べます。SQLはすべてPostgreSQLとMySQLで流し、出力をそのまま載せています。特定のフレームワークには依存しない内容です。

確認環境：Ubuntu 24.04、PostgreSQL 16.15、MySQL 8.0.46、Python 3.11.15（確認日 2026-10-06）。出力例のデータベース名は appdb に置き換えています。

この記事の結論

• 画面の固定文言（ボタン・ラベル・エラーメッセージ）はリソースファイル、利用者や運用者が登録するデータ（備品名・商品名・お知らせ本文）はDBに持つ。
• DBの持ち方は、迷ったらエンティティ別翻訳テーブル（本体テーブル＋「本体ID×言語」の翻訳テーブル）を選ぶ。言語の追加が行の追加だけで済み、外部キーと一意制約が素直に付く。
• 未翻訳の行は、翻訳テーブルを2回LEFT JOINしてCOALESCEで既定言語に落とす。INNER JOINだけにすると、未翻訳のデータが一覧から消える。
• 言語コードはBCP 47の言語タグ（ja、en-US、zh-Hant など）を文字列で持つ。大文字・小文字の表記をCHECK制約でそろえる。
• 並べ替えと重複判定は照合順序で結果が変わる。MySQL 8.0の既定（utf8mb4_0900_ai_ci）では「はは」と「ぱぱ」が同じ値として扱われる。

多言語データの持ち方 早見表

国際化（i18n）と地域化（l10n）の違い

W3Cの解説では、国際化（internationalization、i18n）は「地域化しやすいように製品を設計・開発すること」、地域化（localization、l10n）は「特定の地域・言語の要求に合わせて製品や内容を適合させること」と説明されています。i18n は i と n の間に18文字、l10n は l と n の間に10文字あることから来た略語です。

この記事で扱うテーブル設計は、国際化の作業です。ここを先に整えておくと、地域化は「行を足す」「ファイルを足す」だけの作業になります。

リソースファイルと翻訳テーブルの線引き

判断の軸は「誰が、いつ、その文言を変えるか」です。

リソースファイルはソースコードと一緒にバージョン管理され、レビューを通して変わります。翻訳テーブルは稼働中に画面から変わります。リリースなしで変えたい文言かどうかで分けると判断がぶれません。

リソースファイル側の最小例（Python）

リソースファイルでも、未翻訳のキーを既定言語に落とす考え方は同じです。言語タグを後ろから削りながら候補を作り、最後に既定言語を足します。

{
  "button.reserve": "予約する",
  "label.stock": "在庫 {count} 点",
  "error.required": "{field}を入力してください。"
}

上が messages.ja.json、下が messages.en.json です（英語版は error.required をまだ訳していない状態）。

{
  "button.reserve": "Reserve",
  "label.stock": "{count} in stock"
}

# messages.py（同じフォルダに messages.ja.json / messages.en.json を置く）
import json
from pathlib import Path

DEFAULT_LANG = "ja"


def load(lang):
    path = Path(__file__).with_name(f"messages.{lang}.json")
    return json.loads(path.read_text(encoding="utf-8")) if path.exists() else {}


def candidates(lang):
    """en-US → en → ja の順に候補を作る"""
    parts = lang.split("-")
    result = ["-".join(parts[:i]) for i in range(len(parts), 0, -1)]
    if DEFAULT_LANG not in result:
        result.append(DEFAULT_LANG)
    return result


def message(key, lang, **params):
    for cand in candidates(lang):
        text = load(cand).get(key)
        if text is not None:
            return text.format(**params), cand
    return key, None  # どの言語にも無いときはキーをそのまま返す


print(candidates("en-US"))
print(message("button.reserve", "en-US"))
print(message("label.stock", "en-US", count=3))
print(message("error.required", "en-US", field="備品名"))
print(message("error.unknown", "en-US"))

実行結果：

['en-US', 'en', 'ja']
('Reserve', 'en')
('3 in stock', 'en')
('備品名を入力してください。', 'ja')
('error.unknown', None)

文言に値を埋め込むときは、文を連結せず「{count} in stock」のように差し込み位置を文言側に持たせます。 語順は言語によって変わるためです。

言語コードの持ち方（BCP 47）

言語コードには、BCP 47（RFC 5646）の言語タグを使います。サブタグをハイフンでつなぐ形です。

RFC 5646 で確認した、設計に効く点は次の3つです。

• 言語タグは大文字・小文字を区別しないと定められている。一方で表記の慣習として、言語は小文字（en）、文字体系は先頭だけ大文字（Hant）、地域は大文字（US）で書く。
• 各サブタグは最大8文字。
• 格納領域に上限を設ける場合の目安として、4.4.1 節に35文字が挙げられている。

そこで、言語コードの列は varchar(35) とし、表記を慣習の形にそろえるCHECK制約を付けます。区別しない定めの値を、区別するDBにそのまま入れると「en-US」と「en-us」が別の行になるためです。

-- PostgreSQL：言語マスタ
CREATE TABLE language (
  lang       varchar(35) PRIMARY KEY
             CHECK (lang ~ '^[a-z]{2,3}(-[A-Z][a-z]{3})?(-[A-Z]{2})?$'),
  name       varchar(50) NOT NULL,
  fallback   varchar(35) REFERENCES language(lang),  -- 未翻訳時に次に見る言語
  is_default boolean NOT NULL DEFAULT false
);
-- 既定言語は1行だけ
CREATE UNIQUE INDEX language_one_default ON language (is_default) WHERE is_default;

INSERT INTO language VALUES
 ('ja','日本語',NULL,true),
 ('en','English','ja',false),
 ('en-US','English (US)','en',false),
 ('zh-Hant','繁體中文','ja',false);

このCHECK制約は「言語」「言語-文字体系」「言語-地域」「言語-文字体系-地域」の形だけを通す簡易版です（地域を3桁の数字で表すタグや変種サブタグは対象にしていません）。扱う言語が決まっているシステムでは、言語マスタへの外部キーが実質的な入力チェックになります。

4パターンのDDL

題材は備品マスタ（備品名を多言語で持つ）です。まずPostgreSQLのDDLを示します。

A. 列追加

CREATE TABLE item_a (
  item_id bigint PRIMARY KEY,
  name_ja varchar(100) NOT NULL,
  name_en varchar(100)
);
INSERT INTO item_a VALUES
 (1,'プロジェクター','Projector'),
 (2,'延長コード',NULL),
 (3,'会議用スピーカー','Conference speaker');

B. エンティティ別翻訳テーブル

言語に依存しない列（コード、在庫数）は本体に、言語で変わる列（名前、説明）は翻訳テーブルに分けます。

CREATE TABLE item (
  item_id bigint PRIMARY KEY,
  code    varchar(20) NOT NULL UNIQUE,
  stock   integer NOT NULL
);
CREATE TABLE item_translation (
  item_id     bigint       NOT NULL REFERENCES item(item_id) ON DELETE CASCADE,
  lang        varchar(35)  NOT NULL REFERENCES language(lang),
  name        varchar(100) NOT NULL,
  description text,
  PRIMARY KEY (item_id, lang)
);
INSERT INTO item VALUES (1,'PJ-001',2),(2,'EC-010',8),(3,'SP-003',1);
INSERT INTO item_translation VALUES
 (1,'ja','プロジェクター','会議室用。HDMI接続。'),
 (1,'en','Projector','For meeting rooms. HDMI.'),
 (2,'ja','延長コード','5m、3口。'),
 (3,'ja','会議用スピーカー','USB接続。'),
 (3,'en','Conference speaker',NULL),
 (3,'en-US','Conference speakerphone',NULL);

備品2は英語が未登録、備品3は英語の説明文だけ未登録、という状態にしてあります。

C. 汎用翻訳テーブル

CREATE TABLE translation (
  table_name  varchar(63) NOT NULL,
  record_id   bigint      NOT NULL,
  column_name varchar(63) NOT NULL,
  lang        varchar(35) NOT NULL REFERENCES language(lang),
  value       text        NOT NULL,
  PRIMARY KEY (table_name, record_id, column_name, lang)
);
INSERT INTO translation
SELECT 'item', item_id, 'name', lang, name FROM item_translation;

D. JSON列

CREATE TABLE item_d (
  item_id bigint PRIMARY KEY,
  name    jsonb NOT NULL CHECK (name ? 'ja')   -- 既定言語のキーを必須にする
);
INSERT INTO item_d VALUES
 (1,'{"ja":"プロジェクター","en":"Projector"}'),
 (2,'{"ja":"延長コード"}'),
 (3,'{"ja":"会議用スピーカー","en":"Conference speaker"}');

4パターンの比較表

未翻訳時に既定言語へ落とすSQL（COALESCE）

B. エンティティ別翻訳テーブル：要求言語→既定言語

翻訳テーブルを「要求された言語」と「既定言語」で1回ずつLEFT JOINし、COALESCEで先に見つかったほうを採ります。 次のSQLはPostgreSQLとMySQLで同じ文のまま動きます。

SELECT i.item_id, i.code,
       COALESCE(t.name, d.name) AS name,
       COALESCE(t.lang, d.lang) AS shown_lang
FROM item i
LEFT JOIN item_translation t ON t.item_id = i.item_id AND t.lang = 'en'
LEFT JOIN item_translation d ON d.item_id = i.item_id AND d.lang = 'ja'
ORDER BY i.item_id;

PostgreSQL 16.15 の実行結果：

 item_id |  code  |        name        | shown_lang 
---------+--------+--------------------+------------
       1 | PJ-001 | Projector          | en
       2 | EC-010 | 延長コード         | ja
       3 | SP-003 | Conference speaker | en
(3 rows)

MySQL 8.0.46 の実行結果：

+---------+--------+--------------------+------------+
| item_id | code   | name               | shown_lang |
+---------+--------+--------------------+------------+
|       1 | PJ-001 | Projector          | en         |
|       2 | EC-010 | 延長コード         | ja         |
|       3 | SP-003 | Conference speaker | en         |
+---------+--------+--------------------+------------+

英語が未登録の備品2だけ、日本語に落ちています。shown_lang を一緒に返しておくと、画面側で「この行は日本語で表示している」と分かり、HTMLの lang 属性の出し分けや翻訳待ちの印に使えます。

INNER JOIN だけにした場合

SELECT i.item_id, t.name
FROM item i
JOIN item_translation t ON t.item_id = i.item_id AND t.lang = 'en'
ORDER BY 1;

 item_id |        name        
---------+--------------------
       1 | Projector
       3 | Conference speaker
(2 rows)

未翻訳の備品2が一覧から消えます。 英語画面で件数が合わないときは、まずここを確かめます。

列ごとに落とす

行はあっても一部の列だけ未翻訳、という状態もあります。列ごとにCOALESCEを書くと、列単位で既定言語に落ちます。

SELECT i.item_id,
       COALESCE(t.name, d.name) AS name,
       COALESCE(t.description, d.description) AS description
FROM item i
LEFT JOIN item_translation t ON t.item_id = i.item_id AND t.lang = 'en'
LEFT JOIN item_translation d ON d.item_id = i.item_id AND d.lang = 'ja'
ORDER BY i.item_id;

 item_id |        name        |       description        
---------+--------------------+--------------------------
       1 | Projector          | For meeting rooms. HDMI.
       2 | 延長コード         | 5m、3口。
       3 | Conference speaker | USB接続。
(3 rows)

備品3は、名前は英語、説明文は日本語になりました。1行の中で言語が混ざる表示を許すかどうかは、画面の仕様として決めておきます（混ぜたくない場合は、前のSQLのように行単位で扱い、説明文は NULL のまま返します）。

3段階で落とす（en-US → en → ja）

言語マスタの fallback 列をたどると、地域つきのタグから順に落とせます。

SELECT i.item_id,
       COALESCE(t1.name, t2.name, t3.name) AS name,
       COALESCE(t1.lang, t2.lang, t3.lang) AS shown_lang
FROM item i
JOIN language l1 ON l1.lang = 'en-US'
LEFT JOIN language l2 ON l2.lang = l1.fallback
LEFT JOIN language l3 ON l3.lang = l2.fallback
LEFT JOIN item_translation t1 ON t1.item_id = i.item_id AND t1.lang = l1.lang
LEFT JOIN item_translation t2 ON t2.item_id = i.item_id AND t2.lang = l2.lang
LEFT JOIN item_translation t3 ON t3.item_id = i.item_id AND t3.lang = l3.lang
ORDER BY i.item_id;

PostgreSQL・MySQLとも同じ結果でした（表示はPostgreSQL）。

 item_id |          name           | shown_lang 
---------+-------------------------+------------
       1 | Projector               | en
       2 | 延長コード              | ja
       3 | Conference speakerphone | en-US
(3 rows)

A・C・D のフォールバック

-- A. 列追加
SELECT item_id, COALESCE(name_en, name_ja) AS name FROM item_a ORDER BY item_id;

-- C. 汎用翻訳テーブル
SELECT i.item_id, COALESCE(t.value, d.value) AS name
FROM item i
LEFT JOIN translation t ON t.table_name='item' AND t.record_id=i.item_id
                       AND t.column_name='name' AND t.lang='en'
LEFT JOIN translation d ON d.table_name='item' AND d.record_id=i.item_id
                       AND d.column_name='name' AND d.lang='ja'
ORDER BY i.item_id;

-- D. JSON列（PostgreSQL）
SELECT item_id, COALESCE(name->>'en', name->>'ja') AS name FROM item_d ORDER BY item_id;

3つとも結果は同じです。

 item_id |        name        
---------+--------------------
       1 | Projector
       2 | 延長コード
       3 | Conference speaker
(3 rows)

MySQLでJSON列を使うときは、パスの書き方と演算子が変わります。

-- MySQL 8.0
CREATE TABLE item_d (
  item_id bigint PRIMARY KEY,
  name    json NOT NULL,
  CONSTRAINT item_d_name_check CHECK (JSON_CONTAINS_PATH(name, 'one', '$.ja'))
);
SELECT item_id, COALESCE(name->>'$.en', name->>'$.ja') AS name FROM item_d ORDER BY item_id;

+---------+--------------------+
| item_id | name               |
+---------+--------------------+
|       1 | Projector          |
|       2 | 延長コード         |
|       3 | Conference speaker |
+---------+--------------------+

->> の代わりに JSON_EXTRACT を使うと、値が引用符つきで返りました。画面に出す値は ->> で取り出します。

+---------+----------------------+
| item_id | name_quoted          |
+---------+----------------------+
|       1 | "Projector"          |
|       2 | "延長コード"         |
|       3 | "Conference speaker" |
+---------+----------------------+

一意制約・外部キー・CHECK制約の付け方

エンティティ別翻訳テーブルで付ける制約と、実際に止まったときのエラー文です。

PostgreSQL 16.15 で、順に4つのINSERTを流した結果：

INSERT INTO item_translation VALUES (1,'en','Beamer',NULL);
INSERT INTO item_translation VALUES (1,'fr','Projecteur',NULL);
INSERT INTO language VALUES ('EN-us','x',NULL,false);
INSERT INTO language VALUES ('ko','한국어','ja',true);

ERROR:  duplicate key value violates unique constraint "item_translation_pkey"
DETAIL:  Key (item_id, lang)=(1, en) already exists.
ERROR:  insert or update on table "item_translation" violates foreign key constraint "item_translation_lang_fkey"
DETAIL:  Key (lang)=(fr) is not present in table "language".
ERROR:  new row for relation "language" violates check constraint "language_lang_check"
DETAIL:  Failing row contains (EN-us, x, null, f).
ERROR:  duplicate key value violates unique constraint "language_one_default"
DETAIL:  Key (is_default)=(t) already exists.

MySQLには部分一意インデックスが無いため、「既定言語は1つ」は生成列で表しました。is_default が真のときだけ 1、それ以外は NULL になる列に UNIQUE を付けます（NULL は重複扱いにならないため、真の行だけが1つに制限されます）。

-- MySQL 8.0：言語マスタ
CREATE TABLE language (
  lang       varchar(35) CHARACTER SET ascii COLLATE ascii_bin PRIMARY KEY,
  name       varchar(50) NOT NULL,
  fallback   varchar(35) CHARACTER SET ascii COLLATE ascii_bin,
  is_default boolean NOT NULL DEFAULT false,
  default_flag tinyint AS (IF(is_default, 1, NULL)) STORED UNIQUE,
  CONSTRAINT language_lang_check
    CHECK (REGEXP_LIKE(lang, '^[a-z]{2,3}(-[A-Z][a-z]{3})?(-[A-Z]{2})?$', 'c')),
  FOREIGN KEY (fallback) REFERENCES language(lang)
);
CREATE TABLE item_translation (
  item_id     bigint NOT NULL,
  lang        varchar(35) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  name        varchar(100) NOT NULL,
  description text,
  PRIMARY KEY (item_id, lang),
  FOREIGN KEY (item_id) REFERENCES item(item_id) ON DELETE CASCADE,
  FOREIGN KEY (lang) REFERENCES language(lang)
);

同じ4つのINSERTに対するMySQL 8.0.46 のエラー文（エラー文の部分を抜粋）：

ERROR 1062 (23000): Duplicate entry '1-en' for key 'item_translation.PRIMARY'
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`appdb`.`item_translation`, CONSTRAINT `item_translation_ibfk_2` FOREIGN KEY (`lang`) REFERENCES `language` (`lang`))
ERROR 3819 (HY000): Check constraint 'language_lang_check' is violated.
ERROR 1062 (23000): Duplicate entry '1' for key 'language.default_flag'

MySQLの lang 列に CHARACTER SET ascii COLLATE ascii_bin を付けているのは、次の節で見るとおり、既定の照合順序では大文字・小文字を区別しないためです。REGEXP_LIKE の第3引数 'c' も、大文字・小文字を区別して照合する指定です。

汎用翻訳テーブルで起きること

汎用翻訳テーブルは record_id がどのテーブルの行を指すかをDBが知らないため、本体に無いIDの翻訳も登録できます。

INSERT INTO translation VALUES ('item', 999, 'name', 'ja', '存在しない備品');
SELECT count(*) AS orphan FROM translation t
WHERE t.table_name='item'
  AND NOT EXISTS (SELECT 1 FROM item i WHERE i.item_id=t.record_id);

INSERT 0 1
 orphan 
--------
      1
(1 row)

このパターンを選ぶ場合は、本体の削除時に翻訳も消す処理と、上のような点検SQLを定期的に流す運用をセットにします。

翻訳漏れを一覧にするSQL

言語マスタとの組み合わせを作り、翻訳テーブルに無いものを出します。

SELECT i.item_id, l.lang
FROM item i CROSS JOIN language l
LEFT JOIN item_translation t ON t.item_id = i.item_id AND t.lang = l.lang
WHERE t.item_id IS NULL
ORDER BY i.item_id, l.lang;

 item_id |  lang   
---------+---------
       1 | en-US
       1 | zh-Hant
       2 | en
       2 | en-US
       2 | zh-Hant
       3 | zh-Hant
(6 rows)

en-US のように「上位の言語（en）があれば足りる」言語は、言語マスタに「翻訳必須」の列を足して絞り込むと、作業一覧として使いやすくなります。

照合順序で並べ替えと重複判定が変わる

照合順序（collation）は、文字列の大小と等しさを決める規則です。多言語のデータでは、並べ替えの結果と一意制約が同じ値とみなす範囲の両方に効きます。

8つの語（ハサミ、はさみ、鋏、ばね、はり、パネル、Zebra、apple）を並べ替えた結果です。

文字コード順（C.UTF-8、utf8mb4_bin）では、大文字が小文字より前、ひらがながカタカナより前にまとまります。辞書に近い順で並べたい列は、PostgreSQLなら次のように照合順序を指定します。

-- PostgreSQL：並べ替えのときだけ指定する
SELECT name FROM item_translation WHERE lang = 'ja' ORDER BY name COLLATE "ja-x-icu";

等しさの判定も確かめました。

SELECT 'はは' = 'ぱぱ' AS haha_papa, 'はは' = 'ハハ' AS hira_kata, 'en-US' = 'en-us' AS tag_case;

MySQLの既定の照合順序のまま「言語×名前」に一意制約を付けると、次のようになります。

CREATE TABLE tag_default (
  lang varchar(35) NOT NULL, name varchar(50) NOT NULL, UNIQUE (lang, name));
INSERT INTO tag_default VALUES ('ja','はは');
INSERT INTO tag_default VALUES ('ja','ぱぱ');

ERROR 1062 (23000): Duplicate entry 'ja-ぱぱ' for key 'tag_default.lang'

日本語の名前を別の値として扱いたい列は、列に照合順序を指定します。

CREATE TABLE tag_ja (
  lang varchar(35) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  name varchar(50) COLLATE utf8mb4_ja_0900_as_cs_ks NOT NULL,
  UNIQUE (lang, name));
INSERT INTO tag_ja VALUES ('ja','はは');
INSERT INTO tag_ja VALUES ('ja','ぱぱ');
INSERT INTO tag_ja VALUES ('ja','ハハ');
SELECT * FROM tag_ja ORDER BY name;

+------+--------+
| lang | name   |
+------+--------+
| ja   | はは   |
| ja   | ハハ   |
| ja   | ぱぱ   |
+------+--------+

3行とも登録でき、並び順も「はは、ハハ、ぱぱ」になりました。

パターンの選び方

判断の流れ

1. その文言は開発者がリリース時に変えるものか。 はい → リソースファイル（DBには持たない）。いいえ → 2へ。
2. 翻訳する必要があるか。 利用者が自由に書く文章なら、翻訳せず1列で持つ。翻訳する → 3へ。
3. 言語は2つで固定、かつ翻訳対象の列は1〜2個か。 はい → A. 列追加。いいえ → 4へ。
4. その文言で検索・並べ替え・一意制約を使うか。 はい → B. エンティティ別翻訳テーブル。いいえ → 5へ。
5. 翻訳対象のテーブルが多く、翻訳作業を1か所に集めたいか。 はい → C. 汎用翻訳テーブル。いいえ → 6へ。
6. 表示だけに使う短い文言で、JSON型が使えるDBか。 はい → D. JSON列。いいえ → B。

条件→選ぶもの

フレームワークの多言語機能との切り分け

フロントエンドのフレームワークが持つ多言語機能（翻訳ファイルの読み込み、言語切り替え、複数形や日付の整形）は、主に画面の固定文言を扱います。この記事のDB設計は、その外側にあるデータの翻訳を扱います。

3つの層で言語コードと既定言語をそろえておくと、画面は英語なのにデータは日本語、といったずれの原因を追いやすくなります。

確かめて分かったこと

• INNER JOIN だけの取得では、未翻訳の行が結果から消えた（3件中2件になった）。LEFT JOIN 2回＋COALESCE で3件そろった。
• MySQL 8.0 の既定の照合順序では 'はは' と 'ぱぱ'、'en-US' と 'en-us' が等しいと判定された。 一意制約もこの判定で動き、「ぱぱ」の登録が重複エラーになった。
• utf8mb4_ja_0900_as_cs でも、ひらがなとカタカナ（'はは' と 'ハハ'）は等しいままだった。 区別するには末尾が _ks の照合順序を使う。
• PostgreSQLで照合順序が C.UTF-8 のDBでは、大文字が小文字より前に並んだ（Zebra < apple）。COLLATE "ja-x-icu" を付けると apple < Zebra になった。
• MySQLの JSON_EXTRACT は引用符つきの文字列を返した。 画面に出す値は ->> で取り出す。
• 汎用翻訳テーブルには、本体に無いIDの翻訳が登録できた。 外部キーで守れないぶん、点検SQLが必要になる。

よくある質問

Q. 翻訳テーブルとは何ですか？ A. 本体テーブルの1行に対して、言語ごとの文言を「本体ID×言語コード」の行として持つテーブルです。言語に依存しない列（コード、数量、日時）は本体に残し、言語で変わる列だけを移します。

Q. 多言語対応でリソースファイルとDBの違いは何ですか？ A. 変える人と時期が違います。リソースファイルは開発者がリリース時に変える固定文言、DBは運用者や利用者が稼働中に登録するデータを持ちます。

Q. 英語に切り替えるとデータが表示されないときはどうすればよいですか？ A. 取得SQLが翻訳テーブルとのINNER JOINになっていないかを確かめます。LEFT JOINに変え、既定言語の行もLEFT JOINしてCOALESCEで落とすと、未翻訳の行も表示されます。

Q. 言語コードは ja と ja-JP のどちらで持てばよいですか？ A. 地域で文言を分けないなら ja、分けるなら ja-JP のように地域つきにします。どちらもBCP 47の正しい言語タグなので、システム内で1つの方針に決め、言語マスタに登録した値だけを使います。

Q. 既定言語の文言も翻訳テーブルに入れますか？ A. 入れる形をおすすめします。本体に日本語、翻訳テーブルにそれ以外、と分けると取得SQLが言語によって変わります。すべての言語を翻訳テーブルに入れると、既定言語を後から変える場合も言語マスタの更新だけで済みます。

まとめ

• 画面の固定文言はリソースファイル、利用者や運用者が登録するデータは翻訳テーブルに持つ。
• DBの持ち方は4パターン。言語が増える見込みがある、検索や並べ替えをするなら、エンティティ別翻訳テーブルを選ぶ。
• 未翻訳の行は、LEFT JOIN 2回＋COALESCEで既定言語に落とす。表示した言語（shown_lang）も一緒に返すと画面側で使える。
• 言語コードはBCP 47 の言語タグを varchar(35) で持ち、言語マスタへの外部キーとCHECK制約で表記をそろえる。
• PRIMARY KEY (本体ID, 言語) で重複を防ぎ、既定言語は1つだけになるよう一意制約を付ける。
• 並べ替えと重複判定は照合順序で変わる。MySQLの既定では濁点・半濁点、ひらがな・カタカナ、大文字・小文字を区別しない。

参考・出典

• RFC 5646 Tags for Identifying Languages（BCP 47）: https://www.rfc-editor.org/rfc/rfc5646.txt
• W3C Internationalization「Localization vs. Internationalization」: https://www.w3.org/International/questions/qa-i18n

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。