MySQLの照合順序(COLLATE)をSQLで解説

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

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,
  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_cs: アルファベットの大文字小文字を区分する。
utf8mb4_unicode_cs: アルファベットの大文字小文字を区分する。全角半角を区別しない。濁音、半濁音を「は＝ぱ＝ば」として区分しない。
utf8mb4_general_ci: アルファベットの大文字小文字を区分しない。
utf8mb4_unicode_ci: アルファベットの大文字小文字を区分しない。全角半角を区別しない。濁音、半濁音を「は＝ぱ＝ば」として区分しない。

（例）

CREATE TABLE t_test (
  c1 VARCHAR(255) CHARACTER SET 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';