MySQL | SQLite経験者向け、30日で習得するMySQL：差分理解 － Day4 文字コードと照合順序

Day4 後半のゴール

「utf8mb4 と collation の“違いが結果にどう出るか”を体で理解する」

前半では、
「utf8mb4 を選ぶ」「collation は比較・ソートのルール」というところまで行きました。

後半のゴールはここです。

utf8mb4 を使うと何が安全になるかを、具体例でイメージできる
collation を変えると、検索結果・ソート結果がどう変わるかを説明できる
「新しいプロジェクトでは、最初にここを決めるべき理由」を自分の言葉で言える

頭の知識を、「あ、だからこうなるのか」という感覚に落としていきます。

utf8mb4 を使う意味を“絵文字”で確認する

「MySQLの utf8 では保存できない文字がある、を実感する」

まずは、utf8 と utf8mb4 の違いが一番分かりやすく出る「絵文字」を題材にします。

ここではイメージとして話しますが、実際に試すときはこういう流れになります。

utf8 のテーブルを作る
utf8mb4 のテーブルを作る
同じ絵文字入り文字列を INSERT してみる

utf8 のテーブル定義例はこんな感じです。

CREATE TABLE messages_utf8 (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  content VARCHAR(255) CHARACTER SET utf8
);

CREATE TABLE messages_utf8 (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  content VARCHAR(255) CHARACTER SET utf8
);

utf8mb4 のテーブルはこうです。

CREATE TABLE messages_utf8mb4 (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  content VARCHAR(255) CHARACTER SET utf8mb4
);

CREATE TABLE messages_utf8mb4 (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  content VARCHAR(255) CHARACTER SET utf8mb4
);

ここで、例えば「こんにちは😊」のような文字列を入れようとすると、
utf8 の方ではエラーになったり、文字化けしたりします。
utf8mb4 の方は問題なく保存できます。

この差は、

MySQL の utf8 は3バイトまで
絵文字は4バイト必要

という技術的な事情から来ています。

ここでのポイントは、「絵文字を使うかどうか」ではなく、

「将来、想定していなかった文字を入れたときに落ちないか」

という安全性の話です。

utf8mb4 にしておけば、
「この文字だけ入らない」という事故をかなり避けられます。

データベース全体を utf8mb4 にするイメージ

「“テーブルごとにバラバラ”にしないのが大事」

実務でよくある失敗が、

あるテーブルは utf8
別のテーブルは utf8mb4

という「混在状態」です。

こうなると、

JOIN したときに文字コードの変換が走る
一部のテーブルだけ絵文字が入らない
バックアップ・移行時にトラブルが出る

といった面倒が増えます。

理想は、データベースを作る段階でこうしておくことです。

CREATE DATABASE app_db
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_general_ci;

CREATE DATABASE app_db
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_general_ci;

こうしておけば、

そのデータベース内で新しく作るテーブルは、
何も指定しなければ utf8mb4 ＋ utf8mb4_general_ci になる

という「安全な初期設定」が手に入ります。

「あとからテーブルごとに直す」のは本当に大変なので、
Day4 の段階で「最初に決める」が習慣になっていると強いです。

collation の違いを“検索”で感じる

「大文字小文字を区別するかどうかが、WHERE に効いてくる」

次は collation の話です。
まずは分かりやすく「大文字小文字」の違いを見ます。

同じ utf8mb4 でも、collation を変えた2つのテーブルを用意します。

