MySQLの照合順序（COLLATE）の使い方と違い｜utf8mb4_unicode_ciの意味と確認方法

MySQLのキャラクタセット(CHARACTER SET)の「utf8」と「utf8mb4」違い

MySQLやMariaDBで照合順序（COLLATE）を正しく設定していますか？
このページでは、utf8mb4_unicode_ci などの照合順序の意味や違い、データベース・テーブル・カラムごとの確認・変更方法を、実際のSQL例とともにわかりやすく解説します。
「mysql 照合順序 確認」「collate utf8mb4_unicode_ci」「mysql collation」などのキーワードでお探しの方におすすめです。

utf8

1文字で1～3バイト文字のUTF8のみ対応

utf8mb4

1文字で1～4バイト文字のUTF8に対応

一般的にUTF8は1文字で１～４バイト必要なので、MySQLでは文字セット「utf8mb4」を使うのが望ましい。
（例）データベース作成時

CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE t_test (
  id SERIAL PRIMARY KEY,
  c1 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

MySQLの照合順序（COLLATE）

utf8mb4_general_ci

アルファベットの大文字小文字を区分しない。

utf8mb4_unicode_ci

アルファベットの大文字小文字を区分しない。全角半角を区別しない。
濁音、半濁音を「は＝ぱ＝ば」として区分しない。

utf8mb4_general_cs

アルファベットの大文字小文字を区分する。
MariaDB(MySQL)では使用できない

utf8mb4_unicode_cs

アルファベットの大文字小文字を区分する。全角半角を区別しない。
濁音、半濁音を「は＝ぱ＝ば」として区分しない。
MariaDB(MySQL)では使用できない

（例）

CREATE TABLE t_test (
  c1 VARCHAR(255) NOT NULL
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
insert into t_test values('は');
insert into t_test values('ぱ');
insert into t_test values('ば');
select count(*) from t_test where c1='は';
⇒ 3 を返す

照合順序（COLLATE）の確認

■データベースの照合順序（COLLATE）の確認

use データベース名;
SELECT @@character_set_database, @@collation_database;

■テーブルの照合順序（COLLATE）の確認

SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema = 'データベース名';

■カラムの照合順序（COLLATE）の確認

select table_name, column_name, collation_name from information_schema.columns where table_schema='データベース名' and collation_name is not null;

照合順序（COLLATE）の変更

■データベースの照合順序（COLLATE）の変更

ALTER DATABASE データベース名 COLLATE utf8mb4_unicode_ci;

■テーブルの照合順序（COLLATE）の変更

ALTER TABLE テーブル名 COLLATE utf8mb4_unicode_ci;

■カラムの照合順序（COLLATE）の変更(例)

ALTER TABLE テーブル名 MODIFY COLUMN カラム名 varchar(255) COLLATE utf8mb4_unicode_ci;