检查表中不存在集合中的ID [英] Check which IDs from a set do not exists in a table

问题描述

我有一个用户ID列表，如下所示:

I have a list of user IDs, like this:

757392,733602,749955,744304,746852,753904,755117,636163,564931,740787,751450,743799,643918,
749903,571888,30207,705953,749120,749001,749192,749978,750840,544228,702121,746246,383667,
558790,585628,592771,745818,749375,241209,749235,746860,748318,748016,748951,747321,748684,
748225,565375,748673,747869,748522,748335,744775,672229,578056,713127,740234,632608,711135,
746528,362131,742223,746567,745224,332989,439837,745418,673582,269584,742606,745135,746950,
476134,740830,742949,276934

我有一个带有id字段的MySQL表users.

I have a MySQL table users with the id field.

如何使用查询检查users表中是否存在我没有的那些ID?

How do I check - using a query - which IDs of the ones I have do not exists in the users table?

这对我来说似乎是一个简单的问题，但是我在StackOverflow上找不到任何可以解决一组固定ID值的示例.

This sounds like a simple problem to me, yet I couldn't find any example on StackOverflow which would address a fixed set of ID values.

推荐答案

这是一个选择:

SELECT ids.id
  FROM ( SELECT @i
              , substring(@string, @start, @end-@start) id
            FROM <BigTable>
               , ( SELECT @string := <YourStringOfIds>
                        , @start:=0
                        , @end:=0
                        , @i:=0
                        , @len:=length(@string)
                        , @n:=@len-length(replace(@string,',',''))+1
                 ) t
            WHERE (@i := @i+1) <= @n
              AND (@start := @end+1)
              AND (@loc := locate(',',@string,@start))
              AND @end := if(@loc!=0,@loc,@len+1)
       ) ids
    LEFT JOIN <BigTable> u
      ON u.id = ids.id
  WHERE u.id is null

BigTable可以是行数> =字符串中ID数的任何表.

BigTable can be any table whose number of rows >= number of ids in your string.

这篇关于检查表中不存在集合中的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！