問題描述

我有一個簡單的 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();

任何幫助將不勝感激.

UPDATE:有問題的服務器上的默認 MySQL 字符集是 utf8；其他人是latin1.

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.

這對我來說似乎一個錯誤糟糕的設計:為什么連接的字符集會對二進制列的數據產生任何影響，特別是當它被識別為用 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.

推薦答案

這對我來說似乎是一個錯誤:為什么連接的字符集會對二進制列的數據產生任何影響，尤其是當它被識別為 PDO 本身的二進制數據時，PARAM_LOB?

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?

我不認為這一定是一個錯誤.我可以想象，每當客戶端與服務器交談并說以下命令是 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 存儲壞數據的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！