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

mysql full-text search boolean mode
mysql similar text search
mysql full-text search wildcard
mysql search query
fulltext index
mysql tilde
mysql 5.7 match

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.


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

MySQL Bugs: #74042: Fulltext query including @ symbol fails in , Bug #74042, Fulltext query including @ symbol fails in boolean mode on but quoting the search string will produce results that look more like  Ike Walker Description:Some fulltext queries that work on a MyISAM table fail on an InnoDB table. The specific case I have seen is that searching for a string containing the @ symbol fails on an InnoDB table with a fulltext index if the query is executed in boolean mode.


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.

12.9.2 Boolean Full-Text Searches, Specifying a trailing plus or minus sign causes InnoDB to report a syntax error. InnoDB full-text search does not support the use of the @ symbol in boolean  mysql 5.6 > use test Database changed mysql 5.6 > DROP TABLE IF EXISTS `ft_test`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql 5.6 > mysql 5.6 > CREATE TABLE IF NOT EXISTS `ft_test` ( -> `uid` int(11) DEFAULT NULL, -> `strings` text COLLATE utf8_bin, -> FULLTEXT KEY `strings` (`strings`) -> ) ENGINE=InnoDB COLLATE=utf8_bin; Query OK, 0


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.

Full-Text Search vulnerable to some special characters · Issue #1 , By default, SQL's Full-Text Search seems to handle some special characters -​search-with-symbol-produces-error-syntax-error-unexpected/25972465#​25972465 Note that this is a MySQL bug affecting only InnoDB tables. The inverted index will tell you about the records where that particular search term is present. Indexing. Full-text indexes in MySQL are an index of type FULLTEXT. FULLTEXT indexes can be created from VARCHAR and TEXT columns at CREATE TABLE time or added later with ALTER TABLE or CREATE INDEX.


Full text search of values with a special character (') produces , I'm having a problem with MySQL full text search. I have a users table that has a single column, username . The problem appears when I'm  12.9.1 Natural Language Full-Text Searches 12.9.2 Boolean Full-Text Searches 12.9.3 Full-Text Searches with Query Expansion 12.9.4 Full-Text Stopwords 12.9.5 Full-Text Restrictions 12.9.6 Fine-Tuning MySQL Full-Text Search 12.9.7 Adding a Collation for Full-Text Indexing 12.9.8 ngram Full-Text Parser 12.9.9 MeCab Full-Text Parser Plugin


MySQL Full text search, MySQL Full text search: Full-Text Search in MySQL server lets users run The full-text index can include one or more character-based columns in the table. ('​SQL Cross Join','The SQL CROSS JOIN produces a result set which is but as per result it shows me error (#1064 - syntax error, unexpected '+'). These search types are described in Section 12.9.2, “Boolean Full-Text Searches”, and Section 12.9.3, “Full-Text Searches with Query Expansion”. A full-text search that uses an index can name columns only from a single table in the MATCH() clause because an index cannot span multiple tables.


CN104133854A, MySQL full-text search is used specific participle technique, is utilized the the failure of MySQL simplified Chinese character full-text search will directly be of test use, the database table that creates test use, establishment full-text index,  MySQL Full text search: Full-Text Search in MySQL server lets users run full-text queries against character-based data in MySQL tables. There are three types of FULL text search - Natural Language Full-Text Searches, Boolean Full-Text searches, Query expansion searches . The full-text index can include one or more character-based columns in the table.