どもです。
MySQLにて、テストサイトでは問題のなかった
SELECT
CONCAT(文字列1, 文字列2)
FROM
対象テーブル
CONCATが、本番サイトのときだけ
なんてエラーを吐きおってくださりました。
意味は「文字コード違うからCONCATできねーぞ？」。
カラムごとに文字コードを変更する例はありますが、
今回の場合、双方とも文字コードを特別設定していません。
では、何の文字コードが違っているのか？
答えは「サーバーとクライアント」です。
サーバーとクライアント
まず、MySQLの状態は
で確認します。
環境や使用ソフトによって様々ですが、だいたいこんな返事が来ます。
+--------------------------+--------+
| 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)]