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

Nous avons une firebase database avec une table dont les valeurs ont été imscopes d’un autre système. Il y a une colonne à incrémentation automatique et il n’y a pas de valeurs en double, mais il y a des valeurs manquantes. Par exemple, en exécutant cette requête:

select count(id) from arrc_vouchers where id between 1 and 100 

devrait retourner 100, mais il retourne 87 à la place. Existe-t-il une requête que je peux exécuter pour retourner les valeurs des numéros manquants? Par exemple, les enregistrements peuvent exister pour les identifiants 1-70 et 83-100, mais il n’y a pas d’enregistrements avec les identifiants 71-82. Je veux retourner 71, 72, 73, etc.

Est-ce possible?

Mettre à jour

ConfexianMJS a fourni une réponse bien meilleure en termes de performances.

La réponse (pas aussi rapide que possible)

Voici la version qui fonctionne sur des tables de toutes tailles (pas seulement sur 100 lignes):

 SELECT (t1.id + 1) as gap_starts_at, (SELECT MIN(t3.id) -1 FROM arrc_vouchers t3 WHERE t3.id > t1.id) as gap_ends_at FROM arrc_vouchers t1 WHERE NOT EXISTS (SELECT t2.id FROM arrc_vouchers t2 WHERE t2.id = t1.id + 1) HAVING gap_ends_at IS NOT NULL 
  • gap_starts_at – premier identifiant dans l’intervalle actuel
  • gap_ends_at – dernier identifiant dans l’intervalle actuel

Cela a juste fonctionné pour moi pour 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 ne commence pas à 1 et que cela 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 devez effectuer une sorte de tâche de script shell sur les ID manquants, vous pouvez également utiliser cette variante pour produire directement une expression que vous pouvez itérer 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 attendu, IF (@ rownum = height, 0, @rownum: = height) AS obtenu FROM (SELECT @rownum: = 0) AS un bloc JOIN ORDER BY height) AS z WHERE z.got! = 0;

Cela produit une sortie comme ça

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

Vous pouvez ensuite le copier et le coller dans une boucle for dans un terminal bash 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 modifiant la commande “CONCAT” ci-dessus, la syntaxe peut être générée pour d’autres langages de programmation. Ou peut-être même SQL.

Requête rapide et sale qui devrait faire l’affaire:

 SELECT a AS id, b AS next_id, (b - a) -1 AS missing_inbetween FROM ( SELECT a1.id AS a , MIN(a2.id) AS b FROM arrc_vouchers AS a1 LEFT JOIN arrc_vouchers AS a2 ON a2.id > a1.id WHERE a1.id <= 100 GROUP BY a1.id ) AS tab WHERE b > a + 1 

Cela vous donnera un tableau montrant l’ID avec les identifiants manquants au-dessus, et next_id qui existe, et combien sont manquants entre … par exemple

 
 id next_id missing_inbetween
  1 4 2
 68 70 1
 75 87 11

Créez une table temporaire avec 100 lignes et une seule colonne contenant les valeurs 1-100.

Extérieur Reliez cette table à votre table arrc_vouchers et sélectionnez les valeurs de colonne unique où l’ID arrc_vouchers est null.

Coder cet aveugle, mais devrait fonctionner.

 select tempid from temptable left join arrc_vouchers on temptable.tempid = arrc_vouchers.id where arrc_vouchers.id is null 

Une autre solution nécessitant une requête + un code effectuant 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; 

Notez que la requête ne contient aucune sous-sélection dont nous soaps qu’elle n’est pas gérée efficacement par le planificateur MySQL.

Cela retournera une entrée par centralValue (cValue) qui n’a pas une valeur plus petite (lValue) ou une valeur supérieure (rValue), 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 plus de détails (nous les verrons dans les paragraphes suivants), cette sortie signifie que:

  • Aucune valeur entre 0 et 2
  • Aucune valeur entre 9 et 22
  • Aucune valeur entre 24 et 29
  • Aucune valeur entre 29 et 33
  • Aucune valeur entre 33 et MAX VALUE

