collation(照合順序)衝突の問題

MySQLで、以下のようなエラーに悩まされたことはないだろうか。

ERROR 1267 (HY000): Illegal mix of collations (sjis_japanese_ci,IMPLICIT) and (cp932_japanese_ci,IMPLICIT) for operation 'UNION'
ERROR 1267 (HY000): Illegal mix of collations (sjis_japanese_ci,IMPLICIT) and (cp932_japanese_ci,IMPLICIT) for operation '='

ちなみにこれは、以下のようなSQL文を順に実行したときに発生する。

CREATE TABLE t1(
      id VARCHAR(8) NOT NULL
    , name VARCHAR(8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=sjis;

CREATE TABLE t2(
      id VARCHAR(8) NOT NULL
    , name VARCHAR(8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=cp932;

SELECT id, name FROM t1
UNION ALL
SELECT id, name FROM t2;

SELECT t1.id, t1.name, t2.id, t2.name
FROM t1, t2
WHERE t1.id = t2.id;

2つのテーブルの文字エンコーディング設定が異なるため、それぞれに含まれるカラムのCollation(照合順序)も異なってくる。異なるCollation間では、UNIONや比較ができない。そのため上記のようなエラーになる。
(まぁ、そもそもこんな壊れたテーブル設計なんてぶち壊してしまえという話だが、自分の一存ではぶち壊せないので(謎))

ところで、Collationが違うと常にだめなのか、ということについて疑問に思ったので、検証してみた。
まず、以下のようなカオスなテーブルを作成する。

CREATE TABLE hoge(
      id int NOT NULL DEFAULT 1
    , s11 VARCHAR(8) CHARACTER SET sjis                        NOT NULL DEFAULT 'hello'
    , s12 VARCHAR(8) CHARACTER SET sjis    collate sjis_bin    NOT NULL DEFAULT 'hello'
    , s21 VARCHAR(8) CHARACTER SET ujis                        NOT NULL DEFAULT 'hello'
    , s22 VARCHAR(8) CHARACTER SET ujis    collate ujis_bin    NOT NULL DEFAULT 'hello'
    , s31 VARCHAR(8) CHARACTER SET cp932                       NOT NULL DEFAULT 'hello'
    , s32 VARCHAR(8) CHARACTER SET cp932   collate cp932_bin   NOT NULL DEFAULT 'hello'
    , s41 VARCHAR(8) CHARACTER SET eucjpms                     NOT NULL DEFAULT 'hello'
    , s42 VARCHAR(8) CHARACTER SET eucjpms collate eucjpms_bin NOT NULL DEFAULT 'hello'
    , s51 VARCHAR(8) CHARACTER SET utf8                        NOT NULL DEFAULT 'hello'
    , s52 VARCHAR(8) CHARACTER SET utf8    collate utf8_bin    NOT NULL DEFAULT 'hello'
    , s61 VARCHAR(8) CHARACTER SET utf8mb4                     NOT NULL DEFAULT 'hello'
    , s62 VARCHAR(8) CHARACTER SET utf8mb4 collate utf8mb4_bin NOT NULL DEFAULT 'hello'
    , s71 VARCHAR(8) CHARACTER SET latin1                      NOT NULL DEFAULT 'hello'
    , s72 VARCHAR(8) CHARACTER SET latin1  collate latin1_bin  NOT NULL DEFAULT 'hello'
    , s81 VARCHAR(8) CHARACTER SET ascii                       NOT NULL DEFAULT 'hello'
    , s82 VARCHAR(8) CHARACTER SET ascii   collate ascii_bin   NOT NULL DEFAULT 'hello'
);
INSERT INTO hoge(id) VALUES(1234);

まぁ、実際にこんなテーブルがあったら、まずぶち壊すところから始めるところだが、今回は実験用ということで。
で、このテーブルに対して、以下のようなクエリを全組み合わせについて発行する。

SELECT h1.id FROM hoge h1, hoge h2 WHERE h1.s11 = h2.s12 ;
SELECT h1.id FROM hoge h1, hoge h2 WHERE h1.s11 = h2.s21 ;
SELECT h1.id FROM hoge h1, hoge h2 WHERE h1.s11 = h2.s22 ;
  :

すると、結果は次のようになる。

(1)

(2)

(3)

(4)

(5)

(6)

(7)

(8)

(9)

(10)

(11)

(12)

(13)

(14)

(15)

(16)

(1) sjis

--

O

X

X

X

X

X

X

O

O

O

O

X

X

O

O

(2) sjis(b)

O

--

X

X

X

X

X

X

O

O

O

O

X

X

O

O

(3) ujis

X

X

--

O

X

X

X

X

O

O

O

O

X

X

O

O

(4) ujis(b)

X

X

O

--

X

X

X

X

O

O

O

O

X

X

O

O

(5) cp932

X

X

X

X

--

O

X

X

O

O

O

O

X

X

O

O

(6) cp932(b)

X

X

X

X

O

--

X

X

O

O

O

O

X

X

O

O

(7) eucjpms

X

X

X

X

X

X

--

O

O

O

O

O

X

X

O

O

(8) eucjpms(b)

X

X

X

X

X

X

O

--

O

O

O

O

X

X

O

O

(9) utf8

O

O

O

O

O

O

O

O

--

O

O

O

O

O

O

O

(10) utf8(b)

O

O

O

O

O

O

O

O

O

--

O

O

O

O

O

O

(11) utf8mb4

O

O

O

O

O

O

O

O

O

O

--

O

O

O

O

O

(12) utf8mb4(b)

O

O

O

O

O

O

O

O

O

O

O

--

O

O

O

O

(13) latin1

X

X

X

X

X

X

X

X

O

O

O

O

--

O

O

O

(14) latin1(b)

X

X

X

X

X

X

X

X

O

O

O

O

O

--

O

O

(15) ascii

O

O

O

O

O

O

O

O

O

O

O

O

O

O

--

O

(16) ascii(b)

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

--

後ろに「(b)」がついているのはバイナリ(_bin)、ついていないのは何ていったらいいんだ(_japanese_ci)。
こうしてみると、utf8、utf8mb4、asciiは(調べた範囲内での)どの組み合わせでも比較などが問題なくできることが分かる。