免责声明
在尝试结束这个问题之前,请理解我已经将其发布在 wordpress.se.com 上,甚至提供了赏金,但没有答案。 这让我相信也许这个问题与WordPress本身无关,而是一些我还不明白的php/mysql怪癖。 我希望我在下面为php/mysql专家提供了足够的信息,让我了解可能出了什么问题。
链接到原始帖子
我遇到的问题是更新语句
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>
块,<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;
您没有发布处理数据库交互(如何构建和执行查询(的代码,但是如果您prepare
字符串,则应按预期完整插入/更新数据。
我把字符串运行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;连接、整理等