Perl + MySQL で cp932 を使っている際、'表' 等のように 0x5C が含まれる文字を INSERT するとエラーになる場合の対処方法

mysql perl

悲しい大人の事情で、MySQL に cp932 のデータを保存する事になったので、そのメモ。

環境

CREATE DATABASE foo CHARACTER SET cp932;
GRANT SELECT, DELETE, UPDATE, INSERT ON foo.* TO bar@localhost IDENTIFIED BY 'baz';

CREATE TABLE zip_codes(
  id          INTEGER      AUTO_INCREMENT,
  zip_code    CHAR(7)      NOT NULL,
  state_id    INTEGER      NOT NULL,
  city        VARCHAR(256) NOT NULL,
  street      VARCHAR(256),
  office      VARCHAR(256),
  modify_date TIMESTAMP,

  PRIMARY KEY (id),
          KEY (zip_code),
          KEY (state_id)
) TYPE=InnoDB;

相談された際の my.cnf の内容

$ cat /etc/my.cnf
[mysqld]
#..snip..
default-character-set=cp932
skip-character-set-client-handshake
#..snip..

渡されたエラーの再現コード

$ cat ./test.csv
5180801,24,"伊賀市","平野見能"
$ cat ./test.pl
use strict;
use warnings;

use Carp;
use FindBin qw($Bin);
use Path::Class;
use Text::CSV_XS;
use DBI;

my $csv = Text::CSV_XS->new({binary=>1});
my $fh = file($Bin, 'test.csv')->openr or croak $!;
my $dbh = DBI->connect('DBI:mysql:foo', 'bar', 'baz');
my $sth = $dbh->prepare('insert into zip_codes (zip_code, state_id, city, street) value (?, ?, ?, ?)');

while(my $line = $fh->getline) {
    $line =~ s/\n$//;
    $csv->parse($line) or croak $line;
    my @fields = $csv->fields;
    $sth->execute(@fields);
}

$ perl test.pl
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''平野見能\')' at line 1 at testz.pl line 19, <GEN0> line 1.

修正してみた

まずは、DBD::mysql が default-character-set を利用出来ていない気がしたので my.cnf を下記のように修正してみた。

$ cat /etc/my.cnf
[mysqld]
#..snip..
default-character-set=cp932
# skip-character-set-client-handshake
#..snip..

[client]
default-character-set=cp932

これで INSERT ができるようになった！と思ったら・・・文字化けた状態で INSERT されていた orz
とりあえず perl 側で my.cnf を指定してみる。

my $dbh = DBI->connect('DBI:mysql:foo;mysql_read_default_file=/etc/my.cnf', 'bar', 'baz');

これで正しく文字化けせずに INSERT できるようになった。

参考 URI