在 Laravel 9 中使用原生表达查询时，in 条件用于多个字段
1、参考：在 MySQL 8 中，in 条件用于多个字段 。
1 2 | SELECT * FROM `customers` WHERE ( name, email ) IN (( '123', '123@outlook.com' ),( '我是客户姓名33', '1303842899@qq.com' )) |
2、需要在 Laravel 9 中使用原生表达查询时，in 条件用于多个字段。whereRaw 和 orWhereRaw 方法将原生的「where」注入到你的查询中。
1 2 3 4 | $customers = DB::table('customers') ->whereRaw('email in ?', ['(\'123@outlook.com\', \'12414dfgfdg@78.com\')']) ->get();print_r($customers);exit; |
3、执行报错：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 。如图1
![执行报错：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](https://www.shuijingwanwq.com/wp-content/uploads/2024/03/1-4.png)
图1
1 | "message": "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 `customers` where email in ('123@outlook.com', '12414dfgfdg@78.com'))", |
4、不再使用 ?，而是直接拼接出原生 SQL。查询成功。如图2
图2
1 2 3 4 | $customers = DB::table('customers') ->whereRaw('email in (\'123@outlook.com\', \'12414dfgfdg@78.com\')') ->get();print_r($customers);exit; |
5、再次调整，以使 in 条件用于多个字段。查询成功。如图3
图3
1 2 3 4 | $queryBuilder = app('Modules\Order\Models\Customer')::query();$customers = $builder->whereRaw('(name,email) in ((\'123\',\'123@outlook.com\'),(\'我是客户姓名33\',\'1303842899@qq.com\'))') ->get();print_r($customers);exit; |
1 2 3 4 5 6 7 8 9 10 | $queryBuilder = app('Modules\Order\Models\Customer')::query();print_r($criteria['customers']);$str = '';foreach ($criteria['customers'] as $key => $customer) { $str .= ($key == 0) ? '(\'' : ',(\''; $str .= implode('\',\'', $customer) . '\')';}print_r($str);$customers = $builder->whereRaw('(name,email) in (' . $str . ')')->get();print_r($customers);exit; |
6、查看生成的 SQL，符合预期。如图4
图4
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | select `id`, `name`, `email`, `type`from `customers`where (name, email) in ( ('王某人', '44445@163.com'), ('客户姓名', '44445@163.com'), ('王某某', '4444@163.com'), ('王某某', '44445@163.com'), ('李某某', '4444@163.com'), ('客户姓名', '4444@163.com') )order by `id` desc |
7、后续发现 SQL 报错，具体可参考：在 Laravel 9 中使用原生表达查询时，报错：SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; 。
1 条回复
[…] 2、参考：在 Laravel 9 中使用原生表达查询时，in 条件用于多个字段 […]