COLLATION(照合順序)の意味や確認方法を紹介します。
COLLATION(照合順序)の意味
COLLATION(照合順序)とは、文字列を比較・並べ替えするときのルールのことです。
「大文字と小文字を同じとみなすか」「全角と半角を同じとみなすか」といった判定基準が照合順序で決まり、WHERE句の一致判定やORDER BYの並び順に影響します。
データベース、テーブル、カラムの単位で設定でき、指定しなければ上位(サーバー→データベース→テーブル)の値を引き継ぎます。
なお、MySQL 8.0以降のデフォルトはutf8mb4_0900_ai_ciです(MySQL 5.7まではlatin1_swedish_ci、utf8mb4指定時はutf8mb4_general_ci)。
以下では、今でもよく見かける3つの照合順序の違いを押さえます。
utf8mb4_general_ci
英字の大文字小文字を区別しません。
半角と全角は区別します。
MySQL 5.7以前のutf8mb4のデフォルトで、既存のプロジェクトでは今も多く使われています。
utf8mb4_unicode_ci
大文字小文字に加えて、全角半角、ひらがな・カタカナ、濁音・半濁音も区別しません。
「は」「ハ」「パ」が同じ文字として一致するため、日本語データの検索では意図しない一致が起きやすい点に注意が必要です。
utf8mb4_bin
すべて区別します。
バイナリとして比較するため、パスワードやトークンなど完全一致が必要なカラムに向いています。
COLLATION(照合順序)の確認方法
現在のデータベースやテーブルにどの照合順序が設定されているかは、次のSQLで確認できます。
-- サーバー・接続・データベースの照合順序をまとめて確認
SHOW VARIABLES LIKE 'collation%';
-- 現在のデータベースの照合順序だけ確認
SELECT @@collation_database;
-- テーブルごとの照合順序を確認
SHOW TABLE STATUS WHERE Name = 'テーブル名';
-- カラムごとの照合順序を確認(Collation列に表示される)
SHOW FULL COLUMNS FROM テーブル名;サーバーが対応している照合順序の一覧は、SHOW COLLATIONで表示できます。
-- 一覧の表示
show collation
-- utf8に絞って表示
show collation where charset like '%utf8%';表のCollationの列の隣に、対応しているcharset(文字コード)が記載されています。
Default列がYesの行が、そのcharsetを指定したときに使われる照合順序です。
COLLATIONの設定方法
-- テーブル作成
create table テーブル名 (
col varchar(10)
)
character set 'utf8mb4'
collate 'utf8mb4_general_ci';collateでcollationを指定できます。
既存のテーブルやカラムを変更する場合は、ALTER TABLEを使います。
-- テーブル全体(既存カラムも含めて)を変換
ALTER TABLE テーブル名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 特定のカラムだけ変更
ALTER TABLE テーブル名 MODIFY col VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;照合順序は同じ文字コード(charset)の中から選びます。
charsetそのものの違い(utf8とutf8mb4など)は別の話なので、MySQL DDLの書き方のCREATE TABLEの項も合わせて確認してみてください。
COLLATIONで検索する
select * from テーブル名 where 属性 = '値';
select * from テーブル名 where 属性 COLLATE utf8mb4_unicode_ci = '値';
select * from テーブル名 where 属性 COLLATE utf8mb4_bin = '値';カラムの照合順序と異なる照合順序で比較したいときは、上のようにCOLLATE句を条件式に直接つけます。
たとえばutf8mb4_general_ciのカラムで大文字小文字を区別して検索したい場合は、COLLATE utf8mb4_binをつければテーブルを変更せずに済みます。
照合順序でよくあるエラー
JOINやUNIONでIllegal mix of collationsというエラーが出る場合は、比較している2つのカラムの照合順序が違うことが原因です。
SHOW FULL COLUMNSで両方のカラムの照合順序を確認し、ALTER TABLEで揃えるか、比較する側にCOLLATE句をつけて一時的に合わせます。
サーバー全体の既定値を変えたい場合は、設定ファイルのcollation-server(とcharacter-set-server)を変更します。
設定値の確認と変更の仕方は、MySQLのシステム変数の確認、取得、変更方法を参考にしてみてください。