Comment trouver des lacunes dans la numérotation séquentielle dans mysql?

Ce juste travaillé pour moi de trouver les lacunes dans une table avec plus de 80k lignes:

SELECT
 CONCAT(z.expected, IF(z.got-1>z.expected, CONCAT(' thru ',z.got-1), '')) AS missing
FROM (
 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 z
WHERE z.got!=0;

Résultat:

+------------------+
| missing          |
+------------------+
| 1 thru 99        |
| 666 thru 667     |
| 50000            |
| 66419 thru 66456 |
+------------------+
4 rows in set (0.06 sec)

Notez que l'ordre des colonnes expected et got est critique.

Si vous savez que YourCol de ne pas commencer à 1 et qui n'a pas d'importance, vous pouvez remplacer

(SELECT @rownum:=0) AS a

avec

(SELECT @rownum:=(SELECT MIN(YourCol)-1 FROM YourTable)) AS a

Nouveau résultat:

+------------------+
| missing          |
+------------------+
| 666 thru 667     |
| 50000            |
| 66419 thru 66456 |
+------------------+
3 rows in set (0.06 sec)

Si vous avez besoin pour effectuer une sorte de script shell tâche sur les disparus Id, vous pouvez également utiliser cette variante, afin de produire directement une expression que vous pouvez parcourir en bash.

SELECT GROUP_CONCAT(IF(z.got-1>z.expected, CONCAT('$(',z.expected,' ',z.got-1,')'), z.expected) SEPARATOR " ") AS missing
FROM (  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;

Ce qui produit une sortie comme

$(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456)

Vous pouvez ensuite copier et coller dans une boucle for dans un bash terminal pour exécuter une commande pour chaque ID

for ID in $(seq 1 99) $(seq 666 667) 50000 $(seq 66419 66456); do
  echo $ID
  # fill the gaps
done

C'est la même chose que ci-dessus, seulement que c'est à la fois lisible et exécutable. En changeant le "CONCAT" commande ci-dessus, la syntaxe peut être généré pour d'autres langages de programmation. Ou peut-être même de SQL.

Si vous utilisez un MariaDB vous avez un plus rapide (800%) option à l'aide de la séquence moteur de stockage:

SELECT * FROM seq_1_to_50000 WHERE SEQ NOT IN (SELECT COL FROM TABLE);

Une solution alternative qui nécessite une requête + un peu de code en faisant un traitement serait:

select l.id lValue, c.id cValue, r.id rValue 
  from 
  arrc_vouchers l 
  right join arrc_vouchers c on l.id=IF(c.id > 0, c.id-1, null)
  left  join arrc_vouchers r on r.id=c.id+1
where 1=1
  and c.id > 0 
  and (l.id is null or r.id is null)
order by c.id asc;

Remarque que la requête ne contiennent pas de sous-sélection que nous savons qu'il n'est pas manipulé performantly par MySQL planner.

Qui sera de retour une entrée par centralValue (cValue) qui n'ont pas une valeur plus petite (lValue) ou une plus grande valeur (valeur r), c'est à dire:

lValue |cValue|rValue
-------+------+-------
{null} | 2    | 3      
8      | 9    | {null} 
{null} | 22   | 23     
23     | 24   | {null} 
{null} | 29   | {null} 
{null} | 33   | {null} 

Sans entrer dans les détails (nous le verrons dans les paragraphes suivants) cette sortie signifie que:

• Pas de valeurs entre 0 et 2
• Pas de valeurs entre le 9 et le 22
• Pas de valeurs entre le 24 et le 29
• Pas de valeurs comprises entre 29 et 33
• Pas de valeurs entre 33 et VALEUR MAX

Donc l'idée de base est de faire du DROIT et GAUCHE rejoint à la même table pour voir si nous avons adjacents valeurs par la valeur (c'est à dire: si la valeur centrale est '3', puis nous vérifier 3-1=2, à gauche et 3+1 à droite), et lorsqu'une LIGNE a une valeur NULL à DROITE ou à GAUCHE alors nous savons qu'il n'est pas adjacent valeur.

Le raw complet sortie de ma table est:

select * from arrc_vouchers order by id asc;

0  
2  
3  
4  
5  
6  
7  
8  
9  
22 
23 
24 
29 
33 

Quelques remarques:

1. SQL SI l'instruction dans la condition de jointure est nécessaire si vous définissez le champ 'id' non signés, par conséquent, il ne vous permettra pas de le diminuer en-dessous de zéro. Ce n'est pas strictement nécessaire si vous gardez le c.valeur > 0 comme il est indiqué dans la note suivante, mais je suis, y compris comme doc.
2. Je suis un filtrage du zéro valeur centrale que nous ne sommes pas intéressés par toute valeur précédente et on peut en déduire la valeur post à partir de la ligne suivante.

Si il y a une séquence d'avoir d'écart maximum entre deux numéros (comme
1,3,5,6), alors la requête qui peut être utilisé est:

select s.id+1 from source1 s where s.id+1 not in(select id from source1) and s.id+1<(select max(id) from source1);

• table_name - source1
• column_name - id

Je essayé de différentes manières et de la meilleure performance que j'ai découvert cette requête simple:

select a.id+1 gapIni
    ,(select x.id-1 from arrc_vouchers x where x.id>a.id+1 limit 1) gapEnd
    from arrc_vouchers a
    left join arrc_vouchers b on b.id=a.id+1
    where b.id is null
    order by 1
;

... un left join pour vérifier si la prochaine id existe, uniquement si s'est pas trouvé, alors la sous-requête de trouver la prochaine id qui existe pour trouver la fin de gap. Je l'ai fait car requête avec l'égalité des (=) est de meilleures performances que plus de (>) de l'opérateur.

À l'aide de la sqlfiddle il pas voir si les différentes performances des autres requête, mais dans une vraie base de données cette requête ci-dessus 3 fois plus rapide que les autres.

Le schéma:

CREATE TABLE arrc_vouchers (id int primary key)
;
INSERT INTO `arrc_vouchers` (`id`) VALUES (1),(4),(5),(7),(8),(9),(10),(11),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29)
;

