MySQL InnoDB Full text search containing email address

Question

I have mysql community 5.6.13 on 2 mac laptops - one with os x 10.8 and another with 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. 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 ('lawyer@domain.com.au' IN BOOLEAN MODE) OR user_account.username like '%lawyer@domain.com.au%' OR legal_matter.display_name like '%lawyer@domain.com.au%' OR client_account.company_name like '%lawyer@domain.com.au%' ``` 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 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 issu…

Accepted Answer

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('test@user.com' IN BOOLEAN MODE);
ERROR 1064 (42000): syntax error, unexpected '@', expecting $end

SELECT username FROM users WHERE MATCH(user_email) AGAINST("test@user.com" 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('"test@user.com"' IN BOOLEAN MODE);
```

or escaped:

```
SELECT username FROM users WHERE MATCH(user_email) AGAINST('\"test@user.com\"' IN BOOLEAN MODE);
```

MySQL InnoDB Full text search containing email address

Solution

Problem