検索結果が正しくない

なぜか「中」を含む文字列を調べようとしているのに、「団」とか「台」が検索されてしまう。
どうやらMySQLをrpmでインストールするとlatin1がデフォルト文字コードセットになってしまっていてそれでマルチバイトの検索がうまくいかないことがあるようだ。
現在の文字コード設定をチェックしてみる。
mysql> show variables like ‘%char%’;
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)
binaryというのは無変換を示している。現在は、入力された文字がlatin1と見なされ、latin1のデータベースに登録されるため、例えばEUCの文字で登録した場合でもデータが破壊されることはない。
データベースのデフォルト文字コードをEUCにするためには、/etc/my.iniに以下のように設定して再起動すればよい。
[mysqld]
default-character-set=ujis
skip-character-set-client-handshake
何もデータがない状態であれば、これでよいのだが、もうすでに運用済みの場合にはこれではデータが破壊されてしまう。（登録済みデータがlatin1なのに変更後はEUCと見なされて処理されるため）
不幸なことに、自分はUTF-8で運用しているデータベースもあれば、EUC-JPで運用しているデータベースもあるため、一括で変換することも難しい。
今回はいろいろ調べた結果、特定のデータベースの文字コードセットだけを変更する方法があったので紹介したい。hogehogeデータベースに対してlatin1からEUC-JPへ変換する方法をご紹介する。
#ダンプ(hogehoge.dmp)を取得する。
mysqldump –default-character-set=binary -u root news –password=XXXX > hogehoge.dmp
#MySQLへログインし、データベースのcharacter_set_databaseを変換する。
alter database hogehoge character set ujis;
#hogehoge.dmp の中のSQL文にcharsetが指定されている箇所があるので、viなどで開きlatin1からujisへ置換しておく。
mysql -u root -p hogehoge –default-character-set=ujis < hogehoge.dmp で取り込む これで正しく取り込めるのだが、問題はクライアントの問い合わせ方法になる。 alter database news character set ujis;　のSQLで変更されているのはcharacter_set_databaseだけであることが分かる。 mysql -u root -p hogehoge mysql> show variables like ‘%char%’;
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)
このままだと、発行されるクエリはlatin1と見なされるが、データベースの内部文字コードはEUCなので、SELECT文はマッチしないだろうし、INSERT文などを発行すればデータが破壊されてしまう。
そこで、以下のSQLを事前に発行すると・・・
mysql> set names ujis;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘%char%’;
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | ujis |
| character_set_connection | ujis |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | ujis |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)
となる。つまり、SET NAMES ujis文によってcharacter_set_client、character_set_connection、character_set_databaseがEUCとなる。ちなみにcharacter_set_serverはデフォルト文字コードセットなので、latin1となっているが特に問題は発生しない。またcharater_set_systemはUTF-8固定でありこちらも処理上は問題はない。
※SET NAMES ujis; とおなじクエリを接続時にパラメータとして渡すこともできる。
mysql -u root -p hogehoge –default-character-set=ujis
mysql> show variables like ‘%char%’;
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | ujis |
| character_set_connection | ujis |
| character_set_database | ujis |
| character_set_filesystem | binary |
| character_set_results | ujis |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)
もしPHPなどのプログラムによってクエリを発行する場合には、データベースへ接続後
set names ujis;
を実行する必要がある。
今回の問題について非常に有益となったサイトを最後にご紹介する。
http://www.mysql.gr.jp/frame/modules/bwiki/index.php?FAQ#fb74bab6
MySQLの文字コードに関するメモ