是否使用 MySQL 中的当前/默认字符集转换了 BLOB? [英] Is a BLOB converted using the current/default charset in MySQL?

问题描述

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

推荐答案

简答:

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

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

长答案:

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

Long Answer:

This is not PDO bug, this is MySQL bug.

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

When actual database encoding is latin1, but you use:

SET CHARACTER SET utf8

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

(or vice versa: actual is utf8, but you use latin1 - important part is that it is different), then, as far as I can tell, MySQL will try to perform charset conversion for all traffic between client and server (even for BLOB!).

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

If you DO NOT use SET CHARACTER SET statement, from what I see for scripts (PHP/PDO or Perl/DBI) connection charset by default is set to be the database charset, and in that case no implicit conversion takes place.

显然，这种自动转换会杀死不希望发生任何转换的 BLOB.

Obviously, this automatic conversion is what kills BLOBs, which do not want any conversion to happen.

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

I have tested this on both PHP/PDO and Perl/DBI, and issue is easily reproducible: both will fail if using database with latin1 encoding and using SET CHARACTER SET utf8 (or vice versa).

如果您想要完全UTF8 兼容，您应该使用以下方法更改数据库的编码:

If you want to be fully UTF8 compatible, you should change encoding of your database using:

ALTER DATABASE mydb CHARSET utf8;

有了这个，一切都将使用 UTF8，而且 BLOB 也能正常工作.

With this, everything will be using UTF8, and BLOBs will also work fine.

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

Minimal file that causes this corruption problem is blob.bin with single byte 0xFF. On Linux, you can create this test file using printf command:

printf "0xFF" > blob.bin

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

Now, test scripts that reproduce the problem:

<?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
";
} else {
    echo "Not equal
";
    $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])) . "
";
            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") , "
";

这篇关于是否使用 MySQL 中的当前/默认字符集转换了 BLOB?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！