查找不连续的主键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);
相关的脚本：
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;
}
}
}
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);
}
}
