mysql:检查数据库表中是否存在数组的值

Question

我有一个 PHP 数字数组 (0,1,2,3,4,5)，我想检查其中哪些不在在我的数据库表中。

我怎样才能做这样的事情:

SELECT num FROM (0,1,2,3,4,5) AS num WHERE num NOT IN (SELECT id FROM sometable);

我正在询问正确的 SQL 语法。

Answer 1

create table sometable (num int not null);
insert into sometable values (1),(1),(4);

解决方案:

create temporary table tmp (num int not null);
insert into tmp values (0),(1),(2),(3),(4),(5);
select t.num from tmp t left join sometable s on t.num=s.num where s.num is null;

或者

select t.num from tmp t where t.num not in (select num from sometable);

输出:

+-----+
| num |
+-----+
|   2 |
|   3 |
|   5 |
+-----+