MySQLで大文字と小文字を区別する方法｜utf8mb4_binと照合順序の変更

MySQL では、文字列の比較や並べ替えのルールを「照合順序（collation）」で決めています。MySQL 8.0 以降で utf8mb4 を使うときの既定の照合順序は utf8mb4_0900_ai_ci で、名前の最後の ci（case-insensitive）のとおり、大文字と小文字を区別しません。そのため、WHERE username = 'ALICE' で Alice の行が見つかり、一意（UNIQUE）の列に Alice と alice を両方入れることはできません。

この記事では、大文字と小文字を区別したい列の照合順序を変える方法と、クエリだけで区別する方法を説明します。SQL と結果は 2026年10月時点の MySQL 8.4.13 で確認しています。

大文字と小文字を区別する照合順序

照合順序の名前の最後の部分で、区別のしかたが分かります。

大文字と小文字を区別したいときに使う主な照合順序は、次の2つです。

「末尾の空白」は、'a' と 'a '（最後に空白あり）を同じとみなすかどうかです。手元の MySQL 8.4 で確かめた結果は次のとおりです。

SELECT 'Alice' = 'alice'                            AS default_cmp,  -- 1（同じとみなす）
       'Alice' = 'alice' COLLATE utf8mb4_bin        AS bin_cmp,      -- 0（区別する）
       'Alice' = 'alice' COLLATE utf8mb4_0900_as_cs AS as_cs_cmp,    -- 0（区別する）
       'a' = 'a ' COLLATE utf8mb4_bin               AS bin_pad,      -- 1（末尾の空白を無視）
       'a' = 'a ' COLLATE utf8mb4_0900_as_cs        AS as_cs_pad;    -- 0（末尾の空白も区別）

照合順序ごとの PAD 属性は、次の SQL で確認できます。

SELECT COLLATION_NAME, PAD_ATTRIBUTE
FROM information_schema.COLLATIONS
WHERE COLLATION_NAME IN ('utf8mb4_0900_ai_ci', 'utf8mb4_0900_as_cs', 'utf8mb4_bin', 'utf8mb4_0900_bin');

MariaDB では照合順序の名前と既定値が違います。2026年10月時点の MariaDB 11.8 では、utf8mb4 の既定の照合順序は utf8mb4_uca1400_ai_ci です。utf8mb4_bin は MariaDB でも使え、大文字と小文字を区別します。

新しくテーブルを作るときに指定する

区別したい列にだけ、照合順序を指定します。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    email VARCHAR(255) NOT NULL,
    UNIQUE KEY uk_username (username)
);

この例では username だけ大文字と小文字を区別し、email はテーブルの既定（大文字と小文字を区別しない）のままにしています。メールアドレスは大文字と小文字を区別せずに扱われるのが一般的で、User@example.com と user@example.com を別のユーザーとして登録できてしまうと、重複登録の原因になります。区別するかどうかは、列ごとに目的に合わせて決めます。

データベース全体の既定を変えることもできます。この場合、後から作るテーブルで照合順序を指定しなかった列は、すべて大文字と小文字を区別するようになります。

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

既存の列の照合順序を変える

特定の列だけを変えるには、ALTER TABLE ... MODIFY で列の定義を書き直します。NOT NULL などの既存の属性も書き直す必要があるので、先に SHOW CREATE TABLE で今の定義を確認してください。

SHOW CREATE TABLE users;

ALTER TABLE users
  MODIFY username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; を使うと、テーブルにある文字列の列がすべて変わります。区別したくない列（メールアドレスなど）まで変わるので、目的の列だけを変えるときは MODIFY を使います。

変更後は次のどちらかで確認します。

SHOW FULL COLUMNS FROM users;

SELECT COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users';

一意キーへの影響

UNIQUE キーのある列では、照合順序を変えると重複の判定も変わります。

• 区別しない（_ci）→ 区別する（_bin）への変更は、今あるデータがそのまま入ります。変更後は Alice と alice を両方登録できるようになります
• 区別する → 区別しない への変更は、Alice と alice が両方あると ERROR 1062 (23000): Duplicate entry で失敗します

実際に、区別しない照合順序の列に Alice を入れた後で alice を入れると、次のエラーになりました。

ERROR 1062 (23000): Duplicate entry 'alice' for key 'users.uk_username'

ALTER TABLE はテーブルを作り直すことがあり、大きなテーブルでは時間がかかります。本番では、バックアップを取ってから、アクセスの少ない時間に実行してください。

列を変えずにクエリだけで区別する

列の照合順序は変えずに、特定のクエリだけ大文字と小文字を区別したい場合は、COLLATE で比較に使う照合順序を指定します。

SELECT * FROM users WHERE username = 'Alice' COLLATE utf8mb4_bin;

ただし、列と違う照合順序で比べると、その列のインデックスを使った絞り込みができず、インデックス全体を読むことになります。手元で EXPLAIN を実行すると、type は index（インデックス全体を読む）でした。行が多いテーブルでは、次のように通常の比較でインデックスを使って候補を絞り、COLLATE で最終的に区別する書き方にすると、type が const になり、インデックスを使った検索になりました。

SELECT * FROM users
WHERE username = 'Alice'
  AND username = 'Alice' COLLATE utf8mb4_bin;

WHERE BINARY username = 'Alice' という書き方も動きますが、MySQL 8.0.27 以降では非推奨で、実行すると 'BINARY expr' is deprecated という警告が出ます。新しく書く SQL では COLLATE を使ってください。

照合順序を変えると変わること

• 検索：= だけでなく LIKE も大文字と小文字を区別するようになります。WHERE username LIKE 'a%' で Alice は見つからなくなります
• 並べ替え：utf8mb4_bin では ORDER BY がコードポイント順になり、Alice が alice より先、B が a より先に並びます
• アプリケーション：ログイン名などを区別するようにした場合、利用者が大文字と小文字を間違えるとログインできなくなります。画面の案内や入力時の扱い（小文字にそろえるかどうか）も合わせて決めてください

WordPressのテーブルについて

WordPress のテーブルは、wp-config.php の DB_CHARSET・DB_COLLATE とデータベースの設定に従って作られ、通常は大文字と小文字を区別しない照合順序（utf8mb4_unicode_520_ci など）になっています。WordPress 本体のテーブル（wp_users など）の照合順序を変えると、ユーザー名の検索や重複チェックの動きが変わるので、変更しないでください。この記事の方法は、プラグインや独自アプリケーションで自分が作ったテーブルに使います。

まとめ

• MySQL 8.0 以降の既定の照合順序 utf8mb4_0900_ai_ci は、大文字と小文字を区別しない
• 区別したい列は utf8mb4_bin か utf8mb4_0900_as_cs にする。utf8mb4_bin は末尾の空白を無視し、utf8mb4_0900_as_cs は区別する
• 既存の列は ALTER TABLE ... MODIFY で変える。CONVERT TO はすべての文字列の列が変わる
• クエリだけで区別するなら COLLATE utf8mb4_bin を使い、インデックスを使うために通常の比較と組み合わせる
• BINARY 演算子は MySQL 8.0.27 以降で非推奨

関連記事:

• MySQLの文字コードと照合順序の設定
• MySQLの文字コード・照合順序を変更する方法
• データベースの文字コードをUTF-8にする方法