查找不连续的主键

心已赠人 2022-12-17 04:24 152阅读 0赞

查找不连续的主键id，数据量很小：

SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
        FROM vip_des AS a, vip_des AS b
        WHERE a.id < b.id and a.id<100000 and b.id<100000
        GROUP BY a.id
        HAVING start < MIN(b.id)

不需要的列直接用@过滤掉就可以了(可以使用同一个变量名比如@a，或者使用我下面不同的变量名加上@符号)

load data infile 'test.txt' into table table_name (c1，@2, c3，@4, c5，@6， c7 ，@8，c9，@10);

### 相关的脚本： ###

/** * mysqldump批量读入数据 * @author Bruce 2020/10/21 */
        public function testNumbers()
        { 
            $number = 1111111111;
            $interval = 500000;
            $file_name = ' /tmp/vip_des.sql';
            for ($i = 1; $i < 100; $i++) { 
                $samll = $number - $interval * $i;
                if ($samll > 0) { 
                    $big = $samll + $interval;
                    echo sprintf('mysqldump -h xxx.xxx.rds.aliyuncs.com -u xxx-p database tables --where="id>=%s and id<%s" >%s', $samll, $big, $file_name) . PHP_EOL;
                }
            }
        }
    
        /** * 检查不存在的id * @author Bruce 2020/10/21 * @return bool */
        public function checkNotExist()
        { 
            file_put_contents(EASYSWOOLE_ROOT . '/Log/not_exist.txt', '');
            $number = 11111111;
            $intrval = 10000;
            for ($i = 0; $i < $number; $i++) { 
                $small = $i * $intrval;
                $big = $small + $intrval - 1;
                $arr = range($small, $big);
                if ($big > $number) { 
                    return $this->writeJson(sprintf('已完成,大值%s,小值%s', $big, $small));
                }
                $ids = VipdeModel::create()->where('id', [$small, $big], 'between')->column('id');
                $str = '';
                if (empty($ids)) { 
                    $str = implode(',', $arr);
                } elseif (count($ids) != $intrval) { 
                    $str = implode(',', array_diff($arr, $ids));
                }
                if ($str) { 
                    file_put_contents(EASYSWOOLE_ROOT . '/Log/not_exist.txt', $str . ',', FILE_APPEND);
                }
                usleep(100);
            }
        }