MYSQL - 语法错误，意外 '@'，期待 $end

Question

我正在尝试使用以下 SQL 语法编写全文搜索:

SELECT   u.usuario AS `id`,
         Concat('(', u.usuario, ') ', u.nombre) AS `text`
FROM     usuarios AS u
WHERE    u.cuenta_id = 10
AND      MATCH (u.terminos) against ('jacuna@sgc.gov.co' IN boolean mode)
ORDER BY u.usuario ASC
LIMIT    50

• u.usuario 存储用户ID的字符串值
• u.nombre 存储用户的全名
• u.terminos 存储一些与 AGAINST 一起使用的串联值

示例:

如果用户的 ID '123456' 和全名 'john doe'，则 MySQL 列 'terminos' 将包含 id + 全名 意思是“123456 john doe”

注意 有时用户 ID 可以是电子邮件，因此，它会带有“@”字符。当 AGAINST 值包含“@”时，我收到此错误:

Syntax error, unexpected '@', expecting $end

非常感谢。

Answer 1

将短语放在单引号字符串内的双引号中。

我在 MySQL 5.6 上测试过这个:

mysql> SELECT u.usuario, terminos
  FROM usuarios AS u
  WHERE u.cuenta_id = 10 AND
       MATCH (u.terminos) AGAINST ('"jacuna@sgc.gov.co"' IN BOOLEAN MODE);
+---------+------------------------+
| usuario | terminos               |
+---------+------------------------+
|       1 | call jacuna@sgc.gov.co |
+---------+------------------------+

阅读https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html :

A phrase that is enclosed within double quote (") characters matches only rows that contain the phrase literally, as it was typed. The full-text engine splits the phrase into words and performs a search in the FULLTEXT index for the words. Nonword characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order. For example, "test phrase" matches "test, phrase".