【SQLiteからMySQLへの移行３】 MySQL側での文字化けの解消
前回「【SQLiteからMySQLへの移行２】 SQLiteダンプしたSQLのMySQLでの実行」では、SQLiteでダンプしたデータをMySQLへ投入したが、文字化けしたところまで確認して終わった。
今回はこの原因を突き止めて、解決方法を探る。
１．文字コードの確認
そういえば、character_set_databaseをsjisに変更していたのを忘れていたｗ。
SQLiteはutfがデフォルトでsjisを許容していないので、文字化けの原因は恐らくこれだろう。
mysql> show variables like 'char%'; +--------------------------+--------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | sjis | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\dev\mysql55\share\charsets\ | +--------------------------+--------------------------------+ 8 rows in set (0.00 sec)
ここを参考にすると、次のようにある。
色々な値が表示されますが、特に関係があるのは次の値です。
character_set_client utf8 クライアントが送信する文字コード
character_set_connection utf8 文字コード情報が無い文字列の文字コード
character_set_results utf8 クライアントへ送信する文字コード
character_set_server utf8 サーバのデフォルト文字コード
２．文字コードを変更する
mysql> alter database test character set utf8; Query OK, 1 row affected (0.07 sec) mysql> show variables like 'char%'; +--------------------------+--------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\dev\mysql55\share\charsets\ | +--------------------------+--------------------------------+ 8 rows in set (0.00 sec)
３．データを再投入してみる
あれれ、文字化けが直らない・・・。
mysql> delete from historicaldate; Query OK, 1 row affected (0.05 sec) mysql> source C:\dev\SQLite\EventDateDump.sql ERROR 1050 (42S01): Table 'historicaldate' already exists Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> select * from historicaldate \G; *************************** 1. row *************************** id: 1 nen: 2007 tuki: 9 niti: 17 zi: -1 fun: 0 kuni: JP event: 莨大ｴ・域噴閠√・譌･・ lank: 0 mae: NULL yoso: NULL keka: NULL 1 row in set (0.00 sec)
４．データベースのcharset以外に、テーブルのcharsetも変更する
そういえば、テーブルのcharsetも変更したのだったｗ・・・。
まずは、テーブルのcharsetの確認。
mysql> show create table Historicaldate \G; *************************** 1. row *************************** Table: Historicaldate Create Table: CREATE TABLE `historicaldate` ( `id` int(11) NOT NULL, `nen` int(11) DEFAULT NULL, `tuki` int(11) DEFAULT NULL, `niti` int(11) DEFAULT NULL, `zi` int(11) DEFAULT NULL, `fun` int(11) DEFAULT NULL, `kuni` text, `event` text, `lank` int(11) DEFAULT NULL, `mae` text, `yoso` text, `keka` text, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=sjis 1 row in set (0.00 sec)
DEFAULT CHARSET=sjisとあるので、utf8に戻す。
mysql> alter table Historicaldate character set utf8; Query OK, 1 row affected (0.37 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show create table Historicaldate \G; *************************** 1. row *************************** Table: Historicaldate Create Table: CREATE TABLE `historicaldate` ( `id` int(11) NOT NULL, `nen` int(11) DEFAULT NULL, `tuki` int(11) DEFAULT NULL, `niti` int(11) DEFAULT NULL, `zi` int(11) DEFAULT NULL, `fun` int(11) DEFAULT NULL, `kuni` text CHARACTER SET sjis, `event` text CHARACTER SET sjis, `lank` int(11) DEFAULT NULL, `mae` text CHARACTER SET sjis, `yoso` text CHARACTER SET sjis, `keka` text CHARACTER SET sjis, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
５．それでも文字化けが直らない
おかしい。。。SJISで開かれている。。。なんでだろう。
mysql> select * from historicaldate \G; *************************** 1. row *************************** id: 1 nen: 2007 tuki: 9 niti: 17 zi: -1 fun: 0 kuni: JP event: 莨大ｴ・域噴閠√・譌･・ lank: 0 mae: NULL yoso: NULL keka: NULL 1 row in set (0.00 sec)
コマンドラインに直接貼り付けて実行してみた。
mysql> INSERT INTO HistoricalDate VALUES(1,2007,9,17,-1,0,"JP","休場（敬老の日） ",0,NULL,NULL,NULL); ERROR 1366 (HY000): Incorrect string value: '\x8Bx\x8F\xEA\x81i...' for column ' event' at row 1
これはクライアントの文字コードがutf8に設定されているのに対して、入力文字がshift-jisだからか、返却結果をutf8で解釈しているからだろう。
-- クライアントの文字コードをshift-jisに変更 mysql> set character_set_client=sjis; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO HistoricalDate VALUES(1,2007,9,17,-1,0,"JP","休場（敬老の日） ",0,NULL,NULL,NULL); Query OK, 1 row affected (0.00 sec) mysql> select * from historicaldate \G; *************************** 1. row *************************** id: 1 nen: 2007 tuki: 9 niti: 17 zi: -1 fun: 0 kuni: JP event: 莨大ｴ・域噴閠√・譌･・ lank: 0 mae: NULL yoso: NULL keka: NULL 1 row in set (0.00 sec)
クライアント側の文字コードをshift-jisに変更するだけでは文字化け。
そのため、出力結果表示時の文字コードもshift-jisに変更する。
mysql> set character_set_results=sjis; Query OK, 0 rows affected (0.00 sec) mysql> select * from historicaldate \G; *************************** 1. row *************************** id: 1 nen: 2007 tuki: 9 niti: 17 zi: -1 fun: 0 kuni: JP event: 休場（敬老の日） lank: 0 mae: NULL yoso: NULL keka: NULL 1 row in set (0.00 sec)
コマンドラインに直接SQLを打ち込んだ場合の文字化けはこれで解消。
これでも、ファイルからSQLを実行した場合は、文字化けが解消しない。
ファイルの文字コードをshift-jisにしたり、utf8にしたりと試したのだが上手くいかない。何でだろう。
mysql> source C:\dev\SQLite\EventDateDump.sql ERROR 1050 (42S01): Table 'historicaldate' already exists Query OK, 1 row affected (0.04 sec) Query OK, 0 rows affected (0.00 sec) mysql> select * from historicaldate \G; *************************** 1. row *************************** id: 1 nen: 2007 tuki: 9 niti: 17 zi: -1 fun: 0 kuni: JP event: 莨大?ｴ?域噴閠√?譌･? lank: 0 mae: NULL yoso: NULL keka: NULL 1 row in set (0.00 sec)
原因分からずで、いったん入力となるSQLをSJISで作成して保存して実行した。
DB側の文字コードも全てSJISで実行・・・。
mysql> select * from historicaldate where id between 1 and 10 \G; *************************** 1. row *************************** id: 1 nen: 2007 tuki: 9 niti: 17 zi: -1 fun: 0 kuni: JP event: 休場（敬老の日） lank: 0 mae: NULL yoso: NULL keka: NULL *************************** 2. row *************************** id: 2 nen: 2007 tuki: 9 niti: 17 zi: 10 fun: 0 kuni: EU event: 7月貿易収支＜季調前＞ lank: 1 mae: +76億ユーロ yoso: +70億ユーロ keka: +46億ユーロ
