MySQL fulltext search with @ symbol produces error

问题:

The following query results in an error due to the @ (at symbol). The query will work fine when it is removed. I tried escaping the @ character, but no luck.

SELECT * FROM clients WHERE MATCH (form) AGAINST ('[e-mail]' IN BOOLEAN MODE);

The error produced is:

#1064 - syntax error, unexpected '@', expecting $end

Note that I am testing these queries in the phpMyAdmin SQL console area, so it's not a matter of an escape error with my other programming.

MySQL server is version 5.6.17.

Any ideas? Thanks.

回答1:

This is connected to INNODB FULLTEXT indexes.

It is introduced as a combination of:

1. InnoDB full-text search does not support the use of multiple operators on a single search word

2. @distance This operator works on InnoDB tables only. It tests whether two or more words all start within a specified distance from each other, measured in words.

http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

# Running a test search for MATCH('+test{$ascii}test' IN BOOLEAN MODE) on all ASCII chars returns errors on:
40 (
41 )
64 @

MYSQL seems to be treating these symbols as wordbreaks and I have found no way to escape and include these in the actual query so my solution is the split on the symbol and include them as a group e.g. “test@bar” == (+test +bar)

# As a further test, running a search for MATCH('+{$ascii}' IN BOOLEAN MODE) returns errors for:
40 (
41 )
42 *
43 +
45 -
60 <
62 >
64 @
126 ~

Which is as expected from the MYSQL docs as the special BOOLEAN modifier characters

# As a testcase (Requires MYSQL 5.6+): 
CREATE TABLE `fulltext_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
 `text` text COLLATE utf8_unicode_ci,
 PRIMARY KEY (`id`),
 FULLTEXT KEY `text` (`text`)
) ENGINE=InnoDB
INSERT INTO `fulltext_innodb` (`id`, `text`) VALUES (1, 'test@bar');

SELECT * FROM `fulltext_innodb` WHERE MATCH (`text`) AGAINST( '+test@bar’ IN BOOLEAN MODE )

#1064 - syntax error, unexpected '@', expecting $end

回答2:

It seems that there is no way to replace the "@" term with any other character. Removing the "@" and adding the string after it to the search is the best workaround that i found so far. That means that

$mail = '[e-mail]';
$mail = str_replace("@", " +", $mail); //replace the @ that causes the problem
$query = "SELECT * FROM clients WHERE MATCH (form) AGAINST ('$mail' IN BOOLEAN MODE)'; //query with replaced mail address

should bring the needed result.

Another way would be to handle it like in this post that contains a similar problem with another good workaround.