PerlでSQLiteのUTF8を自動的に扱う

タイトルの日本語に違和感があるが、気にしない方向で。

sqlite_unicode を指定するだけ。

DBD::SQLite - Self-contained RDBMS in a DBI Driver - metacpan.org
https://metacpan.org/pod/DBD::SQLite

sample.pl

use strict;
use warnings;
use DBI;
use utf8;
use open IO => qw/ :encoding(utf8) :std /;

my $dbh = DBI->connect("dbi:SQLite:dbname=test.db");
$dbh->{sqlite_unicode} = 1; # important!
$dbh->{AutoCommit}     = 0;
# initdb($dbh);
$dbh->do(q{CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY, name TEXT)});

$dbh->do(q{INSERT INTO user (name) values ('洲崎西')});
$dbh->commit;

my $sth = $dbh->prepare(q{SELECT * FROM user});
$sth->execute;
while (my $row = $sth->fetch) {
  print "$row->[0] $row->[1]\n";
}
$sth->finish;
$dbh->disconnect;

sub initdb
{
  my ($dbh) = @_;

  my %tbl;
  my $sth = $dbh->prepare(q{SELECT name FROM sqlite_master WHERE type='table'});
  $sth->execute();
  while (my $row = $sth->fetch ) {
    $tbl{$row->[0]} = 1;
  }
  $sth->finish;

  unless (exists $tbl{user}) {
    $dbh->do(q{
      CREATE TABLE user (id INTEGER PRIMARY KEY, name TEXT)
    });
  }
  return 1;
}
__END__

# sqlite_unicode無しでそのまま動かすと化けます。
$ perl test.pl
1 a´2a´?e\?
2 a´2a´?e\?
3 a´2a´?e\?

# sqlite_unicodeを定義してあげるとOK
$ perl test.pl
1 洲崎西
2 洲崎西
3 洲崎西