Suivre soufflet toutes les requêtes que j'ai effectué pour comparer la performance:

select a.id+1 gapIni
,(select x.id-1 from arrc_vouchers x where x.id>a.id+1 limit 1) gapEnd
from arrc_vouchers a
left join arrc_vouchers b on b.id=a.id+1
where b.id is null
order by 1
;
select *, (gapEnd-gapIni) qt
from (
select id+1 gapIni
,(select x.id from arrc_vouchers x where x.id>a.id limit 1) gapEnd
from arrc_vouchers a
order by id
) a where gapEnd <> gapIni
;
select id+1 gapIni
,(select x.id from arrc_vouchers x where x.id>a.id limit 1) gapEnd
#,coalesce((select id from arrc_vouchers x where x.id=a.id+1),(select x.id from arrc_vouchers x where x.id>a.id limit 1)) gapEnd
from arrc_vouchers a
where id+1 <> (select x.id from arrc_vouchers x where x.id>a.id limit 1)
order by id
;
select id+1 gapIni
,coalesce((select id from arrc_vouchers x where x.id=a.id+1),(select x.id from arrc_vouchers x where x.id>a.id limit 1)) gapEnd
from arrc_vouchers a
order by id
;
select id+1 gapIni
,coalesce((select id from arrc_vouchers x where x.id=a.id+1),concat('*** GAT *** ',(select x.id from arrc_vouchers x where x.id>a.id limit 1))) gapEnd
from arrc_vouchers a
order by id
;

Peut-être que ça aide quelqu'un et utile.

Vous pouvez voir et tester ma requête à l'aide de cette sqlfiddle:

http://sqlfiddle.com/#!9/6bdca7/1