ど素人から毛を生やす。<延>

心当たりのない「illegal mix of collations for operation」

Web > Other 2017年10月26日(最終更新:28日前)

どもです。
MySQLにて、テスト環境では問題のなかった

SELECT
	CONCAT(文字列1, 文字列2)
FROM
	対象テーブル

CONCATが、本番環境のときだけ

illegal mix of collations for operation 'concat'

なんてエラーを吐きおってくださりました。

意味は「文字コード違うからCONCATできねーぞ?」。

カラムごとに文字コードを変更する例はありますが、
今回の場合、双方とも文字コードを特別設定していません。

では、何の文字コードが違っているのか?

答えは「サーバーとクライアント」です。

サーバーとクライアント

まず、MySQLの状態は

show variables like 'char%';

で確認します。
環境や使用ソフトによって様々ですが、だいたいこんな返事が来ます。

+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | utf8   |
| character_set_connection | utf8   |
| character_set_database   | ujis   |
| character_set_filesystem | binary |
| character_set_results    | utf8   |
| character_set_server     | ujis   |
| character_set_system     | utf8   |
+--------------------------+--------+

このなかで、今回重要なのはこのあたり。

character_set_client クライアントの文字コード
character_set_connection クライアントから受け取った文字をこの文字コードに変換する
character_set_database 参照中のDBの文字コード
character_set_results クライアントへ送信する文字をこの文字コードに変換する
character_set_server DB作成時のデフォルトの文字コード

基本的に、上記全てが同じ文字コードであれば、文字化けや今回のようなエラーは発生しないらしく。

今回、どうやらDBの文字コードだけが「ujis」になっていて、それが原因のよう。
ここまでわかれば対処はできます。

クライアントの文字コードを変更してしまう

SET NAMES "ujis";
// SET character_set_client = ujis;
// SET character_set_results = ujis;
// SET character_set_connection = ujis;

あたりを使ってしまいましょう。

SET NAMES "ujis";
SELECT
	CONCAT(文字列1, 文字列2)
FROM
	対象テーブル

これでエラーは発生せず、CONCATを行うことができました。

ただし、SET NAMESには注意点が2点。
①SQLインジェクションのリスクがあるため、PHPに記載するとかはNG(PDOとか使うこと)。
②MySQLを直接操作する場合、SET NAMESを使用すると表示が文字化けするシチュエーションがあり(今までutfで見ていたのものを急に「ujisで」とかいうから!)、そのまま他の処理を行うと、文字化け状態でデータが保存される可能性があるので一度接続を切ってリセットしてから作業を続けること。

簡単な解決法ですが、ちょっと怖いブツです。

こいつはujisなのだと念押しする

クライアントの文字コードはそのままに、処理対象の文字列のみCONVERTでujisにしてしまいます。

SELECT
	CONCAT( CONVERT( 文字列1 USING ujis ), CONVERT( 文字列2 USING ujis ) )
FROM
	対象テーブル

ちょっと面倒ですが、こっちならPHPでも使えますし、いちいち接続を切ってリセットする必要もありません。

まとめ

・サーバーとクライアントの文字コードが異なっているときに返されるエラー。
・SET NAMESやCONVERTで解決できる。SET NAMESの方はより簡単だけどリスクがあるので注意。

参考サイト


@tmtms のメモ[MySQL の "Illegal mix of collations" エラーについて]
Qiita[mysqlで文字コードをutf8にセットする]
MySQL 5.6 リファレンスマニュアル[10.1.4 接続文字セットおよび照合順序]
docs.microsoft.com[CAST および CONVERT (Transact-SQL)]

この記事は役に立ちましたか?
  • _(:3」∠)_ 面白かった (0)
  • (`・ω・´) 役に立った (0)
  • (・∀・) 参考になった (2)