Donc, l’idée de base est de faire des jointures RIGHT et LEFT avec la même table en vérifiant si nous avons des valeurs adjacentes par valeur (c.-à-d. Si la valeur centrale est «3», nous vérifions 3-1 = 2 à gauche et 3 + 1 à droite), et quand un ROW a une valeur NULL à RIGHT ou LEFT, alors nous soaps qu’il n’y a pas de valeur adjacente.

La sortie brute complète de ma table est la suivante:

 select * from arrc_vouchers order by id asc; 0 2 3 4 5 6 7 8 9 22 23 24 29 33 

Quelques notes:

  1. L’instruction SQL IF dans la condition de jointure est nécessaire si vous définissez le champ ‘id’ comme UNSIGNED, par conséquent, il ne vous permettra pas de le diminuer sous zéro. Ce n’est pas ssortingctement nécessaire si vous conservez la valeur c> 0 comme cela est indiqué dans la note suivante, mais je l’inclus simplement en tant que doc.
  2. Je filtre la valeur centrale zéro car nous ne sums intéressés par aucune valeur précédente et nous pouvons en déduire la valeur de la ligne suivante.

Si vous utilisez un MariaDB vous avez une option plus rapide (800%)

 SELECT * FROM seq_1_to_50000 where seq not in (select col from table); 

https://mariadb.com/kb/en/mariadb/sequence/

sur la base de la réponse donnée ci-dessus par Lucek, cette procédure stockée vous permet de spécifier les noms de table et de colonne à tester pour trouver des enregistrements non contigus – répondant ainsi à la question initiale et expliquant comment utiliser @var pour représenter des tables / ou colonnes dans une procédure stockée.

 create definer=`root`@`localhost` procedure `spfindnoncontiguous`(in `param_tbl` varchar(64), in `param_col` varchar(64)) language sql not deterministic contains sql sql security definer comment '' begin declare strsql varchar(1000); declare tbl varchar(64); declare col varchar(64); set @tbl=cast(param_tbl as char character set utf8); set @col=cast(param_col as char character set utf8); set @strsql=concat("select ( t1.",@col," + 1 ) as starts_at, ( select min(t3.",@col,") -1 from ",@tbl," t3 where t3.",@col," > t1.",@col," ) as ends_at from ",@tbl," t1 where not exists ( select t2.",@col," from ",@tbl," t2 where t2.",@col," = t1.",@col," + 1 ) having ends_at is not null"); prepare stmt from @strsql; execute stmt; deallocate prepare stmt; end 

Bien que tout cela semble fonctionner, le jeu de résultats revient dans une très longue période où il y a 50 000 enregistrements.

Je l’ai utilisé, et il trouve l’écart ou le prochain disponible (dernier utilisé + 1) avec un retour beaucoup plus rapide de la requête.

 SELECT a.id as beforegap, a.id+1 as avail FROM table_name a where (select b.id from table_name b where b.id=a.id+1) is null limit 1; 

Vous pouvez utiliser generate series pour générer des nombres de 1 à l’id le plus élevé de votre table. Exécutez ensuite une requête où id n’est pas dans cette série.

S’il existe une séquence ayant un écart de maximum un entre deux nombres (comme 1,3,5,6), la requête pouvant être utilisée est la suivante:

 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); 
  • nom_table - source1
  • nom_colonne - id

Cela peut ne pas fonctionner dans MySQL, mais au travail (Oracle) nous avions besoin de quelque chose de similaire.

Nous avons écrit un processus stocké qui a pris un nombre comme valeur maximale. Le processus stocké a ensuite créé une table temporaire avec une seule colonne. La table contenait tous les nombres de 1 à Max. Ensuite, une jointure NOT IN a été établie entre la table temporaire et notre table d’intérêt.

Si vous l’appeliez avec Max = Select max (id) de arrc_vouchers, toutes les valeurs manquantes seraient renvoyées.