SET NAMES のいらない生活

PHPとRubyのMySQLクライアントライブラリを清く正しく使う方法*1をまとめてみた。Shift_JISが来てもU+00A5が来ても大丈夫なはず。
汚れた部分を見つけた方はぜひご指摘を！

Version

• MySQL Server (CentOS 5.2)
  • mysql-server-5.0.45-7.el5
• MySQL Client (Ubuntu 8.04)
  • libmysqlclient15off 5.0.51a-3ubuntu5.4
  • PHP 5.2.4-2ubuntu5.4 with Suhosin-Patch 0.9.6.2 (cli) (built: Nov 25 2008 22:10:31)
  • ruby 1.8.6 (2007-09-24 patchlevel 111) [i486-linux]
  • libmysql-ruby 2.7.4-1

PHP

mysql

$link = mysql_connect('localhost', 'user', 'password');
mysql_select_db('test', $link);
mysql_set_charset('utf8', $link);

$result = mysql_query(
    sprintf("SELECT id FROM tb WHERE name = '%s'",
            mysql_real_escape_string('あいうえお'))
);

if ($result) {
    while ($row = mysql_fetch_assoc($result)) {
        printf("%d\n", $row['id']);
    }

    mysql_free_result($result);
}

mysql_close($link);

mysqli

OOP:

$mysqli = new mysqli('localhost', 'user', 'password', 'test');
$mysqli->set_charset('utf8');

$stmt = $mysqli->prepare("SELECT id FROM tb WHERE name = ?");
$name = 'あいうえお';
$stmt->bind_param('s', $name);
$stmt->bind_result($id);

$stmt->execute();

while ($stmt->fetch()) {
    printf("%d\n", $id);
}
$stmt->close();

$mysqli->close();

手続き型:

$link = mysqli_connect('localhost', 'user', 'password', 'test');
mysqli_set_charset($link, 'utf8');

$stmt = mysqli_prepare($link, "SELECT id FROM tb WHERE name = ?");
$name = 'あいうえお';
mysqli_stmt_bind_param($stmt, 's', $name);
mysqli_stmt_bind_result($stmt, $id);

mysqli_stmt_execute($stmt);

while (mysqli_stmt_fetch($stmt)) {
    printf("%d\n", $id);
}
mysqli_stmt_close($stmt);

mysqli_close($link);

非prepared statements:

$mysqli = new mysqli('localhost', 'user', 'password', 'test');
$mysqli->set_charset('utf8');

$result = $mysqli->query(
    sprintf("SELECT id FROM tb WHERE name = '%s'",
            $mysqli->real_escape_string('あいうえお'))
);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        printf("%d\n", $row['id']);
    }
    
    $result->close();
}

$mysqli->close();

PDO_MYSQL

$dbh = new PDO('mysql:host=localhost;dbname=test', 'user', 'password',
               array(
                     PDO::MYSQL_ATTR_READ_DEFAULT_FILE => '/etc/my.cnf',
                     PDO::MYSQL_ATTR_READ_DEFAULT_GROUP => 'pdo',
                     PDO::MYSQL_ATTR_DIRECT_QUERY => false  /* server-side prepared statements */
               ));
 
$stmt = $dbh->prepare("SELECT id FROM tb WHERE name = ?");
$stmt->bindValue(1, 'あいうえお', PDO::PARAM_STR);

$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    printf("%d\n", $row['id']);
}

非prepared statements:

$dbh = new PDO('mysql:host=localhost;dbname=test', 'user', 'password',
               array(
                     PDO::MYSQL_ATTR_READ_DEFAULT_FILE => '/etc/my.cnf',
                     PDO::MYSQL_ATTR_READ_DEFAULT_GROUP => 'pdo',
                     PDO::MYSQL_ATTR_DIRECT_QUERY => true
               ));
 
$result = $dbh->query(sprintf("SELECT id FROM tb WHERE name = %s",
                              $dbh->quote('あいうえお', PDO::PARAM_STR)
));

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    printf("%d\n", $row['id']);
}

/etc/my.cnf

[pdo]
default-character-set = utf8

Ruby

MySQL/Ruby

my = Mysql.init
my.options(Mysql::SET_CHARSET_NAME, "utf8")
my.connect("localhost", "user", "password", "test")

st = my.prepare("SELECT id FROM tb WHERE name = ?")
st.execute("あいうえお")
st.each do |x|
  p x[0]
end
st.close

my.close

非prepared statements:

my = Mysql.init
my.options(Mysql::SET_CHARSET_NAME, "utf8")
my.connect("localhost", "user", "password", "test")

my.query(sprintf("SELECT id FROM tb WHERE name = '%s'",
         my.quote("あいうえお"))).each do |x|
  p x[0]
end

my.close

*1:対SQLインジェクションな意味で