PHP / PDO / MySQL：插入MEDIUMBLOB存储坏数据(PHP/PDO/MySQL: inserting into MEDIUMBLOB stores bad data)

 我有一个简单的PHP Web应用程序，它通过文件上传接受图标图像并将它们存储在MEDIUMBLOB列中。 
 
 在我的机器（Windows）和两个Linux服务器上，这很好用。 在第三台Linux服务器上，插入的映像已损坏：SELECT后不可读，并且MySQL length（）函数报告的列数据长度比上载文件的大小大约40％。 
 
 （每个服务器连接到一个单独的MySQL实例。） 
 
 当然，这让我想到编码和字符集问题。 BLOB列没有关联的字符集，因此看起来最可能的罪魁祸首是PDO及其对该列的参数值的解释。 
 
 
 
 我尝试将bindValue与PDO :: PARAM_LOB一起使用，没有任何效果。 
 
 
 我已经验证了正确地在服务器上接收图像（即在上传后没有问题地阅读它们），所以它肯定是DB / PDO问题。 
 
 
 我已经搜索了服务器之间明显的配置差​​异，但我不是PHP配置方面的专家，所以我可能错过了一些东西。 
 
 
 插入代码几乎如下： 
 
$imagedata = file_get_contents($_FILES["icon"]["tmp_name"]);
$stmt = $pdo->prepare('insert into foo (theimage) values (:theimage)');
$stmt->bindValue(':theimage', $imagedata, PDO::PARAM_LOB);
$stmt->execute();
 
 任何帮助将非常感激。 
 
 更新 ：有问题的服务器上的默认MySQL字符集是utf8; 它是其他人的拉丁文。 
 
 通过将PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES latin1 COLLATE latin1_general_ci"到PDO构造函数来“解决”该问题。 
 
 这对我来说似乎是 
 
   一个 
 糟糕的设计：为什么连接的字符集会对二进制列的数据产生任何影响，特别是当它被PARAM_LOB识别为PDO本身的二进制时？ 
 
 请注意，在所有情况下，DB表都定义为latin1：它只是服务器的默认字符集不一致。 

I have a simple PHP web app that accepts icon images via file upload and stores them in a MEDIUMBLOB column. 
 
On my machine (Windows) plus two Linux servers, this works fine. On a third Linux server, the inserted image is corrupted: unreadable after a SELECT, and the length of the column data as reported by the MySQL length() function is about 40% larger than the size of the uploaded file. 
 
(Each server connects to a separate instance of MySQL.)
 
Of course, this leads me to think about encoding and character set issues. BLOB columns have no associated charsets, so it seems like the most likely culprit is PDO and its interpretation of the parameter value for that column. 
 
 
 
I've tried using bindValue with PDO::PARAM_LOB, to no effect.
 
 
I've verified that the images are being received on the server correctly (i.e. am reading them post-upload with no problem), so it's definitely a DB/PDO issue. 
 
 
I've searched for obvious configuration differences between the servers, but I'm not an expert in PHP configuration so I might have missed something.
 
 
The insert code is pretty much as follows:
 
$imagedata = file_get_contents($_FILES["icon"]["tmp_name"]);
$stmt = $pdo->prepare('insert into foo (theimage) values (:theimage)');
$stmt->bindValue(':theimage', $imagedata, PDO::PARAM_LOB);
$stmt->execute();
 
Any help will be really appreciated.
 
UPDATE: The default MySQL charset on the problematic server is utf8; it's latin1 on the others. 
 
The problem is "solved" by adding PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES latin1 COLLATE latin1_general_ci" to the PDO constructor. 
 
This seems like 
 
   a bug 
  poor design to me: why should the charset of the connection have any effect on data for a binary column, particularly when it's been identified as binary to PDO itself with PARAM_LOB?
 
Note that the DB tables are defined as latin1 in all cases: it's only the servers' default charsets that are inconsistent. 

原文：https://stackoverflow.com/questions/6346319