以外と原因に気づかなかったりするんですよねこれ。
MySQLを使っていて、たまに以下のようなエラーが起きる事があります。
[SQLで例外(Message=[Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'], ErrorCode=1267, SQLState=HY000)が発生しました。
エラーメッセージ通りなら、文字コードが何か誤っている、という事なのですが、具体的に何が原因なのでしょう。
よくあるエラーケース
MySQLのバージョンは5.5〜5.7が対象になります。この記事の事を実際に試したい場合は、docker-machineでmysqlのコンテナを作るとよいです。以前書いたこちらの記事も合わせてご覧下さい。
www.bunkei-programmer.net
MySQLの設定はこんな感じです。
mysql> show variables like 'char%'; ＋--------------------------＋----------------------------＋ ｜ Variable_name ｜ Value ｜ ＋--------------------------＋----------------------------＋ ｜ character_set_client ｜ utf8mb4 ｜ ｜ character_set_connection ｜ utf8mb4 ｜ ｜ character_set_database ｜ utf8mb4 ｜ ｜ character_set_filesystem ｜ binary ｜ ｜ character_set_results ｜ utf8mb4 ｜ ｜ character_set_server ｜ utf8mb4 ｜ ｜ character_set_system ｜ utf8 ｜ ｜ character_sets_dir ｜ /usr/share/mysql/charsets/ ｜ ＋--------------------------＋----------------------------＋
文字コードがutf8mb4のデータベースを作成します。
create database hoge charset utf8mb4;
テーブルがutf8mb4以外のケース
文字コードがlatin1のテーブルを作ります。
drop table if exists tbl_table_charset; create table tbl_table_charset( v varchar(10) )engine=innodb charset=latin1;
このテーブルでwhere句にマルチバイト文字列を使おうとすると、このエラーが起きます。
mysql> select * from tbl_table_charset where v = 'あ'; ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='
insertでマルチバイトを使おうとすると、また違ったエラーになります。
mysql> insert into tbl_table_charset values('あ'); ERROR 1366 (HY000): Incorrect string value: '\xE3\x81\x82' for column 'v' at row 1
データが無い状態でupdateした場合は、エラーになりません。
mysql> update tbl_table_charset set v = 'あ'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0
データが有る状態でupdataした場合は、先ほどと同じエラーになります。
mysql> insert into tbl_table_charset values('a'); Query OK, 1 row affected (0.00 sec) mysql> update tbl_table_charset set v = 'あ'; ERROR 1366 (HY000): Incorrect string value: '\xE3\x81\x82' for column 'v' at row 1
カラムがutf8mb4以外のケース
utf8mb4のテーブルに、latin1のカラムを混ぜ込みます。
drop table if exists tbl_col_charset; create table tbl_col_charset( v varchar(10) character set latin1 )engine=innodb charset=utf8mb4;
そしてlatin1のカラムに大して前述と同様にwhere句にマルチバイトを使ってしまうと、やはりエラーになります。
mysql> select * from tbl_col_charset where v = 'あ'; ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='
こんなケース滅多に遭遇しないだろ、と思いますか？以外とそうでもありません。
ケース1）大きすぎるテーブル
InnoDBのファイルフォーマットがAntelopeになっている。その状態でInnoDBのテーブルを作成し、カラムに大きな値のカラムを沢山作ってしまい、大量のデータをinsertしようとして
ERROR 1118 (42000) at line 1: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs
こんなエラーが出てしまい、テーブル容量の節約をしようとして、シングルバイトしか入らないカラムを片っ端からlatin1にしてしまった場合です。
カラムの文字コードを個別に設定した場合、そういう設定がされている事を調べるには「show create table xxx」するしかありません。「desc xxx」では解らないのです。なので、「え！？このカラムlatin1なの！？」と後から知って愕然とするケースもあります。
ケース2）メールアドレス
メールアドレスのカラムを作る時、最も問題となるのはフィールド長です。
文字コードをutf8mb4にすると、インデックスが貼れるのは最大で191byteまでです。191文字で済む要件の場合は問題無いのですが、「500文字まで受け付けたい！」等と言われると、途中までしかインデックス化できません。そこでよく使われる手法として、メアドのカラムを複数に分割して、ユニークキーのインデックスを貼る事で解決を図ります。その点については大分前に記事を書いたので、合わせてご覧下さい。
こうしてlatin1のカラムが生まれたりします。
で、何が問題なのか
先ほどのコンソールを見て貰うと解りますが、selectしただけでエラーが発生してしまう点にあります。つまり、アプリケーションからlatin1等のマルチバイト非対応カラムに対してマルチバイトで検索するwhere句を発行されると即エラーになるのです。と言うことは、バリデーション処理が必須になるという事に繋がります。
例えば「商品コード」というvarcharで英数字が入るカラムがあったとして、通常ならそのカラムが「あああ」等と検索されてもエラーにならず、0件の結果が返ります。しかしこの商品コードがlatin1等である場合、「あああ」と検索された場合はSQLExceptionが確定してしまうので、シングルバイトの入力チェックバリデーションが必須になります。商品コードは最初はutf8mb4だったがいつからかlatin1に変更されていて、最近「Illegal mix of collations (latin1_swedish_ci,IMPLICIT)」なんてエラーが出るんだけど、なんぞこれ？というケースがあります。今まで必要無かったバリデーションが必要になってしまうのです。
雑感
このお話、残念ながらノンフィクションです・・・
antelopeとbarracudaの違いも知らず、row_type？何それ？という時代に設計したテーブルが段々と肥大化してしまい、ついにantelope＋utf8mb4のInnoDBテーブルのサイズの限界に達してしまい、大きいデータが来た時にのみエラーが出てしまうという状況に遭遇し、以下のエラーに悩まされました。
ERROR 1118 (42000) at line 1: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs
この制限は確かMySQL5.5以降から発生するので、「joinするの面倒だから1テーブルに全項目を押し込めようぜ！」等と横着をしたりせず、適宜テーブルを分割するようにすれば、このエラーは起きにくくなります。また、もしInnoDBファイルフォーマットがBarracudaではなくAntelopeになっているのであれば、できるならBarracudaに変更し、create tableする際は「row_format=dynamic」を付けると、こういった制限に引っかかりにくくなります。現在のInnoDBファイルフォーマットは以下で確認可能です。
mysql> show variables like 'innodb_file_format'; +--------------------+-----------+ | Variable_name | Value | +--------------------+-----------+ | innodb_file_format | Antelope | +--------------------+-----------+
この辺りを疎かにしていると私のようにアホな目に合うので、手遅れにならない内に確認した方がいいでしょう。
