我正在使用 Laravel 5.5 & MySQL . 我想使用全文搜索电子邮件,但我一直收到此错误:
语法错误或访问冲突:1064语法错误,意外'@',期待$ end(SQL:select * from users where MATCH(users.email)AGAINST(user @ * IN BOOLEAN MODE)或MATCH(users.username)AGAINST (用户@ * IN BOOLEAN模式))
这就是我要查询的方式
$keyword = $request->keyword;
$exp = explode(' ', $keyword);
$s = '';
$c = 1;
foreach ($exp AS $e)
{
$s .= "+$e*";
if ($c + 1 == count($exp))
$s .= ' ';
$c++;
}
$users = User::whereRaw("MATCH (users.email) AGAINST (? IN BOOLEAN MODE)", [$s])
->orWhereRaw("MATCH (users.username) AGAINST (? IN BOOLEAN MODE)", [$s])
->get();
我已经尝试更改where子句以逃避并添加引号但它不起作用
::whereRaw('"MATCH (users.email) AGAINST (? IN BOOLEAN MODE)"', [$s])
->orWhereRaw('"MATCH (users.username) AGAINST (? IN BOOLEAN MODE)"', [$s])
::whereRaw("MATCH (users.email) AGAINST (? IN BOOLEAN MODE)", ["$s"])
->orWhereRaw("MATCH (users.username) AGAINST (? IN BOOLEAN MODE)", ["$s"])
::whereRaw('MATCH (users.email) AGAINST ("?" IN BOOLEAN MODE)', [$s])
->orWhereRaw('MATCH (users.username) AGAINST ("?" IN BOOLEAN MODE)', [$s])
1 回答
这是MySQL的一个限制 . From the documentation:
此问题已打开bug report,但随着文档更新而关闭 . 这让我相信他们不会很快改变 .
您可以尝试将表更改为MyISAM引擎 . 这并不理想,根据提到的错误报告,仍然可能无法解决问题 .
除非你在字段中填充多个电子邮件或用户名,否则我认为你可以使用varchars,普通索引和
LIKE
查询 . 由于您似乎只是从字符串的开头搜索(例如email LIKE 'user@%'
),因此将使用普通索引 . 当您需要将通配符放在字符串的开头时,会遇到麻烦 .