mysql - 从 where 子句中的给定集合中选择表中不存在的 ID

Question

我有一组用户 ID:(512,5,13,​​14,67) 以及包含以下内容的表格:

+----+--------+
| Id | userID |
+----+--------+
|  1 |    512 |
|  2 |     13 |
|  3 |     14 |
|  4 |     51 |
|  5 |      6 |
+----+--------+

集合中的某些用户 ID 不存在于表中。例如。用户 ID 5 和用户 ID 64 不存在。

当我执行 Select * from mytable where userID NOT IN (512,5,13,​​14,67) 时，它将显示用户 ID 为 6 和 51

+----+--------+
| Id | userID |
+----+--------+
|  4 |     51 |
|  5 |      6 |
+----+--------+

我想做这样的事情:
SELECT userID FROM my TABLE WHERE NOT EXISTS IN (1,5,10,15)

并得到结果:

+-------+--------+
|  Id   | userID |
+-------+--------+
| NULL  |      5 |
| NULL  |     64 |
+-------+--------+

我的表包含数百万行，搜索集可能包含 1000 个要搜索的 ID。

Answer 1

如果我正确理解你的问题，你正在寻找。

SELECT 
   NULL AS id 
 , search_filter.userID
FROM (
  SELECT 
   5 AS userID
  UNION
  SELECT
    64 AS userID
  # [...]
) AS search_filter
LEFT JOIN
 your_table
ON
 search_filter.userID = your_table.userID
WHERE 
 your_table.userID IS NULL

The search set could be 1000 ids so I cannot execute the query like that.

然后使用 CSV 搜索列表，该列表将由嵌套的 SUBSTRING_INDEX() 函数解析，并结合 SQL 数字生成器是您唯一也是最好的选择。

SELECT 
   NULL AS id 
 , search_filter.userID
FROM (
  SELECT 
   DISTINCT 
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(init_search.search_list, ',', sql_number_generator.number), ',', -1
    ) AS userID
FROM (

SELECT
  @number := @number + 1 AS number
FROM (
  (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) row1
  CROSS JOIN
  (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) row2
  CROSS JOIN
  (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) row3 
  CROSS JOIN
  (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) row4    
  CROSS JOIN 
  (SELECT @number:=0) AS init_user_params
)  
) AS sql_number_generator
CROSS JOIN (
 SElECT '512,5,13,14,67' AS search_list 
) AS init_search
) AS search_filter
LEFT JOIN
 your_table
ON
 search_filter.userID = your_table.userID
WHERE 
 your_table.userID IS NULL

结果

| id  | userID |
| --- | ------ |
|     | 5      |
|     | 67     |

参见 demo