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

Question

Nous avons une base de données avec une table dont les valeurs ont été importés à partir d'un autre système. Il s'agit d'une auto-incrémentée, et il n'y a pas de doublons, mais il y a des valeurs manquantes. Par exemple, l'exécution de cette requête:

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

doit retourner 100, mais il renvoie 87 au lieu de cela. Est-il une requête je peux courir qui retourne les valeurs des nombres manquants? Par exemple, les enregistrements peuvent exister pour id 1-70 et 83-100, mais il n'y a pas d'enregistrements avec l'identité de 71-82. Je veux retourner 71, 72, 73, etc.

Est-ce possible?

Answer 1

Voici la version qui fonctionne sur le tableau de taille quelconque (et pas seulement sur les 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 - id du premier dans le fossé actuel
• gap_ends_at - dernier id dans le fossé actuel

Answer 2

Rapide et Sale requête 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 indiquant l'id a id manquant au-dessus d'elle, et next_id qui existe, et combien sont manquantes entre...par exemple

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

Answer 3

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;

Notez que la requête ne contient aucun 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:

• Aucune des valeurs entre 0 et 2
• Pas de valeurs entre le 9 et le 22
• Pas de valeurs entre le 24 et le 29
• Aucune des 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 une valeur voisine.

Le raw complet sortie 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 remarques:

1. Le 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.

Answer 4

Créer une table temporaire avec 100 lignes et une seule colonne contenant les valeurs de 1 à 100.

Jointure externe ce tableau à votre arrc_vouchers de la table et de sélectionner la colonne des valeurs où le arrc_vouchers id est null.

Ce codage aveugle, mais devrait fonctionner.

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

Answer 5

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

Nous avons écrit une procédure Stockée qui a eu un certain nombre que la valeur Max. La procédure Stockée a ensuite créé une table temporaire avec une seule colonne. La table contient tous les chiffres de 1 à Max. Puis il a fait un PAS DANS la jointure entre la table temporaire et notre table d'intérêts.

Si vous l'avez appelé avec Max = Select max(id) from arrc_vouchers, il serait alors retourner toutes les valeurs manquantes.