【宋词】《玉楼春》 宋祁：东城渐觉风光好。皱波纹迎客棹。绿杨烟外晓寒轻，红杏枝头春意闹。浮生长恨欢娱少。肯爱千金轻一笑。为君持酒劝斜阳，

永夜

没有不值得去解决的问题，也没有不值得去学习的技术！

Troubleshooting Empty Query Results When Using IN Conditions on Multiple Columns in MySQL 8

In MySQL 8, when the IN condition is used on multiple fields, the query result is empty

作者：

永夜

在

, , , , , , , , ,
Views: 61

1. In MySQL 8, when the IN condition is used on multiple fields, the query result is empty. See Figure 1.

In MySQL 8, when the IN condition is used on multiple fields, the query result is empty
Figure 1
SQL

SELECT
	`id`,
	`name`,
	`email`,
	`type` 
FROM
	`customers` 
WHERE
	( 'name,email' ) IN ( '(\'客户姓名二\',\'44445@163.com\'),(\'客户姓名\',\'44445@163.com\')' ) 
ORDER BY
	`id` DESC

2. Reference: Using the IN condition on multiple fields in MySQL 8  The SQL that returns a non-empty query result is as follows:

SQL

SELECT * FROM `customers` WHERE ( NAME, email ) IN (( '123', '123@outlook.com' ),
( '我是客户姓名33', '1303842899@qq.com' ))

3. Reference: When using raw expressions for queries in Laravel 9, an error occurs: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;  I adjusted the code and printed the bound parameters, which matched expectations without any issues. See Figure 2.

Reference: When using raw expressions for queries in Laravel 9, an error occurs: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;
Figure 2
PHP

$str = '';
foreach ($criteria['whereRawIn'][1] as $key => $customer) {
	$str .= ($key == 0) ? '(\'' : ',(\'';
	$str .= implode('\',\'', $customer) . '\')';
}

var_dump($criteria['whereRawIn'][0]);
var_dump($str);
exit;
Plaintext
string(10) "name,email"
string(2959) "('客户姓名二','44445@163.com'),('客户姓名','44445@163.com')"

4. The adjusted code implementation is as follows:

PHP

            $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. At runtime, an error occurs: 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. See Figure 3.

At runtime, an error occurs: 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.
Figure 3

6. However, copying the error-reporting SQL into the database and executing it manually succeeds. See Figure 4.

However, copying the error-reporting SQL into the database and executing it manually succeeds
Figure 4

7. The adjusted code implementation is as follows. At runtime, an error occurs: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 2 column(s). However, copying the error-reporting SQL into the database and executing it manually succeeds. See Figure 5.

The adjusted code implementation is as follows. At runtime, an error occurs: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 2 column(s). However, copying the error-reporting SQL into the database and executing it manually succeeds
Figure 5
PHP

            $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 ultimately decided to abandon this SQL and switch to a different SQL format. The code implementation and generated SQL are as follows. The program no longer reports errors at runtime, and the query result meets expectations. See Figure 6.

I ultimately decided to abandon this SQL and switch to a different SQL format. The code implementation and generated SQL are as follows. The program no longer reports errors at runtime, and the query result meets expectations
Figure 6
PHP

foreach ($criteria['names_emails'] as $customer) {
	$builder->orWhere(function ($query) use ($customer) {
		$query->where('name', $customer[0])
			->where('email', $customer[1]);
	});
}
SQL

SELECT
	`id`,
	`name`,
	`email`,
	`type` 
FROM
	`customers` 
WHERE
	( `name` = '客户姓名二' AND `email` = '客户姓名二' ) 
	OR ( `name` = '客户姓名' AND `email` = '客户姓名' )
ORDER BY
	`id` DESC

关于作者 · 持续学习与技术实践

我是一名拥有 15 年以上开发经验的技术从业者，自 2013 年起持续运营个人技术博客，记录工作与个人项目中遇到的真实问题，以及解决问题过程中的探索、实践和思考。

除了技术写作，我也在持续开发和维护自己的独立项目，探索新技术和新工具在实际场景中的应用。我始终相信，没有不值得去解决的问题，也没有不值得去学习的技术。

如果这篇文章对你有所帮助，欢迎浏览博客中的其他文章，也欢迎交流技术经验与想法。

关于我  ·  GitHub  ·  邮件联系

更多文章