MySQL에 일본어 insert문이 작동되지 않는경우 ; mysql의 언코딩타입 변경하기

MySQLに日本語でINSERTできない場合

1. テーブル単位でEncodingタイプ設定

CREATE TABLE INSURANCE_RECOMM_TB (
id VARCHAR(10) PRIMARY KEY,
age int(5) NOT NULL,
sex VARCHAR(5) NOT NULL,
marriage VARCHAR(5) NOT NULL,
job VARCHAR(10) NOT NULL,
child VARCHAR(5) NOT NULL,
d_insurance VARCHAR(500),
c_insurance VARCHAR(500),
insurance_reason VARCHAR(500),
url VARCHAR(500)
)
ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

２．Mysql設定でEncodingタイプ設定

MySQLは幾度もインストールしているが、毎回文字コードの設定では悩まされる。忘れてしまっていることもあるが、本質的に理解できていないので、毎回文字化けなどを起こしてしまうのだ。昨日も作成したデータベースに日本語をINSERTしようとすると以下のようなエラーが出た。対応のメモを残しておく。

    Incorrect string value: '\xE3\x81\x95\xE3\x81\x82...' 
    for column 'NAME' at row 1 at com.mysql.jdbc

単に以下のようなINSERT文をJavaからexecuteUpdate()で実行しただけだ。同じことをMySQLコンソールからやった場合にはうまくいった。

    $ 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', 'たこはち');

以下のように対処した。まず、MySQLコンソールからcharacter_setをチェックしてみた。

    $ 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   |
    +--------------------------+--------+

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のインストール先にあるmy.iniファイルを開いてみて、default-character-set=latin1(もしくはutf8)となっていたら、それもsjisに変更する。Wizardを実行するとmy.iniファイルが更新されるが、必ずしもdefault-character-setは自動変更されないようなので、手作業で修正しておく。

    [mysql]
    default-character-set=sjis

    [mysqld]
    default-character-set=sjis

MySQLサービスを再起動後に、再度、MySQLコンソールからcharacter_setをチェックしてみる。

    $ 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   |
    +--------------------------+--------+

しかし、既存の作成済みのデータベースのcharacter_set_databaseは変わらないことに注意する必要がある。これはデータベースをdropして再作成するか、setコマンドで変更してやる必要がある。alter database文で文字コードを指定して変更することも、勿論可能だ。

    > 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   |
    +--------------------------+--------+

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   |
    +--------------------------+--------+
    > 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   |
    +--------------------------+--------+

ちなみにJavaでMySQLからデータベースに接続するときは、勿論、以下の感じでcharacterEncodingにSJISを指定しておくこと。

    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){
        ;
    }

[2010/02/02追記]

文字コード関連の変数についての情報があったので追記しておく。文字コードは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に設定すると、結果に対する文字コード変換は行われない。

Permalink | コメント(1) | トラックバック(0)