読者です 読者をやめる 読者になる 読者になる

HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

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は(調べた範囲内での)どの組み合わせでも比較などが問題なくできることが分かる。