MySQL Illegal mix of collations エラーが出る・出ないまとめ

Illegal mix of collations

Illegal mix of collations エラーは 異なる照合順序で結合や比較を行った場合に発生するエラーです。

mysql> SELECT CONCAT(_utf8mb4 'A' COLLATE utf8mb4_bin, _utf8mb4 'B' COLLATE utf8mb4_unicode_ci);
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_bin,EXPLICIT) and (utf8mb4_unicode_ci,EXPLICIT) for operation 'concat'

mysql> SELECT _utf8mb4 'A' COLLATE utf8mb4_bin = _utf8mb4 'B' COLLATE utf8mb4_unicode_ci;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_bin,EXPLICIT) and (utf8mb4_unicode_ci,EXPLICIT) for operation '='

エラーが発生しない場合もある

照合順序が異なっていても、エラーにならないケースもあります。 latin1_swedish_ci と utf8mb4_unicode_ci と異なる照合順序で比較していますが、エラーなく実行することが出来てます。

mysql> SELECT _latin1 'A' COLLATE latin1_swedish_ci = _utf8mb4 'B' COLLATE utf8mb4_unicode_ci AS r;
+------+
| r    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

Illegal mix of collations エラーが発生する・しない まとめ

雑なコードを書いて、主な照合順序を総当たりでチェックしてみました*1。

name	sjis_japanese_ci	sjis_bin	ujis_japanese_ci	ujis_bin	utf8_general_ci	utf8_unicode_ci	utf8_bin	utf8mb4_general_ci	utf8mb4_unicode_ci	utf8mb4_bin	latin1_swedish_ci	latin1_bin	ascii_general_ci	ascii_bin
sjis_japanese_ci	-	ERR	ERR	ERR	OK	OK	OK	OK	OK	OK	ERR	ERR	ERR	ERR
sjis_bin	ERR	-	ERR	ERR	OK	OK	OK	OK	OK	OK	ERR	ERR	ERR	ERR
ujis_japanese_ci	ERR	ERR	-	ERR	OK	OK	OK	OK	OK	OK	ERR	ERR	ERR	ERR
ujis_bin	ERR	ERR	ERR	-	OK	OK	OK	OK	OK	OK	ERR	ERR	ERR	ERR
utf8_general_ci	OK	OK	OK	OK	-	ERR	ERR	OK	OK	OK	OK	OK	OK	OK
utf8_unicode_ci	OK	OK	OK	OK	ERR	-	ERR	OK	OK	OK	OK	OK	OK	OK
utf8_bin	OK	OK	OK	OK	ERR	ERR	-	OK	OK	OK	OK	OK	OK	OK
utf8mb4_general_ci	OK	OK	OK	OK	OK	OK	OK	-	ERR	ERR	OK	OK	OK	OK
utf8mb4_unicode_ci	OK	OK	OK	OK	OK	OK	OK	ERR	-	ERR	OK	OK	OK	OK
utf8mb4_bin	OK	OK	OK	OK	OK	OK	OK	ERR	ERR	-	OK	OK	OK	OK
latin1_swedish_ci	ERR	ERR	ERR	ERR	OK	OK	OK	OK	OK	OK	-	ERR	ERR	ERR
latin1_bin	ERR	ERR	ERR	ERR	OK	OK	OK	OK	OK	OK	ERR	-	ERR	ERR
ascii_general_ci	ERR	ERR	ERR	ERR	OK	OK	OK	OK	OK	OK	ERR	ERR	-	ERR
ascii_bin	ERR	ERR	ERR	ERR	OK	OK	OK	OK	OK	OK	ERR	ERR	ERR	-

utf8, utf8mb4 は他のキャラクタセットとの比較が可能

mysql> SELECT _utf8mb4"A" COLLATE utf8mb4_general_ci = _sjis"A" COLLATE sjis_japanese_ci;
+----------------------------------------------------------------------------+
| _utf8mb4"A" COLLATE utf8mb4_general_ci = _sjis"A" COLLATE sjis_japanese_ci |
+----------------------------------------------------------------------------+
|                                                                          1 |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

ただし、同じ UTF8 の他の照合順序 (例：utf8mb4_general_ci vs utf8mb4_unicode_ci) とは比較できない

mysql> SELECT _utf8mb4"A" COLLATE utf8mb4_general_ci = _utf8mb4"A" COLLATE utf8mb4_unicode_ci;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,EXPLICIT) and (utf8mb4_unicode_ci,EXPLICIT) for operation '='

一方、uft8, utf8mb4 以外はキャラクタセット・照合順序が一致していないと比較できない。

mysql> SELECT _sjis"A" COLLATE sjis_bin = _sjis"A" COLLATE sjis_japanese_ci;
ERROR 1267 (HY000): Illegal mix of collations (sjis_bin,EXPLICIT) and (sjis_japanese_ci,EXPLICIT) for operation '='

という、法則のようです。

*1:version 5.7.37 で試しました