MySQL fulltext search with @ symbol produces error "syntax error, unexpected '@', expecting $end"

Question

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 ('test@test.com' 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.

Answer 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

Answer 2

Not a direct answer, but if anybody is looking for a PHP code to handle tokenizing of user-input search string for Full Text Searching, can use the following code:

/**
 * Method to take an input string and tokenize it into an array of words for Full Text Searching (FTS).
 *
 * This method is used when an input string can be made up of multiple words (let's say, separated by space characters),
 * and we need to use different Boolean operators on each of the words. The tokenizing process is similar to extraction
 * of words by FTS parser in MySQL. The operators used for matching in Boolean condition are removed from the input $phrase.
 * These characters as of latest version of MySQL (8+) are: +-><()~*:""&|@  (@ is specific for InnoDB)
 * We can also execute the following query to get updated list: show variables like 'ft_boolean_syntax';
 * Afterwards, the modified string is split into individual words considering either space, comma, and, period (.) characters.
 * Details at: https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html
 *
 * @param string $phrase Input statement/phrase consisting of words
 * @return array Tokenized words
 * @author Madhur, 2019
 */
function tokenizeStringIntoFTSWords(string $phrase) : array {
    $phrase_mod = trim(preg_replace('/[><()~*:"&|@+-]/', ' ', trim($phrase)));
    $words_arr = preg_split('/[\s,.]/', $phrase_mod, null, PREG_SPLIT_NO_EMPTY);

    // filter out the fulltext stop words and words whose length is less than 3.
    $fts_words = array();
    $fulltext_stop_words = array(
        'about','are','com','for','from','how','that','this','was','what',
        'when','where','who','will','with','und','the','www'
    );
    foreach($words_arr as $word) {
        // By default MySQL FULLTEXT index does not store words whose length is less than 3.
        // Check innodb_ft_min_token_size Ref: https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_ft_min_token_size
        // So we need to ignore words whose length is less than 3.
        if(strlen($word) < 3) continue;

        // Ignore the fulltext stop words, whose length is greater than 3 or equal to 3.
        // Ref: https://dev.mysql.com/doc/refman/8.0/en/fulltext-stopwords.html
        if (in_array($word, $fulltext_stop_words)) continue;

        $fts_words[] = $word;
    }

    return $fts_words;
}

Above code will handle Stopwords, minimum word length limit, and Boolean mode operators as well. So, for instance, if user inputs: Search@bar with in Javascript, it will return an array of (Search, bar, Javascript). Afterwards, a Full text query can be written using this array.

Answer 3

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 = 'test@test.com';
$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.