php - 是否使用 MySQL 中的当前/默认字符集转换 BLOB？

Question

1. 我有一个包含 BLOB 字段的表。
2. 表格的字符集是 Latin1。
3. 我连接到数据库并“SET CHARACTER SET utf8”。
4. 然后我将二进制数据保存到字段中。
5. 然后我检索数据，发现它不是我保存的(损坏的)。

代码:

<?php
$pdo = new \PDO("mysql:host=127.0.0.1;dbname=***", '***', '***');

$pdo->exec('SET CHARACTER SET utf8');

$sql = "INSERT INTO pdo_blob (the_blob) VALUES(:the_blob)";
$insertStm = $pdo->prepare($sql);

$blob = (binary) file_get_contents('/home/***/test.pdf');
$insertStm->bindParam(":the_blob", $blob, \PDO::PARAM_LOB);
$insertStm->execute();

$selectStm = $pdo->prepare("SELECT the_blob FROM pdo_blob ORDER BY id DESC LIMIT 1");
$selectStm->execute();

$savedBlob = null;
$selectStm->bindColumn(1, $savedBlob, \PDO::PARAM_LOB);
$selectStm->fetch();

echo 'equal: ' . ((int) ($blob == $savedBlob));

Answer 1

简答:

只需删除或注释掉下面的行，它就会始终有效，无论实际使用的是哪种数据库编码(utf8、latin1 等):

$pdo->exec('SET CHARACTER SET utf8');

长答案:

这不是 PDO 错误，这是 MySQL 错误。

当实际的数据库编码是latin1，但是你使用:

SET CHARACTER SET utf8

(反之亦然:实际是 utf8，但您使用 latin1 - 重要的部分是它不同)，然后，作为据我所知，MySQL 将尝试对客户端和服务器之间的所有流量执行字符集转换(即使是 BLOB!)。

如果您不使用 SET CHARACTER SET 语句，从我看到的脚本(PHP/PDO 或 Perl/DBI)默认情况下连接字符集设置为数据库字符集，并且在那在没有隐式转换发生的情况下。

显然，这种自动转换会杀死 BLOB，因为它不希望发生任何转换。

我已经在 PHP/PDO 和 Perl/DBI 上对此进行了测试，问题很容易重现:如果使用带有 latin1 编码的数据库并使用 SET CHARACTER SET utf8 (反之亦然)。

如果你想完全兼容UTF8，你应该改变你的数据库编码:

ALTER DATABASE mydb CHARSET utf8;

有了这个，一切都将使用 UTF8，BLOB 也可以正常工作。

导致此损坏问题的最小文件是具有单字节 0xFF 的 blob.bin。在 Linux 上，您可以使用 printf 命令创建此测试文件:

printf "0xFF" > blob.bin

现在，测试重现问题的脚本:

PHP测试代码:

<?php
$dbh = new PDO("mysql:host=127.0.0.1;dbname=test");
# If database encoding is NOT utf8, uncomment to break it:
# $dbh->exec("SET CHARACTER SET utf8");

$blob1 = file_get_contents("blob.bin");
$sth = $dbh->prepare(
    "INSERT INTO pdo_blob (the_blob) VALUES(:the_blob)"
);
$sth->bindParam(":the_blob", $blob1, PDO::PARAM_LOB);
$sth->execute();

$sth = $dbh->prepare(
    "SELECT the_blob FROM pdo_blob ORDER BY id DESC LIMIT 1"
);
$sth->execute();

$blob2 = null;
$sth->bindColumn(1, $blob2, PDO::PARAM_LOB);
$sth->fetch();

if ($blob1 == $blob2) {
    echo "Equal\n";
} else {
    echo "Not equal\n";
    $arr1 = str_split($blob1);
    $arr2 = str_split($blob2);
    $i=0;
    for ($i=0; $i<count($arr1); $i++) {
        if ($arr1[$i] != $arr2[$i]) {
            echo "First diff: " . dechex(ord($arr1[$i])) . " != "
                                . dechex(ord($arr2[$i])) . "\n";
            break;
        }
    }
}
?>

Perl 测试代码:

#!/usr/bin/perl -w

use strict;
use DBI qw(:sql_types);

my $dbh = DBI->connect("dbi:mysql:host=127.0.0.1;dbname=test");
# If database encoding is NOT utf8, uncomment to break it:
# $dbh->do("SET CHARACTER SET utf8");
open FILE, "blob.bin";
binmode FILE;
read(FILE, my $blob1, 100000000);
close FILE;
my $sth = $dbh->prepare(
    "INSERT INTO pdo_blob (the_blob) VALUES(?)"
);
$sth->bind_param(1, $blob1, SQL_BLOB);
$sth->execute();
my ($blob2) = $dbh->selectrow_array(
    "SELECT the_blob FROM pdo_blob ORDER BY id DESC LIMIT 1"
);
print ($blob1 eq $blob2 ? "Equal" : "Not equal") , "\n";