获取给定数据在数据库中不存在的数据.

产品给出了一批手机号,让我验证那些不是我们平台的用户.
那些是我们平台的用户,好查询,那些不是可就难坏我了.

 

方案1: 经典方式.

拼接union select 创造多行数据,然后左链接判空进行数据去除

select a.userId from (
select "6000002088921683" as userId
union select "6000002092355494"
union select "6000002093134388"
union select "........."

)a 
left join "我的业务表" on "我的业务表".user_id = a.userId and "我的筛选条件"
where "我的业务表".user_id is null;

 

方案2: 优雅的拆分为多行数据左链接去空

通过系统表mysql.help_topic 协助substring_index拆分为多行最多拆分585条.(可自建自增表则无限制)

SELECT
    b.userId
FROM
    (
        SELECT
            substring_index(
                substring_index(
                    a.tempId,
                    ',',
                    b.help_topic_id + 1
                ),
                ',' ,- 1
            ) AS userId
        FROM
            (
                # 存在局限性,单次最多处理585条数据.拆分为行.
                SELECT
                    "6000002088921683,6000002092355494,6000002093134388,......" AS tempId
            ) a
        JOIN mysql.help_topic b ON b.help_topic_id < (
            length(a.tempId) - length(REPLACE(a.tempId, ',', '')) + 1
        )

    ) b
LEFT JOIN "我的业务表" ON "我的业务表".user_id = b.userId
AND "我的筛选条件"
WHERE
    "我的业务表".user_id IS NULL

方案3: 通过repalce方式去除多余数据.(书写简单,数据量大慎用)

SET @temp = "6000002088921683,6000002092355494,6000002093134388,......,";

SELECT
    @temp := REPLACE (
        @temp,
        concat("我的业务表".user_id, ","),
        ""
    )FROM
    "我的业务表"
WHERE
    "我的筛选条件";
SELECT
    @temp AS userId;

通过这种方式,第一个输出量会比较大对我们没有实际意义,可以采用如下方式屏蔽无效输出导致的网络传输.

SET @temp = "6000002088921683,6000002092355494,6000002093134388,......,";

SELECT DISTINCT
    id
FROM
    (
        SELECT
            @temp := REPLACE (
                @temp,
                concat("我的业务表".user_id, ","),
                ""
            ),
            '我是占位符结果看下条'  AS id
        FROM
            "我的业务表"

        WHERE
            "我的筛选条件"

    ) a;

SELECT
    @temp as userId;

 

 

 

 

