MySQL/PDO截断数据 [英] MySQL/PDO truncates the data

问题描述

$book是一个7kb的字符串.如果使用PHP PDO exec执行此查询，则monograph列(LONGTEXT)数据将被截断为6765个字符:

$book is a 7kb string. If this query is executed using PHP PDO exec, the monograph column (LONGTEXT) data gets truncated at 6765 character:

echo strlen($book); // output 7157

$db->exec("UPDATE `chemicals` SET `monograph` = {$db->quote($book)} WHERE `id` = {$db->quote($c['id'])};");

但是，如果我打印查询并使用SQL客户端(绕过PHP)执行查询，它将把所有数据插入数据库.这让我觉得我还不熟悉这是PHP设置.

However, if I print the query and execute it using SQL client (bypassing PHP), it inserts all the data to the database. Which makes me think it is PHP setting that I am not yet familiar to.

请注意，如果我使用准备好的语句(包括PDO :: PARAM_LOB)，也会发生同样的情况.

Note that the same is happening if I use prepared statements (incl. with PDO::PARAM_LOB).

$book值(7157).最终存储在数据库中的实际数据 https://gist.github.com/df49d4a9707660b8b60b (6765) .我不明白从技术上讲这种数据截断是怎么可能的，因为整个查询都传递给了MySQL(否则会弹出SQL语法错误).

$book value dumped before exec https://gist.github.com/79d5fe1050bbb0e2fac8 (7157). The actual data that ends up at the database https://gist.github.com/df49d4a9707660b8b60b (6765). I don't understand how such data truncation is technically possible since the whole query is passed to MySQL (otherwise SQL syntax error would pop).

echo "UPDATE `chemicals` SET `monograph` = {$db->quote($book)} WHERE `id` = {$db->quote($c['id'])};";

如果我使用SQL客户端执行输出(https://gist.github.com/a05fe4c033e74897b82b)，则该数据最终存储在数据库

If I execute the output (https://gist.github.com/a05fe4c033e74897b82b) using SQL client, this is the data that ends up in the database https://gist.github.com/88870fe26a3ae40e991e (7157, expected).

PDO是使用UTF8连接启动的.

PDO is initiated using UTF8 connection.

new PDO('mysql:dbname=[..];host=localhost;charset=utf8', 'root', '[..]', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';"));

---

更新2012年7月25日美国东部时间

现在我知道这是编码问题.

Now I know that's encoding issue.

$db->exec("UPDATE `chemicals` SET `monograph` = {$db->quote(utf8_decode($book))} WHERE `id` = {$db->quote($c['id'])};");

但是，我不确定要怎么做.我与MySQL的连接已经是unicode.

However, I am not quiet sure what to do about it. My connection to MySQL is unicode already.

/etc/my.cnf配置为在服务器范围内使用以下设置.

/etc/my.cnf is configured to use the following settings server-wide.

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

推荐答案

此处有两点.一种是理想情况下所有字符编码都必须为UTF8-服务器，客户端，连接和表.二是PHP的strlen函数计算字节，而不是字符.

There are two points to be made here. One is that ideally all character encodings must be UTF8 - that's server, client, connection, and table. Two is that PHP's strlen function counts bytes, not characters.

您的表字符集可能未设置为UTF8.你可以做

Your table character set may not be set to UTF8. You can do

SHOW CREATE TABLE chemicals;

进行检查.您还应该将这些添加到您的my.cnf:

to check that. You should also add these to your my.cnf:

[mysqld]
character-set-client=utf8
character-set-results=utf8

在此处阅读有关MySQL字符集的更多信息:

Read more about MySQL character sets here:

MySQL字符集

这篇关于MySQL/PDO截断数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！