PHP/PDO/MySQL:插入MEDIUMBLOB会存储错误数据 [英] PHP/PDO/MySQL: inserting into MEDIUMBLOB stores bad data

问题描述

我有一个简单的PHP Web应用程序，该应用程序通过文件上传接受图标图像并将其存储在MEDIUMBLOB列中.

I have a simple PHP web app that accepts icon images via file upload and stores them in a MEDIUMBLOB column.

在我的计算机(Windows)和两个Linux服务器上，这可以正常工作.在第三台Linux服务器上，插入的映像已损坏:执行SELECT后不可读，并且MySQL length()函数报告的列数据长度比上载文件的大小大40％.

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.

(每个服务器都连接到一个单独的MySQL实例.)

(Each server connects to a separate instance of MySQL.)

当然，这使我想到了编码和字符集问题. BLOB列没有关联的字符集，因此似乎最可能的罪魁祸首是PDO及其对该列的参数值的解释.

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.

• 我尝试将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；在其他人身上是拉丁语.

UPDATE: The default MySQL charset on the problematic server is utf8; it's latin1 on the others.

通过将PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES latin1 COLLATE latin1_general_ci"添加到PDO构造函数来解决"问题.

The problem is "solved" by adding PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES latin1 COLLATE latin1_general_ci" to the PDO constructor.

对我来说，这似乎是一个bug 的糟糕设计:为什么 connection 的字符集会对二进制列的数据产生任何影响，特别是当它被标识为用PARAM_LOB二进制化到PDO本身?

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?

请注意，在所有情况下，数据库表都定义为latin1:只有服务器的默认字符集不一致.

Note that the DB tables are defined as latin1 in all cases: it's only the servers' default charsets that are inconsistent.

推荐答案

对我来说，这似乎是个错误:为什么连接的字符集对二进制列的数据有任何影响，特别是当使用PARAM_LOB将其识别为PDO本身的二进制文件时?

This seems like a bug 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?

我不认为这一定是bug.我可以想象，每当客户端与服务器对话并说以下命令位于UTF-8中，而服务器需要使用Latin-1时，则查询可能会在解析和执行之前得到重新编码.因此，这是数据传输的编码问题.由于整个查询之前的解析将受到此重新编码的影响，因此BLOB列的二进制数据也将发生更改.

I do not think that this must be a bug. I can imagine that whenever the client talks with the server and says that the following command is in UTF-8 and the server needs it in Latin-1, then the query might get re-encoded prior parsing and execution. So this is an encoding issue for the transportation of the data. As the whole query prior parsing will get influenced by this re-encoding, the binary data for the BLOB column will get changed as well.

摘自Mysql手册:

服务器在收到语句后应将其转换为什么字符集?

为此，服务器使用character_set_connection和collat​​ion_connection系统变量.它将客户端发送的语句从character_set_client转换为character_set_connection(具有诸如_latin1或_utf8之类的介绍符的字符串文字除外). collat​​ion_connection对于比较文字字符串很重要.对于将字符串与列值进行比较，collat​​ion_connection无关紧要，因为列具有自己的排序规则，排序规则优先级更高.

For this, the server uses the character_set_connection and collation_connection system variables. It converts statements sent by the client from character_set_client to character_set_connection (except for string literals that have an introducer such as _latin1 or _utf8). collation_connection is important for comparisons of literal strings. For comparisons of strings with column values, collation_connection does not matter because columns have their own collation, which has a higher collation precedence.

或者在返回途中:来自商店的Latin1数据将转换为UTF-8，因为客户端告诉服务器它更喜欢使用UTF-8进行运输.

Or on the way back: Latin1 data from the store will get converted into UTF-8 because the client told the server that it prefers UTF-8 for the transportation.

您命名的PDO本身的标识符看起来完全不同:

The identifier for PDO itself you name looks like being something entirely different:

PDO :: PARAM_LOB 告诉PDO将数据映射为流，以便您可以使用PHP Streams API对其进行操作. (参考)

PDO::PARAM_LOB tells PDO to map the data as a stream, so that you can manipulate it using the PHP Streams API. (Ref)

我不是MySQL专家，但是我会这样解释.客户端和服务器需要协商他们正在使用哪些字符集，我认为他们这样做是有原因的.

I'm no MySQL expert but I would explain it this way. Client and server need to negotiate which charsets they are using and I assume they do this for a reason.

这篇关于PHP/PDO/MySQL:插入MEDIUMBLOB会存储错误数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！