MySQL InnoDB全文搜索包含电子邮件地址

I have mysql community 5.6.13 on 2 mac laptops - one with os x 10.8 and another with os x 10.9.

我在2台Mac笔记本电脑上安装了mysql社区5.6.13 - 一台使用os x 10.8,另一台使用os x 10.9。

As far as I can tell, the installations of mysql are the same but the same full text search behaves differently on each installation.

据我所知,mysql的安装是相同的,但相同的全文搜索在每次安装时表现不同。

The query I have is:

我的查询是:

SELECT legal_matter.* FROM legal_matter 
left join user_account 
on user_account.id = legal_matter.lawyer_id 
left join client_account 
on client_account.id = legal_matter.client_account_id 
WHERE MATCH (legal_matter.question) AGAINST ('[e-mail]' IN BOOLEAN MODE) 
OR user_account.username like '[e-mail]%' 
OR legal_matter.display_name like '[e-mail]%' 
OR client_account.company_name like '[e-mail]%' 

On the laptop with 10.8, the query executes normally, on the laptop with 10.9, the query complains:

在10.8的笔记本电脑上,查询正常执行,在10.9的笔记本电脑上,查询抱怨:

Error Code: 1064. syntax error, unexpected '@', expecting $end 

TI have no idea if it has anything to do with the different OS versions, I suspect not but am at a loss as to what the issue is.

TI不知道它是否与不同的操作系统版本有任何关系,我怀疑不是,但我不知道问题是什么。

Any pointers gratefully received.

任何指针都感激不尽。

Thanks.

4 个解决方案

#1

I had queries that used match against email which started failing when I switched to innodb since @ is used to search for words a certain distance apart in InnoDB:

我使用匹配电子邮件的查询,当我切换到innodb时开始失败,因为@用于在InnoDB中搜索相隔一定距离的单词:

SELECT username FROM users WHERE MATCH(user_email) AGAINST('[e-mail]' IN BOOLEAN MODE);
ERROR 1064 (42000): syntax error, unexpected '@', expecting $end

SELECT username FROM users WHERE MATCH(user_email) AGAINST("[e-mail]" IN BOOLEAN MODE);
ERROR 1064 (42000): syntax error, unexpected '@', expecting $end
mysql>

Try wrapping your email address like this:

尝试包装您的电子邮件地址,如下所

SELECT username FROM users WHERE MATCH(user_email) AGAINST('"[e-mail]"' IN BOOLEAN MODE);

or escaped:

SELECT username FROM users WHERE MATCH(user_email) AGAINST('\"[e-mail]\"' IN BOOLEAN MODE);

#2

The @ symbol is an operator and conflicts with the BOOLEAN SEARCH... it shouldn't... its a bug.

@符号是一个操作符并且与BOOLEAN SEARCH冲突......它不应该......它是一个bug。

This post, points that is more likely to be a problem of InnoDB and MyISAM...

这篇文章,更有可能成为InnoDB和MyISAM的问题......

https://bugs.mysql.com/bug.php?id=74042

Maybe, you had MyISAM on one machine's DB... dunno.

也许,你在一台机器的DB上有MyISAM ... dunno。

#3

What is the context? mysql commandline tool? shell script? Java? PHP? Other? I suspect that the answer to this will lead to what changed.

背景是什么? mysql命令行工具? shell脚本? Java的? PHP?其他?我怀疑这个问题的答案会导致改变。

Quite possibly preceding @ by \ will fix it. And that fix may work in both OSs.

很可能在@ by \之前会修复它。并且该修复可能在两个操作系统中都有效。

Do you know if the complaint is about the @ in the AGAINST? Or all of the @? Try replacing the @ in AGAINST with a space.

你知道投诉是关于反对中的@吗?还是所有的@?尝试用空格替换AGAINST中的@。

Then, since 'lawyer' and 'domain' may not be next to each other, it may be necessary to say

然后,由于“律师”和“域名”可能不会彼此相邻,因此可能需要说明

MATCH(question) AGAINST('+lawyer +domain +com +au' IN BOOLEAN MODE)
  AND question LIKE     '[e-mail]%'`

FT gives you the performance; LIKE double checks that result.

FT为您提供性能;像双重检查结果。

Caveat: Unless you have the min token size set to 2, +au will cause lead to zero hits. One workaround is to not use the + on any 'word' shorter than the min token size. (The LIKE covers you.)

警告:除非您将最小标记大小设置为2,否则+ au将导致导致零点击。一种解决方法是不要在比最小令牌大小短的任何“单词”上使用+。 (喜欢盖你。)

#4

Try to use this syntax :

尝试使用以下语法:

SELECT legal_matter.* FROM legal_matter 
left join user_account 
on user_account.id = legal_matter.lawyer_id 
left join client_account 
on client_account.id = legal_matter.client_account_id 
WHERE MATCH (legal_matter.question) AGAINST ('[e-mail]' IN BOOLEAN MODE) 
OR user_account.username like '[e-mail]%' 
OR legal_matter.display_name like '[e-mail]%' 
OR client_account.company_name like '[e-mail]%'