MySQL - 检查同一 IN 子句中是否有多个值

Question

有没有一种方法可以检查同一个 in 子句中是否存在多个值，而无需重写相同的 IN 子句？

示例

Employee Table:
+----+--------------+---------------+-------------+
| id |  first_name  |  middle_name  |  last_name  |
+----+--------------+---------------+-------------+
| 1  |     Ian      |    Daniel     | de Villiers |
| 2  |     Karien   |               | Tolmie      |
| 3  |     John     |    Peter      | Green       |
| 4  |     Daniel   |    Silie      | von Guns    |
| 5  |     Francois |    Roos       | Krans       |
+----+--------------+---------------+-------------+

假设我想要所有名字、中间名或姓氏为 Daniel 或 Peter 的员工 - 因此，我想要 id 1、3 和 4。

我知道如何做到这一点的唯一方法是:

SELECT id 
FROM   employees 
WHERE  ( first_name IN ( "Daniel", "Peter" ) ) 
        OR ( middle_name IN ( "Daniel", "Peter" ) ) 
        OR ( last_name IN ( "Daniel", "Peter" ) ) 

如果我有多个值要测试或 IN 子句中的值过多，这可能会很长。

我已经尝试过

SELECT id 
FROM   employees 
WHERE  ( first_name OR middle_name OR last_name IN ( "Daniel", "Peter" ) ) 

但我认为这会导致first_name被解释为 bool 值。

Answer 1

从 MySQL 8.0 开始，您可以使用 JSON_OVERLAPS()比较使用 JSON_ARRAY() 创建的两个 JSON 文档。如果两个文档具有任何共同的键值对或数组元素，则这样做将返回 true。

如果两个参数都是标量，该函数将执行简单的相等测试。

因此您引用的代码看起来类似于:

SELECT
    `id` 
FROM
    `employees` 
WHERE
    JSON_OVERLAPS (
        JSON_ARRAY( "Daniel", "Peter" ),
        JSON_ARRAY( `first_name`, `middle_name`, `last_name` )
    );

具体来说，当比较两个数组时，如果它们共享一个或多个数组元素，JSON_OVERLAPS() 将返回 true，如果不共享，则返回 false:

mysql> SELECT JSON_OVERLAPS(JSON_ARRAY("Daniel", "Peter"), JSON_ARRAY("Ian", "Daniel", "de Villiers"));
+---------------------------------------+
| JSON_OVERLAPS(JSON_ARRAY("Daniel", "Peter"), JSON_ARRAY("Ian", "Daniel", "de Villiers")) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS(JSON_ARRAY("Daniel", "Peter"), JSON_ARRAY("John", "Peter", "Green"));
+---------------------------------------+
| JSON_OVERLAPS(JSON_ARRAY("Daniel", "Peter"), JSON_ARRAY("John", "Peter", "Green")) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS(JSON_ARRAY("Daniel", "Peter"), JSON_ARRAY("Francois", "Roos", "Krans"));
+---------------------------------------+
| JSON_OVERLAPS(JSON_ARRAY("Daniel", "Peter"), JSON_ARRAY("Francois", "Roos", "Krans")) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)