【MySQL】#1271 – Illegal mix of collations for operation ‘concat’

Tweet

Category MySQL 2017/12/13

問題

MySQLで以下のようなSQLを実行したら、エラーになりました。

SELECT
    CONCAT(first_name, if(first_name, ' ',''), last_name)
from
    staff;

#1271 - Illegal mix of collations for operation 'concat'

どうすると実行できるでしょうか。

答え

接続のcharsteと対象のカラムのcollationの設定が合っていないと発生することがある。

例）以下のような接続状況の時

mysql> show variables like 'character_set_%';
 +--------------------------+-------+
 | Variable_name            | Value |
 +--------------------------+-------+
 | character_set_client     | utf8  |
 | character_set_connection | utf8  |
 | character_set_database   | ujis  |
 | character_set_results    | utf8  |
 | character_set_server     | ujis  |
 | character_set_system     | utf8  |
 +--------------------------+-------+

データベース、テーブル、カラムはujis設定で、

クライアントはutf8設定だと、

細かくエラーを教えてくれる環境だと以下のようなエラーになる。

ERROR 1270 (HY000): Illegal mix of collations (ujis_japanese_ci,IMPLICIT), (utf8_general_ci,COERCIBLE), (ujis_japanese_ci,IMPLICIT) for operation 'concat'

カラムの値がujis_japanese_ci、ifの結果はutf8_general_ciとなっているため発生しているようです。

対応方法

カラムのcollationとクライアントの文字コード設定を一致させると、エラーにならなくなる。

クライアントからなら set names などで対応できる。

mysql> set names ujis;

mysql> show variables like 'character_set_%';
 +--------------------------+-------+
 | Variable_name            | Value |
 +--------------------------+-------+
 | character_set_client     | ujis  |
 | character_set_connection | ujis  |
 | character_set_database   | ujis  |
 | character_set_results    | ujis  |
 | character_set_server     | ujis  |
 | character_set_system     | utf8  |
 +--------------------------+-------+

以下のように、キャストしてもよい。

CONCAT(first_name, convert(if(first_name, ' ','') using ujis), last_name)

文字コードが揃えばOK。