php - MySQL更新语句被切断

Question

免责声明

在尝试结束这个问题之前，请理解我已经将它发布在 wordpress.se.com 上，甚至提供了赏金但没有答案。这让我相信问题可能与 WordPress 本身无关，而是一些我尚不了解的 php/mysql 怪癖。我希望我在下面为 php/mysql 专家提供了足够的信息，让我知道可能出了什么问题。

Link to original post

我遇到的问题是更新语句

UPDATE
    `s_6_posts`
SET
    `post_author` = 9,
    `post_date` = '2012-04-11 20:40:05',
    `post_date_gmt` = '2012-04-11 20:40:05',
    `post_content` = '\n\n\n<div class=\"WordSection1\">\n\n<p class=\"MsoNormal\" style=\"margin-bottom:0in;margin-bottom:.0001pt;line-height:normal\"><b><span style=\'font-size:9.0pt;font-family:\"ArialNarrow\",\"sans-serif\";color:red\'>This is a new document</span></b></p>\n\n<p class=\"MsoNormal\" style=\"margin-bottom:0in;margin-bottom:.0001pt;line-height:normal\"><b><span style=\'font-size:9.0pt;font-family:\"ArialNarrow\",\"sans-serif\";color:red\'> </span></b></p>\n\n<p class=\"MsoNormal\" style=\"margin-bottom:0in;margin-bottom:.0001pt;line-height:normal\"><b><span style=\'font-size:9.0pt;font-family:\"ArialNarrow\",\"sans-serif\";color:red\'>Test Test Test</span></b></p>\n\n</div>\n\n\n',
    `post_content_filtered` = '',
    `post_title` = '',
    `post_excerpt` = '',
    `post_status` = 'draft',
    `post_type` = 'post',
    `comment_status` = 'open',
    `ping_status` = 'open',
    `post_password` = '',
    `post_name` = '',
    `to_ping` = '',
    `pinged` = '',
    `post_modified` = '2012-04-11 20:40:05',
    `post_modified_gmt` = '2012-04-11 20:40:05',
    `post_parent` = 0,
    `menu_order` = 0,
    `guid` = 'https://qa.citivelocity.com/cvauthor/equitywire/?p=230'
WHERE
    `ID` = 230

在 php(WordPress 代码库)中执行时被切断。 post_content 字段在数据库中的结果是

<div class="WordSection1">

<p class="MsoNormal" style="margin-bottom:0in;margin-bottom:.0001pt;line-height:normal"><b><span style='font-size:9.0pt;font-family:"ArialNarrow","sans-serif";color:red'>This is a new document</span></b></p>

<p class="MsoNormal" style="margin-bottom:0in;margin-bottom:.0001pt;line-height:normal"><b><span style='font-size:9.0pt;font-family:"ArialNarrow","sans-serif";color:red'>

问题

这里发生的事情是在

<span style='font-size:9.0pt;font-family:"ArialNarrow","sans-serif";
    color:red'> </span>

block ，<span> 之间的空格之后的所有内容标签被切断。我已经能够与其他包含空间的数据一致地重现此问题。这似乎不是字段的大小限制，因为无论数据长度如何，截止总是发生在空格处。

这个 SQL 语句是在 WordPress 调试日志中输出的，我有充分的理由相信它实际上是正在执行的语句。我通过 MySQL 客户端 (Aqua Data Studio) 运行相同的语句，但没有发生中断。

我在 CentOs 上运行 Mysql 5.0.77 和 php 5.3。表是 MyISAM，排序规则是 utf8_general_ci。

