我在 pgAdmin 上进行查询时偶然发现了这种奇怪的行为。
我已连接到运行 PostgreSQL 9.1.9 的服务器。
我有一个名为 messages 的表,其定义如下:
ghareh@godot:~$ psql
psql (9.1.9)
Type "help" for help.
ghareh=# \d messages
Table "public.messages"
Column | Type | Modifiers
---------------+-----------------------------+---------------------------------
messageid | character varying(200) | not null
senderaliasid | integer | not null
referenceid | character varying(200) | default NULL::character varying
recipaliasid | integer |
datetime | timestamp(2) with time zone | not null
subject | character varying(512) | not null
body | text | not null
listid | integer |
Indexes:
"messages_pkey" PRIMARY KEY, btree (messageid)
"messages_datetime_idx" btree (datetime)
"recipaliasid_idx" btree (recipaliasid)
"referenceid_idx" btree (referenceid)
"senderaliasid_idx" btree (senderaliasid)
Foreign-key constraints:
"messages_listid_fkey" FOREIGN KEY (listid) REFERENCES lists(listid)
"messages_recip_fkey" FOREIGN KEY (recipaliasid, listid) REFERENCES aliases(aliasid, listid)
"messages_sender_fkey" FOREIGN KEY (senderaliasid, listid) REFERENCES aliases(aliasid, listid)
Referenced by:
TABLE "messages_attachments" CONSTRAINT "pkfkmid" FOREIGN KEY (messageid) REFERENCES messages(messageid)
我的问题涉及专栏,body
and subject
.
我有一个查询生成了一组结果。然后,为了完善我的查询,我添加了以下术语:where body like '%JSON%'
即 body 包含字符串“JSON”的结果子集。
我得到了一些包含这个词的结果,还有一些不包含这个词!但如果我搜索任意字符串,结果就可以了。我检查后发现查询不仅搜索正文列,还搜索主题列,这太疯狂了。
这是我最初的查询:
select * from messages where messageid = '[email protected]'
返回 1 行:
messageid: "[email protected]";
senderaliasid: 13777;
referenceid: "[email protected]";
recipaliasid: ;
datetime: "2006-07-17 20:53:35-07";
listid: 251;
subject: "Re: svn commit: r422930 - /incubator/abdera/java/trunk/extensions/src/main/java/org/apache/abdera/ext/json/JSONWriter.java";
body: "busted! thanks for the thorough review.
-Elias
Garrett Rooney wrote:
> On 7/17/06, [email protected] <[email protected]> wrote:
>> Author: eliast
>> Date: Mon Jul 17 17:44:10 2006
>> New Revision: 422930
>>
>> URL: http://svn.apache.org/viewvc?rev=422930 (...)"
如果我搜索:
select * from messages
where messageid = '[email protected]'
and body like '%JSON%'
我不应该得到任何结果,因为体内没有。但我仍然得到相同的行返回 - 这似乎是因为“JSON”位于subject
?
我什至尝试过这个:
select * from messages
where messageid = '[email protected]'
and body like '%incubator/abdera/java/trunk/extensions/src/main/java/org/apache/abdera/ext/json/JSONWriter.java%'
我仍然得到同样的排。我很困惑。
我尝试在 sqlfiddle.com 上重现结果,但没有成功。在那里,我得到了 sql select 查询的预期结果:
http://sqlfiddle.com/#!1/ec74c/4