Come trovare spazi nella numerazione sequenziale in mysql?

Abbiamo un database con una tabella i cui valori sono stati importati da un altro sistema. C’è una colonna di autoincremento e non ci sono valori duplicati, ma ci sono valori mancanti. Ad esempio, eseguendo questa query:

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

dovrebbe restituire 100, ma restituisce 87 invece. C’è una query che posso eseguire che restituirà i valori dei numeri mancanti? Ad esempio, i record possono esistere per ID 1-70 e 83-100, ma non ci sono record con ID di 71-82. Voglio restituire 71, 72, 73, ecc.

È ansible?

Aggiornare

ConfexianMJS ha fornito una risposta molto migliore in termini di prestazioni.

La risposta (non il più veloce ansible)

Ecco la versione che funziona su una tabella di qualsiasi dimensione (non solo su 100 righe):

 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 – primo id nel gap corrente
  • gap_ends_at – ultimo ID nel gap corrente

Questo ha funzionato solo per me per trovare le lacune in una tabella con più di 80k righe:

 SELECT CONCAT(z.expected, IF(z.got-1>z.expected, CONCAT(' thru ',z.got-1), '')) AS missing FROM ( SELECT @rownum:[email protected]+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; 

Risultato:

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

Si noti che l’ordine delle colonne expected e got è fondamentale.

Se sai che YourCol non parte da 1 e non importa, puoi sostituire

 (SELECT @rownum:=0) AS a 

con

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

Nuovo risultato:

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

Se è necessario eseguire una sorta di attività di script della shell sugli ID mancanti, è anche ansible utilizzare questa variante per produrre direttamente un’espressione da iterare in 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 previsto, IF (@ rownum = altezza, 0, @rownum: = altezza) AS ottenuto da FROM (SELECT @rownum: = 0) COME blocco JOIN ORDER BY height) AS z DOVE z.got! = 0;

Questo produce un risultato come questo

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

È quindi ansible copiarlo e incollarlo in un ciclo for in un terminale bash per eseguire un comando per ogni ID

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

È la stessa cosa di cui sopra, solo che è sia leggibile che eseguibile. Modificando il comando “CONCAT” qui sopra, la syntax può essere generata per altri linguaggi di programmazione. O forse anche SQL.

Query veloce e sporca che dovrebbe fare il trucco:

 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 

Questo ti darà una tabella che mostra l’id che ha gli ID mancanti sopra, e next_id che esiste, e quanti mancano tra … ad es.

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

Creare una tabella temporanea con 100 righe e una singola colonna contenente i valori 1-100.

Esterno Unisci questa tabella alla tabella arrc_vouchers e seleziona i singoli valori di colonna in cui l’id di arrc_vouchers è nullo.

Coding questo cieco, ma dovrebbe funzionare.

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

Una soluzione alternativa che richiede una query + un codice che elabora un po ‘di elaborazione sarebbe:

 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; 

Si noti che la query non contiene alcuna sottoselezione che sappiamo che non è gestita in modo performante dal pianificatore di MySQL.

Ciò restituirà una voce per valore centrale (cValue) che non ha un valore inferiore (lValore) o un valore maggiore (rValue), cioè:

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

Senza entrare in ulteriori dettagli (li vedremo nei prossimi paragrafi) questo output significa che:

  • Nessun valore tra 0 e 2
  • Nessun valore tra 9 e 22
  • Nessun valore tra 24 e 29
  • Nessun valore tra 29 e 33
  • Nessun valore compreso tra 33 e VALORE MAX

Quindi l’idea di base è di fare un join RIGHT e LEFT con la stessa tabella, vedendo se abbiamo valori adiacenti per valore (es .: se il valore centrale è ‘3’, allora controlliamo per 3-1 = 2 a sinistra e 3 + 1 a a destra), e quando una RIGA ha un valore NULL a DESTRA o A SINISTRA allora sappiamo che non esiste un valore adiacente.

L’output raw completo della mia tabella è:

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

Alcune note:

  1. L’istruzione SQL IF nella condizione di join è necessaria se si definisce il campo ‘id’ come UNSIGNED, pertanto non consente di ridurlo sotto zero. Questo non è strettamente necessario se si mantiene il valore c.> 0 come indicato nella prossima nota, ma lo includo proprio come doc.
  2. Sto filtrando il valore centrale dello zero perché non siamo interessati a nessun valore precedente e possiamo ricavare il valore del post dalla riga successiva.

Se stai usando un MariaDB hai un’opzione più veloce (800%)

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

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

in base alla risposta fornita da Lucek, questa procedura memorizzata consente di specificare i nomi di tabella e colonna che si desidera testare per trovare record non contigui, rispondendo così alla domanda originale e dimostrando come si possa usare @var per rappresentare tabelle e / o colonne in una stored procedure.

 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 

Anche se tutto sembra funzionare, il set di risultati ritorna in un tempo molto lungo quando ci sono 50.000 record.

L’ho usato e trova il gap o il successivo disponibile (ultimo utilizzato + 1) con un ritorno molto più rapido dalla query.

 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; 

Puoi utilizzare generare serie per generare numeri da 1 all’ID più alto della tabella. Quindi esegui una query dove id non in questa serie.

Se c’è una sequenza con gap massimo tra due numeri (come 1,3,5,6), la query che può essere utilizzata è:

 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
  • nome_colonna - id

Questo potrebbe non funzionare in MySQL, ma al lavoro (Oracle) avevamo bisogno di qualcosa di simile.

Abbiamo scritto un processo memorizzato che ha preso un numero come valore massimo. Lo Stored Proc ha quindi creato una tabella temporanea con una singola colonna. La tabella conteneva tutti i numeri da 1 a Max. Poi ha fatto un NON IN join tra il tavolo temporaneo e il nostro tavolo di interesse.

Se lo hai chiamato con Max = Select max (id) da arrc_vouchers, restituirebbe tutti i valori mancanti.