In MySQL 8, the in condition is used for multiple fields, and the query result is empty
1. In MySQL 8, the IN condition is used for multiple fields, and the query result is empty. as shown in Figure 1
Figure 1
SELECT
`id`,
`name`,
`email`,
`type`
FROM
`customers`
WHERE
( 'name,email' ) IN ( '(\'客户姓名二\',\'44445@163.com\'),(\'客户姓名\',\'44445@163.com\')' )
ORDER BY
`id` DESC
2. Reference:In MySQL 8, the in condition is used for multiple fields The query result is not empty for sql as follows
SELECT * FROM `customers` WHERE ( NAME, email ) IN (( '123', '123@outlook.com' ),
( '我是客户姓名33', '1303842899@qq.com' ))
3. Reference:Error when using native expression query in Laravel 9: SQLState[42000]: syntax error or access vitamin: 1064 you have an error in your SQL syntax; Adjust the code, print the binding parameters, as expected, no problem. as shown in Figure 2
![参考：在 Laravel 9 中使用原生表达查询时，报错：SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;](https://www.shuijingwanwq.com/wp-content/uploads/2024/06/2.png)
Figure 2
$str = '';
foreach ($criteria['whereRawIn'][1] as $key => $customer) {
$str .= ($key == 0) ? '(\'' : ',(\'';
$str .= implode('\',\'', $customer) . '\')';
}
var_dump($criteria['whereRawIn'][0]);
var_dump($str);
exit;
string(10) "name,email"
string(2959) "('客户姓名二','44445@163.com'),('客户姓名','44445@163.com')"
4. The adjustment code is implemented as follows
$str = '';
foreach ($criteria['whereRawIn'][1] as $key => $customer) {
$str .= ($key == 0) ? '(\'' : ',(\'';
$str .= implode('\',\'', $customer) . '\')';
}
// var_dump('(' . $criteria['whereRawIn'][0] . ')');
// var_dump('(' . $str . ')');
// exit;
$builder->whereRaw('? in ?', ['(' . $criteria['whereRawIn'][0] . ')', '(' . $str . ')']);
5. Runtime error: 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? Order by `id` descat line 1. as shown in Figure 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 '? order by `id` desc' at line 1 。](https://www.shuijingwanwq.com/wp-content/uploads/2024/06/3.png)
Figure 3
Figure 4
7. The adjustment code is implemented as follows, and an error is reported at runtime: sqlstate[21000]: Cardinality Violation: 1241 Operand should contain 2 column(s) . However, copy the error-reported SQL to the database, execute it manually, and the execution is successful. as shown in Figure 5
![调整代码实现如下，运行时报错：SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 2 column(s) 。但是，将报错的 SQL 复制到数据库中，手动执行，执行成功](https://www.shuijingwanwq.com/wp-content/uploads/2024/06/5.png)
Figure 5
$str = '';
foreach ($criteria['whereRawIn'][1] as $key => $customer) {
$str .= ($key == 0) ? '(\'' : ',(\'';
$str .= implode('\',\'', $customer) . '\')';
}
// var_dump('(' . $criteria['whereRawIn'][0] . ')');
// var_dump('(' . $str . ')');
// exit;
// $builder->whereRaw('? in ?', ['(' . $criteria['whereRawIn'][0] . ')', '(' . $str . ')']);
$builder->whereRaw('(?,?) in (?)', ['name', 'email', $str]);
// $builder->whereRaw('( ' . $criteria['whereRawIn'][0] . ' ) in (?)', [$str]);
8. I finally decided to give up this SQL and adjust it to another SQL format. The code is implemented as follows, generate SQL as follows, the program will no longer report errors when the program runs, and the query results are as expected. as shown in Figure 6
Figure 6
foreach ($criteria['names_emails'] as $customer) {
$builder->orWhere(function ($query) use ($customer) {
$query->where('name', $customer[0])
->where('email', $customer[1]);
});
}
SELECT
`id`,
`name`,
`email`,
`type`
FROM
`customers`
WHERE
( `name` = '客户姓名二' AND `email` = '客户姓名二' )
OR ( `name` = '客户姓名' AND `email` = '客户姓名' )
ORDER BY
`id` DESC
![使用 Composer 从 Yii 2.0.x 升级时，报错： [ErrorException] Undefined variable: nameMatchesRequiredPackage](https://www.shuijingwanwq.com/wp-content/uploads/2019/02/1-520x245.png)