select * from (select "08e093101a4d4cfc822bfba2fdfc298e" as userIdunion select "562a8929c880428f"union select "6000000002435543"union select "6000000008773677"union select "6000000011896418"union select "6000000013629744"union select "6000000017362841"union select "6000000018063797"union select "6000000026762593"union select "6000000039970549"union select "6000000049960327"union select "6000000052120275"union select "6000000060937645"union select "6000000075192797"union select "6000000075394978"union select "6000000076980334"union select "6000000078571249"union select "6000000081089699"union select "6000000082380074"union select "6000000090884633"union select "6000000091314487"union select "6000000100277641"union select "6000000108085131"union select "6000000115744803"union select "6000000119729587"union select "6000000124798444"union select "6000000132693959"union select "6000000136395991"union select "6000000140551764"union select "6000000148438782"union select "6000000162970884"union select "6000000164870761"union select "6000000165487541"union select "6000000166315376"union select "6000000179112582"union select "6000000188053814"union select "6000000190027797"union select "6000000207742976"union select "6000000214090069"union select "6000000236153506"union select "6000000238592908"union select "6000000240630860"union select "6000000240926199"union select "6000000245980849"union select "6000000246411725"union select "6000000258125514"union select "6000000262001703"union select "6000000266772460"union select "6000000271256828"union select "6000000272294242"union select "6000000286013175"union select "6000000286920936"union select "6000000287128017"union select "6000000290510839"union select "6000000291870661"union select "6000000295806292"union select "6000000303048422"union select "6000000307456960"union select "6000000307564945"union select "6000000307579317"union select "6000000315355564"union select "6000000317171395"union select "6000000323512743"union select "6000000323758464"union select "6000000325352587"union select "6000000325433203"union select "6000000327044691"union select "6000000327604415"union select "6000000338896950"union select "6000000341503843"union select "6000000345233781"union select "6000000345926144"union select "6000000346515442"union select "6000000353796861"union select "6000000355646299"union select "6000000362485541"union select "6000000362741883"union select "6000000368840944"union select "6000000372294041"union select "6000000378758128"union select "6000000381145097"union select "6000000381404721"union select "6000000384741611"union select "6000000385348691"union select "6000000387848283"union select "6000000390177686"union select "6000000391344381"union select "6000000391771338"union select "6000000394425391"union select "6000000404221783"union select "6000000414332569"union select "6000000417219297"union select "6000000426213443"union select "6000000428487887"union select "6000000431257156"union select "6000000437432049"union select "6000000438195838"union select "6000000438786396"union select "6000000452736842"union select "6000000454794793"union select "6000000460818084"union select "6000000466545953"union select "6000000467979995"union select "6000000477408080"union select "6000000480505698"union select "6000000480905729"union select "6000000485125859"union select "6000000486652591"union select "6000000487161957"union select "6000000489012814"union select "6000000489904442"union select "6000000491516444"union select "6000000495893906"union select "6000000496491909"union select "6000000498984161"union select "6000000507732292"union select "6000000512875857"union select "6000000519152662"union select "6000000520220921"union select "6000000522605277"union select "6000000530207583"union select "6000000539240308"union select "6000000540451901"union select "6000000540638498"union select "6000000551384658"union select "6000000561659915"union select "6000000567430096"union select "6000000568531234"union select "6000000573043407"union select "6000000573571591"union select "6000000575375490"union select "6000000575800227"union select "6000000581436716"union select "6000000599835265"union select "6000000605538700"union select "6000000608170729"union select "6000000609141375"union select "6000000611619710"union select "6000000614038961"union select "6000000627352721"union select "6000000627881379"union select "6000000630185689"union select "6000000633548797"union select "6000000633903265"union select "6000000637023698"union select "6000000639510007"union select "6000000658049188"union select "6000000663425729"union select "6000000667973597"union select "6000000670451534"union select "6000000671479677"union select "6000000672718106"union select "6000000681311969"union select "6000000699284452"union select "6000000709136823"union select "6000000709392240"union select "6000000710324070"union select "6000000717477463"union select "6000000718921522"union select "6000000721587869"union select "6000000728711983"union select "6000000732038122"union select "6000000734746961"union select "6000000739016140"union select "6000000740922942"union select "6000000754053043"union select "6000000754285369"union select "6000000754403138"union select "6000000757570056"union select "6000000765446982"union select "6000000770353142"union select "6000000774427337"union select "6000000775609374"union select "6000000777635394"union select "6000000778283754"union select "6000000780178903"union select "6000000782710496"union select "6000000790372117"union select "6000000796864908"union select "6000000803069957"union select "6000000805719335"union select "6000000809160255"union select "6000000814200765"union select "6000000815759814"union select "6000000817494576"union select "6000000821213390"union select "6000000824908090"union select "6000000827037713"union select "6000000828589503"union select "6000000833208894"union select "6000000842493208"union select "6000000844260647"union select "6000000846373349"union select "6000000846392613"union select "6000000849547668"union select "6000000850342333"union select "6000000853813105"union select "6000000854602662"union select "6000000857101147"union select "6000000861442818"union select "6000000864797943"union select "6000000865466419"union select "6000000866625011"union select "6000000871886509"union select "6000000872593270"union select "6000000883052105"union select "6000000886051421"union select "6000000888595331"union select "6000000892596641"union select "6000000897588423"union select "6000000928920377"union select "6000000929552082"union select "6000000941716046"union select "6000000942200995"union select "6000000962265284"union select "6000000962370683"union select "6000000973675971"union select "6000000982030020"union select "6000000991663511"union select "6000000997622495"union select "6000001004860868"union select "6000001021309202"union select "6000001027690281"union select "6000001035214868"union select "6000001036714279"union select "6000001055238200"union select "6000001059910465"union select "6000001061806093"union select "6000001068310576"union select "6000001072212055"union select "6000001073502529"union select "6000001080786832"union select "6000001088737386"union select "6000001089461528"union select "6000001092702535"union select "6000001096536907"union select "6000001098625699"union select "6000001103301360"union select "6000001107607095"union select "6000001109202484"union select "6000001112550545"union select "6000001116739999"union select "6000001116751046"union select "6000001121201893"union select "6000001123041862"union select "6000001129114722"union select "6000001129400821"union select "6000001137153064"union select "6000001137167388"union select "6000001146709189"union select "6000001147531720"union select "6000001147735024"union select "6000001154145833"union select "6000001155241264"union select "6000001159725652"union select "6000001160291283"union select "6000001165212674"union select "6000001166782276"union select "6000001168807053"union select "6000001176464257"union select "6000001179768903"union select "6000001180327017"union select "6000001184054010"union select "6000001189357922"union select "6000001199953987"union select "6000001199983506"union select "6000001201510577"union select "6000001204980259"union select "6000001208702564"union select "6000001215983723"union select "6000001221565257"union select "6000001227785843"union select "6000001235273815"union select "6000001235977689"union select "6000001237453586"union select "6000001240253917"union select "6000001245533676"union select "6000001245618771"union select "6000001250101341"union select "6000001256517400"union select "6000001256753885"union select "6000001262697731"union select "6000001262753453"union select "6000001274866158"union select "6000001280835198"union select "6000001285605783"union select "6000001289599808"union select "6000001290499341"union select "6000001291289321"union select "6000001292232866"union select "6000001306305377"union select "6000001310263306"union select "6000001322545369"union select "6000001325254068"union select "6000001333570981"union select "6000001333995788"union select "6000001334956322"union select "6000001339755923"union select "6000001340465078"union select "6000001344141906"union select "6000001345496991"union select "6000001347784740"union select "6000001351880140"union select "6000001353110580"union select "6000001357641093"union select "6000001361194711"union select "6000001361865566"union select "6000001365067223"union select "6000001371359934"union select "6000001372506484"union select "6000001374720785"union select "6000001375394307"union select "6000001376096078"union select "6000001376317881"union select "6000001380744222"union select "6000001383265249"union select "6000001383386196"union select "6000001386011632"union select "6000001389497685"union select "6000001394340758"union select "6000001406620477"union select "6000001409312245"union select "6000001409940572"union select "6000001416122603"union select "6000001423132895"union select "6000001427884224"union select "6000001432542611"union select "6000001432650380"union select "6000001433355639"union select "6000001444175415"union select "6000001445465953"union select "6000001456006747"union select "6000001462680412"union select "6000001467197922"union select "6000001473892476"union select "6000001474159307"union select "6000001474981894"union select "6000001476327726"union select "6000001477838562"union select "6000001489598484"union select "6000001496321659"union select "6000001500026227"union select "6000001501945073"union select "6000001502565949"union select "6000001506604059"union select "6000001509075017"union select "6000001517972943"union select "6000001518538610"union select "6000001518835515"union select "6000001523238944"union select "6000001533310806"union select "6000001538560161"union select "6000001545289781"union select "6000001553338300"union select "6000001556220716"union select "6000001557761738"union select "6000001559397393"union select "6000001570503951"union select "6000001574740013"union select "6000001576345962"union select "6000001578764221"union select "6000001594354409"union select "6000001603659710"union select "6000001610111891"union select "6000001611556250"union select "6000001614960910"union select "6000001630363362"union select "6000001632729789"union select "6000001633732800"union select "6000001634103154"union select "6000001637484886"union select "6000001647048578"union select "6000001650798774"union select "6000001660846055"union select "6000001670221685"union select "6000001674502530"union select "6000001685159626"union select "6000001688800091"union select "6000001698971725"union select "6000001702259515"union select "6000001703127403"union select "6000001704577945"union select "6000001710576477"union select "6000001715305924"union select "6000001718155472"union select "6000001718976841"union select "6000001721239739"union select "6000001725677896"union select "6000001729082726"union select "6000001729515603"union select "6000001729813137"union select "6000001743746033"union select "6000001748311275"union select "6000001756443124"union select "6000001761348828"union select "6000001773829607"union select "6000001779231121"union select "6000001788458654"union select "6000001794613115"union select "6000001802937012"union select "6000001804482446"union select "6000001807451351"union select "6000001809000074"union select "6000001815627517"union select "6000001817547023"union select "6000001825287375"union select "6000001829676292"union select "6000001831012994"union select "6000001834106265"union select "6000001837102167"union select "6000001868281066"union select "6000001869998076"union select "6000001870062300"union select "6000001881072036"union select "6000001896107383"union select "6000001896531152"union select "6000001900101290"union select "6000001903839856"union select "6000001903882187"union select "6000001905192981"union select "6000001917707020"union select "6000001925181362"union select "6000001932185927"union select "6000001933286872"union select "6000001933532485"union select "6000001938551680"union select "6000001942218335"union select "6000001951847270"union select "6000001954955039"union select "6000001955688847"union select "6000001956711549"union select "6000001962264417"union select "6000001963046455"union select "6000001964204972"union select "6000001974588810"union select "6000001976738174"union select "6000001980520662"union select "6000001985717626"union select "6000001994738784"union select "6000002001264025"union select "6000002002166277"union select "6000002006628272"union select "6000002015221536"union select "6000002015423236"union select "6000002016427816"union select "6000002023825897"union select "6000002029667907"union select "6000002034081143"union select "6000002036998656"union select "6000002046084423"union select "6000002046349580"union select "6000002057496934"union select "6000002060803296"union select "6000002063187630"union select "6000002066917242"union select "6000002067805713"union select "6000002078042895"union select "6000002084455478"union select "6000002086340650"union select "6000002086603374"union select "6000002088921683"union select "6000002092355494"union select "6000002093134388"union select "6000002102203182"union select "6000002114810965"union select "6000002117131068"union select "6000002124117568"union select "6000002125488635"union select "6000002126232006"union select "6000002127182073"union select "6000002135237853"union select "6000002138021387"union select "6000002142595198"union select "6000002143055399"union select "6000002143067913"union select "6000002144492155"union select "7889005cac6247ad"union select "b9a9483c6eb04e1c"union select "bb065350005042c7"union select "db5402032c4a4741a5021fefb026d170")a left join t_bind_vehicle on t_bind_vehicle.user_id = a.userId and bind_status = 1where t_bind_vehicle.user_id is null;