我试图查询一个MySQL数据库表,该表包含一个JSON列。
在MySQL中,以下查询运行
mysql> select members from `conversations` where `members` = CAST('[1,2,3]' AS JSON) limit 3;
我可以从mysql控制台看到结果。
+-----------+
| members |
+-----------+
| [1, 2, 3] |
| [1, 2, 3] |
| [1, 2, 3] |
+-----------+
然而,当我使用Laravel QueryBuilder来构建这个查询时。它抛出语法错误。
Laravel代码(控制器内部):
$memberList = $request->input('members');
DB::table("conversations")->where('members', '=', DB::raw('CAST('''.$memberList.'''AS JSON'))->get();
错误消息:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 (SQL: select * from `conversations` where `members` = CAST('[1,2,3]'AS JSON)
你能帮我知道我哪里做错了吗?
首先必须将$memberlist
转换为字符串。
$memberList = $request->input('members');
$memberListString = '[' . implode(",", $memberlist) . ']';
然后你可以使用;
DB::table("conversations")
->whereRaw("members = CAST('".$memberListString."' AS JSON)")->get();
我相信它会起作用的。