湖上湖
这对我来说只是找到一个超过80k行的表中的空白:SELECT CONCAT(z.expected, IF(z.got-1>z.expected, CONCAT(' thru ',z.got-1), '')) AS missingFROM ( SELECT @rownum:=@rownum+1 AS expected, IF(@rownum=YourCol, 0, @rownum:=YourCol) AS got FROM (SELECT @rownum:=0) AS a JOIN YourTable ORDER BY YourCol ) AS zWHERE z.got!=0;结果:+------------------+| missing |+------------------+| 1 thru 99 || 666 thru 667 || 50000 || 66419 thru 66456 |+------------------+4 rows in set (0.06 sec)请注意,列的顺序expected和got是至关重要的。如果您知道YourCol不是从1开始并且无关紧要,则可以替换(SELECT @rownum:=0) AS a同(SELECT @rownum:=(SELECT MIN(YourCol)-1 FROM YourTable)) AS a新结果:+------------------+| missing |+------------------+| 666 thru 667 || 50000 || 66419 thru 66456 |+------------------+3 rows in set (0.06 sec)如果您需要对缺少的ID执行某种shell脚本任务,您还可以使用此变体来直接生成可以在bash中迭代的表达式。SELECT GROUP_CONCAT(IF(z.got-1>z.expected, CONCAT('$(',z.expected,' ',z.got-1,')'), z.expected) SEPARATOR " ") AS missingFROM ( SELECT @rownum:=@rownum+1 AS expected, IF(@rownum=height, 0, @rownum:=height) AS got FROM (SELECT @rownum:=0) AS a JOIN block ORDER BY height ) AS z WHERE z.got!=0;这会产生类似的输出$(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456)然后,您可以将其复制并粘贴到bash终端中的for循环中,以便为每个ID执行命令for ID in $(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456); do echo $ID # fill the gapsdone它与上面的内容相同,只是它既可读又可执行。通过更改上面的“CONCAT”命令,可以为其他编程语言生成语法。或者甚至可能是SQL。