MySQL InnoDB全文搜索，包含电子邮件地址 [英] MySQL InnoDB Full text search containing email address

问题描述

我在2台Mac笔记本电脑上拥有mysql社区5.6.13-一个使用os x 10.8，另一个使用os x 10.9.

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

据我所知，mysql的安装是相同的，但是相同的全文本搜索在每个安装上的行为是不同的.

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

我的查询是:

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]%' 

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

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

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

TI不知道它是否与不同的OS版本有关，我怀疑不是，但是对问题所在不知所措.

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.

任何指针都将受到感激.

Any pointers gratefully received.

谢谢.

推荐答案

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

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:

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);

或逃脱:

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

这篇关于MySQL InnoDB全文搜索，包含电子邮件地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！