CREATE TABLE users_general (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(50)
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

CREATE TABLE users_cs (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(50)
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_as_cs;

CREATE TABLE users_general (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(50)
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

CREATE TABLE users_cs (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(50)
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_as_cs;

前者は ci（case insensitive）なので大文字小文字を区別しない、
後者は cs（case sensitive）なので区別する、という性格です。

両方に同じデータを入れてみます。

INSERT INTO users_general (name)
VALUES ('taro'), ('Taro');

INSERT INTO users_cs (name)
VALUES ('taro'), ('Taro');

INSERT INTO users_general (name)
VALUES ('taro'), ('Taro');

INSERT INTO users_cs (name)
VALUES ('taro'), ('Taro');

ここで、次のクエリを実行してみるイメージです。

SELECT * FROM users_general WHERE name = 'taro';
SELECT * FROM users_cs      WHERE name = 'taro';

SELECT * FROM users_general WHERE name = 'taro';
SELECT * FROM users_cs      WHERE name = 'taro';

users_general の方は、’taro’ と ‘Taro’ の両方がヒットします。
users_cs の方は、’taro’ だけがヒットします。

同じ SQL なのに、collation の違いで結果が変わる。
これが「collation は比較のルール」という意味です。

メールアドレスのように「大文字小文字を区別しない」のが自然な項目は、
utf8mb4_general_ci や utf8mb4_unicode_ci が相性がいいです。

collation の違いを“ソート”で感じる

「ORDER BY の結果が“微妙に違う”世界」

次に、ORDER BY での違いをイメージしてみます。

例えば、こんなデータがあるとします。

INSERT INTO users_general (name)
VALUES ('apple'), ('Banana'), ('banana'), ('Apple');

INSERT INTO users_general (name)
VALUES ('apple'), ('Banana'), ('banana'), ('Apple');

utf8mb4_general_ci の場合、
大文字小文字を区別しないので、
ソート結果は「だいたい」こんな感じになります。

apple
Apple
Banana
banana

一方で、case sensitive な collation だと、
ASCIIコード順に近い並びになり、

Apple
Banana
apple
banana

のように、「大文字が先、小文字が後」という結果になることがあります。

ここで大事なのは、
「どっちが正しいか」ではなく、

「自分が欲しい並びと、実際の並びが一致しているか」

です。

ユーザー一覧の表示順
タグのアルファベット順
日本語の五十音順

こういったところで、「あれ、なんか順番おかしくない？」となったとき、
collation を疑えるかどうかが分かれ目です。

日本語と collation

「Day4では“まずは utf8mb4_general_ci でOK”と割り切る」

MySQL には、日本語向けの collation もあります。

utf8mb4_ja_0900_as_cs など、
「ja（日本語）」「as（アクセント区別）」「cs（大文字小文字区別）」
といった名前が付いているものです。

ただ、Day4 の段階でここに深く入り込むと、
一気に難易度が上がります。

現実的な落としどころとしては、

まずは utf8mb4_general_ci か utf8mb4_unicode_ci を使う
日本語のソート順がどうしても気になる場面が出てきたら、
そのときに「日本語向け collation を検討する」

くらいで十分です。

大事なのは、

「日本語の並び順がおかしいとき、collation という概念があることを思い出せる」

ということです。
知らないと、アプリ側で無理やり並び替えようとして、
余計に複雑になります。

セキュリティ・品質の観点から見た utf8mb4 / collation

「“最初にちゃんと決める”ことが、後の事故を減らす」

文字コードと collation は、一見「表示の問題」に見えますが、
実はセキュリティや品質にも直結します。

utf8mb4 を使う
→ 一部の文字だけ保存できない、という不具合を防ぐ
→ 入力値を無理に削ったり変換したりする必要が減る

collation を意識する
→ メールアドレスのユニーク制約が期待どおりに働く
→ WHERE での比較が「アプリの想定」とズレない
→ 「同じはずの値が重複して入ってしまう」事故を防ぐ

特に、認証・認可まわりでは、

メールアドレスの大小文字をどう扱うか
ユーザー名の比較をどうするか

といった仕様と、collation の設定がズレていると、
「ログインできたりできなかったり」という不安定な状態になります。

Day4 の時点で、

「文字コードと collation は、“最初にちゃんと決めるべき設計項目”だ」

と認識できていれば、それだけでかなりレベルが高いです。

Day4 後半のまとめ

utf8mb4 を使うと、絵文字など4バイト文字も含めて安全に保存できる一方、MySQLの utf8 は3バイトまでしか扱えず、一部の文字がエラーや文字化けの原因になるため、新規プロジェクトでは基本的に utf8mb4 一択と考えてよい。
データベース作成時に DEFAULT CHARACTER SET utf8mb4 を指定しておくと、その中で作るテーブルが自動的に utf8mb4 ベースになり、「テーブルごとに文字コードがバラバラ」という危険な状態を避けられる。
collation は文字列の比較・ソート・ユニーク制約に直接影響し、utf8mb4_general_ci のような case insensitive な設定では ‘taro’ と ‘Taro’ を同じとみなす一方、case sensitive な collation では別物として扱われるため、メールアドレスやユーザー名などの仕様と合わせて選ぶ必要がある。
ORDER BY の結果や WHERE のヒット条件が「なんか想定と違う」と感じたとき、collation を疑えるかどうかが重要で、日本語の並び順が問題になる場面では、日本語向け collation を検討する余地があるが、まずは utf8mb4_general_ci / utf8mb4_unicode_ci を標準として押さえておけば十分戦える。
文字コードと collation を「最初にちゃんと決める」ことは、文字化け防止だけでなく、認証・ユニーク制約・比較ロジックの安定性にも直結し、結果的にセキュリティと品質を底上げする設計判断になる。

ここまで押さえられていれば、
「なんとなく utf8」「なんとなくデフォルトの collation」という危うい状態からは完全に卒業です。
次のステップでは、この土台の上にテーブル設計やインデックス設計を積み上げていけます。