发布表架构:

    CREATE TABLE `s_posts` (
      `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
      `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `post_content` longtext NOT NULL,
      `post_title` text NOT NULL,
      `post_excerpt` text NOT NULL,
      `post_status` varchar(20) NOT NULL DEFAULT 'publish',
      `comment_status` varchar(20) NOT NULL DEFAULT 'open',
      `ping_status` varchar(20) NOT NULL DEFAULT 'open',
      `post_password` varchar(20) NOT NULL DEFAULT '',
      `post_name` varchar(200) NOT NULL DEFAULT '',
      `to_ping` text NOT NULL,
      `pinged` text NOT NULL,
      `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `post_content_filtered` text NOT NULL,
      `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
      `guid` varchar(255) NOT NULL DEFAULT '',
      `menu_order` int(11) NOT NULL DEFAULT '0',
      `post_type` varchar(20) NOT NULL DEFAULT 'post',
      `post_mime_type` varchar(100) NOT NULL DEFAULT '',
      `comment_count` bigint(20) NOT NULL DEFAULT '0',
      PRIMARY KEY (`ID`),
      KEY `post_name` (`post_name`),
      KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
      KEY `post_parent` (`post_parent`),
      KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Answer 1

您没有发布处理数据库交互的代码(如何构建和执行查询)，但如果您准备 字符串，您的数据应该按预期完整插入/更新。

我通过 PDO::prepare 运行字符串并得到:

6774 Query  update fulltext_test set post_content = '\\n\\n\\n<div class=\\\"WordSection1\\\">\\n\\n<p class=\\\"MsoNormal\\\" style=\\\"margin-bottom:0in;margin-bottom:.0001pt;line-height:normal\\\"><b><span style=\'font-size:9.0pt;font-family:\\\"ArialNarrow\\\",\\\"sans-serif\\\";color:red\'>This is a new document</span></b></p>\\n\\n<p class=\\\"MsoNormal\\\" style=\\\"margin-bottom:0in;margin-bottom:.0001pt;line-height:normal\\\"><b><span style=\'font-size:9.0pt;font-family:\\\"ArialNarrow\\\",\\\"sans-serif\\\";color:red\'> </span></b></p>\\n\\n<p class=\\\"MsoNormal\\\" style=\\\"margin-bottom:0in;margin-bottom:.0001pt;line-height:normal\\\"><b><span style=\'font-size:9.0pt;font-family:\\\"ArialNarrow\\\",\\\"sans-serif\\\";color:red\'>Test Test Test</span></b></p>\\n\\n</div>\\n\\n\\n' WHERE id = 1 limit 1

注意转义字符的所有转义。验证数据插入mysql成功:

mysql> select * from fulltext_test\G
*************************** 1. row ***************************
    id: 1
    comment: this is a test comment right here 123453432
    trimmed_comments: this 
    post_content: \n\n\n<div class=\"WordSection1\">\n\n<p class=\"MsoNormal\" style=\"margin-bottom:0in;margin-bottom:.0001pt;line-height:normal\"><b><span style='font-size:9.0pt;font-family:\"ArialNarrow\",\"sans-serif\";color:red'>This is a new document</span></b></p>\n\n<p class=\"MsoNormal\" style=\"margin-bottom:0in;margin-bottom:.0001pt;line-height:normal\"><b><span style='font-size:9.0pt;font-family:\"ArialNarrow\",\"sans-serif\";color:red'> </span></b></p>\n\n<p class=\"MsoNormal\" style=\"margin-bottom:0in;margin-bottom:.0001pt;line-height:normal\"><b><span style='font-size:9.0pt;font-family:\"ArialNarrow\",\"sans-serif\";color:red'>Test Test Test</span></b></p>\n\n</div>\n\n\n

以及代码片段:

<?php

$dsn = 'mysql:dbname=zzz_test;host=127.0.0.1';

try {
    $dbo = new PDO($dsn, 'user', 'pass');
} catch (Exception $e) {
    var_dump($e->getMessage());
}

$string = '\n\n\n<div class=\"WordSection1\">\n\n<p class=\"MsoNormal\" style=\"margin-bottom:0in;margin-bottom:.0001pt;line-height:normal\"><b><span style=\'font-size:9.0pt;font-family:\"ArialNarrow\",\"sans-serif\";color:red\'>This is a new document</span></b></p>\n\n<p class=\"MsoNormal\" style=\"margin-bottom:0in;margin-bottom:.0001pt;line-height:normal\"><b><span style=\'font-size:9.0pt;font-family:\"ArialNarrow\",\"sans-serif\";color:red\'> </span></b></p>\n\n<p class=\"MsoNormal\" style=\"margin-bottom:0in;margin-bottom:.0001pt;line-height:normal\"><b><span style=\'font-size:9.0pt;font-family:\"ArialNarrow\",\"sans-serif\";color:red\'>Test Test Test</span></b></p>\n\n</div>\n\n\n';

$sql = 'update fulltext_test set post_content = :postContent WHERE id = 1 limit 1;';

$stmt = $dbo->prepare($sql);

$stmt->bindValue(':postContent', $string);

$stmt->execute();

所以，在执行查询之前尝试对字符串进行转义，你应该是黄金。为了记录，一切都是UTF-8；连接、整理等