照合順序の異なるテーブルを結合してエラーが発生した場合

異なる照合順序の結合でエラーが発生する場合がある。

mysql> show table status like 'foo' \G;
*************************** 1. row ***************************
Collation: utf8_unicode_ci

mysql> show table status like 'hoge' \G;
*************************** 1. row ***************************
Collation: utf8_general_ci

mysql> desc foo;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| code | varchar(32) | YES | | NULL | |
| name | varchar(256) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc hoge;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| code | varchar(32) | YES | | NULL | |
| val | varchar(256) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

結合でエラー発生
↓
mysql> select * from hoge h, foo f where h.code = f.code;
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

上記のエラー回避するには、COLLATE節を使用して、どちらかのデフォルトの照合順序を無効にしてしまえば良い

mysql> select * from hoge h, foo f where h.code = f.code collate utf8_general_ci;
+------+--------+------+--------+
| code | val | code | name |
+------+--------+------+--------+
| 0001 | ほげ | 0001 | ふー |
+------+--------+------+--------+
1 row in set (0.00 sec)

mysql> select * from hoge h, foo f where h.code collate utf8_unicode_ci = f.code;
+------+--------+------+--------+
| code | val | code | name |
+------+--------+------+--------+
| 0001 | ほげ | 0001 | ふー |
+------+--------+------+--------+
1 row in set (0.00 sec)