如何将 sqllite 语句从 '=' 转换为 MATCH [英] how to convert an sqllite statement from '=' to MATCH instead

问题描述

我正在尝试为电子邮件线程创建一个 dbase 解决方案.当我收到一封电子邮件时.. 我想知道这封电子邮件是否属于现有线程.

I'm trying to create a dbase solution for email threading. When I receive an email.. I want to know if this email belongs to an existing thread.

所以我匹配主题 ie subject = 'cool bro' 匹配 're: cool bro'

我也想匹配发送者和接收者对

and i also want to match the sender and receiver pair

即

(发送者 = '[e-mail]' 接收者 = '[e-mail]')

或

(发送者 ='[e-mail]' 和接收者 = '[e-mail]')

对于那些确切的情况......这个查询工作正常(查看更多细节此处):

for those exact cases.. this query worked fine (see more details here):

(SELECT COUNT(search_email.threadID) FROM search_email 
                                                        WHERE search_email.subject MATCH '%query%'  AND 
                                                        (
                                                            (search_email.sender = '%sender' AND search_email.tos = '%receiver%')
                                                            OR 
                                                            (search_email.sender = '%receiver%' AND search_email.tos = '%sender%')
                                                        )

问题是当您遇到这样的情况时:

problem is when you have a case like this:

(发件人 = 'Amanda Collins [e-mail]' 和接收者 = '[e-mail]')

或

(发件人 ='Billy Bob [e-mail]' 和接收者 = '[e-mail]')

显然.. MATCH 子句(或一些正则表达式或其他东西)在这里是有序的..

clearly.. a MATCH clause (or some regex or somthing) is in order here..

问题是 sqllite 不允许 MATCH 与 OR 一起工作:需要一个 UNION 代替..

problem is that sqllite doesn't allow MATCH to work with OR: a UNION is needed instead..

但我想不出一种方法将上述语句转换为具有 UNION/INTERSECT 的语句:

but I couldn't figure out a way to convert the above statement into a statement that has UNION/INTERSECT instead:

SELECT * FROM search_email WHERE 
                    search_email.subject MATCH '%query%'  INTERSECT SELECT * FROM 
                                        (SELECT * FROM  (SELECT * FROM search_email WHERE 
                                                                                    search_email.sender MATCH '%sender%' 
                                                                                 INTERSECT SELECT * FROM search_email WHERE search_email.tos MATCH '%receiver%' )
                                        UNION 
                                                                            SELECT * FROM(      SELECT * FROM search_email WHERE search_email.sender MATCH '%sender%' 
                                                                                 INTERSECT SELECT * FROM search_email WHERE search_email.tos MATCH '%receiver%'))

有什么想法吗?

更新:似乎答案只是将第一个 intersect 更改为 union:

update: it seems the answer is just by changing the first intersect into a union:

SELECT * FROM search_email WHERE 
                    search_email.subject MATCH '%query%'  UNION SELECT * FROM 
                                        (SELECT * FROM  (SELECT * FROM search_email WHERE 
                                                                                    search_email.sender MATCH '%sender%' 
                                                                                 INTERSECT SELECT * FROM search_email WHERE search_email.tos MATCH '%receiver%' )
                                        UNION 
                                                                            SELECT * FROM(      SELECT * FROM search_email WHERE search_email.sender MATCH '%sender%' 
                                                                                 INTERSECT SELECT * FROM search_email WHERE search_email.tos MATCH '%receiver%'))

但是这个解决方案似乎非常效率低下..mysql编译器需要一段时间才能弄清楚..有没有更有效的方法?

but this solution seems very inefficient.. mysql compiler takes a while to figure it out.. is there a more efficient way?

推荐答案

使用 MATCH 或 LIKE '%string%' 总是很慢.您也许可以使用全文搜索来改进这一点，但您仍然不会走得很远.

Using MATCH or LIKE '%string%' will be always very slow. You might be able to improve that using full text search, but you still will not get very far.

还有另一种方法可以做到这一点，它更有效并且得到广泛支持.

There is another way to do this, which is more efficient and is widely supported.

根据 RFC2822(和更早的 RFC822) 邮件客户端程序应该在邮件头中创建和维护有助于线程支持的字段.实际上，所有已知的邮件客户端实际上都支持这一点.这些字段是:

According to RFC2822 (and older RFC822) mail client programs should create and maintain fields in mail headers that facilitate threading support. In practice, all known mail clients actually support that. Those fields are:

• Message-ID:通常看起来像
• In-Reply-To:此人回复的消息的消息 ID
• 引用:此消息可能链接到的消息 ID 列表

• Message-ID: typically looks like <[e-mail]>
• In-Reply-To: message-id of the message this one was replied to
• References: list of message-id's that this message might be linked to

如果您提取这些标题并将它们保存在数据库中单独的列和/或表中，您应该能够通过使用精确比较轻松创建线程(没有 MATCH '%string%')，所以它会非常快.

If you extract these headers and save them in separate columms and/or tables in your database, you should be able to easily create threads by using exact comparison (no MATCH '%string%'), so it will be very fast.

这篇关于如何将 sqllite 语句从 '=' 转换为 MATCH的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！