MySQLに日本語でINSERTできない場合
MySQLは幾度もインストールしているが、毎回文字コードの設定では悩まされる。忘れてしまっていることもあるが、本質的に理解できていないので、毎回文字化けなどを起こしてしまうのだ。昨日も作成したデータベースに日本語をINSERTしようとすると以下のようなエラーが出た。対応のメモを残しておく。
単に以下のようなINSERT文をJavaからexecuteUpdate()で実行しただけだ。同じことをMySQLコンソールからやった場合にはうまくいった。
Incorrect string value: '\xE3\x81\x95\xE3\x81\x82...'
for column 'NAME' at row 1 at com.mysql.jdbc
以下のように対処した。まず、MySQLコンソールからcharacter_setをチェックしてみた。
$ mysql -uroot -p***
> use msql
> create database test;
> flush privileges;
> use test
> create table DUMMY (
ID varchar(10) primary key,
NAME varchar(20) not null
);
> insert into DUMMY (ID, NAME) values ('001', 'たこはち');
character_set_databaseなどがlatin1になっているのでこれをsjisにする必要がある。MySQLのインストール時にデフォルトのままだとlatin1(もしくはutf8)になる。スタートメニュー経由などでMySQL Server Instance Configuration Wizardを起動して、default character setの設定画面まで進める。このWizardは、以前、「MySQL 5.1.37のインストールで失敗」でも書いたように、何度も実行したことのあるMySQLInstanceConfig.exeだ。そこでデフォルトでlatin1になっているのをsjisに変更する。
$ mysql -uroot -p***
> use mysql
> show variables like 'char%';
+--------------------------+--------+
| 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 |
+--------------------------+--------+
また、MySQLのインストール先にあるmy.iniファイルを開いてみて、default-character-set=latin1(もしくはutf8)となっていたら、それもsjisに変更する。Wizardを実行するとmy.iniファイルが更新されるが、必ずしもdefault-character-setは自動変更されないようなので、手作業で修正しておく。
MySQLサービスを再起動後に、再度、MySQLコンソールからcharacter_setをチェックしてみる。
[mysql]
default-character-set=sjis[mysqld]
default-character-set=sjis
しかし、既存の作成済みのデータベースのcharacter_set_databaseは変わらないことに注意する必要がある。これはデータベースをdropして再作成するか、setコマンドで変更してやる必要がある。alter database文で文字コードを指定して変更することも、勿論可能だ。
$ mysql -uroot -p***
> use mysql
> show variables like 'char%';
+--------------------------+--------+
| character_set_client | sjis |
| character_set_connection | sjis |
| character_set_database | sjis |
| character_set_filesystem | binary |
| character_set_results | sjis |
| character_set_server | sjis |
| character_set_system | utf8 |
+--------------------------+--------+
setコマンドでの変更も一時的には有効だ。しかし恒久的なものではなく、MySQLサービスが再起動されると元に戻ってしまう。以前には、サービス起動の引数でcharacter_setを指定したこともあるが、今回はやめておく。一番いいのは、作成済みのデータベースを一旦drop databaseして新たにcreate databaseする方法だろう。まだ実データがない開発段階では、それが望ましい。新規に作成したデータベースのcharacter_set_databaseにはsjisが適用されるようになる。
> use test
> show variables like 'char%';
+--------------------------+--------+
| character_set_client | sjis |
| character_set_connection | sjis |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | sjis |
| character_set_server | sjis |
| character_set_system | utf8 |
+--------------------------+--------+
> set character_set_database = sjis;
> show variables like 'char%';
+--------------------------+--------+
| character_set_client | sjis |
| character_set_connection | sjis |
| character_set_database | sjis |
| character_set_filesystem | binary |
| character_set_results | sjis |
| character_set_server | sjis |
| character_set_system | utf8 |
+--------------------------+--------+
ちなみにJavaでMySQLからデータベースに接続するときは、勿論、以下の感じでcharacterEncodingにSJISを指定しておくこと。
> use test
> show variables like 'char%';
+--------------------------+--------+
| character_set_client | sjis |
| character_set_connection | sjis |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | sjis |
| character_set_server | sjis |
| character_set_system | utf8 |
+--------------------------+--------+
> drop database test;
> create database test;
> use test
> show variables like 'char%';
+--------------------------+--------+
| character_set_client | sjis |
| character_set_connection | sjis |
| character_set_database | sjis |
| character_set_filesystem | binary |
| character_set_results | sjis |
| character_set_server | sjis |
| character_set_system | utf8 |
+--------------------------+--------+
[2010/02/02追記]
try {
DbUtils.loadDriver("com.mysql.jdbc.Driver");
} catch (Exception ex) {
;
}
// String url = "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=SJIS";
String url = "jdbc:mysql://localhost/" + "test"; // database名がtestの場合
Properties props = new Properties();
props.put("user", "root"); // 任意
props.put("password", "***"); // 任意
props.put("useUnicode", "true"); // これが必要
props.put("characterEncoding", "SJIS"); // これが必要
try {
this.conn = DriverManager.getConnection(url, props);
} catch (SQLException ex){
;
}
文字コード関連の変数についての情報があったので追記しておく。文字コードはcreate database文やalter database文でが指定でき、databaseごとに使い分けが行える。character_set_database, character_set_client, character_set_connection, character_set_resultsは同じ文字コードに設定しておくことが無難そうだ。
| 変数 | 内容 |
|---|---|
| character_set_system | システムがテーブル名やカラム名などの登録する際に使用する文字コード。utf8で固定されている。 |
| character_set_server | character_set_databaseのデフォルト値となる文字コード。 |
| character_set_database | create database文で文字コードが指定されなかった場合、作成されたdatabaseの文字コードはこれになる。character_set_connectionのデフォルト値になる。 |
| character_set_client | サーバがクライアントから受け取るSQL文の文字コード。 |
| character_set_connection | クライアントから受け取ったSQL文をサーバが、この文字コードに変換して取り込む。通常は、character_set_client,character_set_connection,character_set_resultsは同じ文字コードにする。 |
| character_set_results | サーバがクライアントに返す結果の文字コード。この変数をnullに設定すると、結果に対する文字コード変換は行われない。 |
