Wie finde ich Lücken bei der fortlaufenden Nummerierung in MySQL?

Dies funktionierte nur für mich, um die Lücken in einer Tabelle mit mehr als 80.000 Zeilen zu finden:

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;

Ergebnis:

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

Beachten Sie, dass die Reihenfolge der Spalten expected und got kritisch ist.

Wenn Sie wissen, dass YourCol nicht bei 1 beginnt und das keine Rolle spielt, können Sie ersetzen

(SELECT @rownum:=0) AS a

mit

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

Neues Ergebnis:

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

Wenn Sie eine Art Shell-Skriptaufgabe für die fehlenden IDs ausführen müssen, können Sie diese Variante auch verwenden, um direkt einen Ausdruck zu erzeugen, den Sie in bash iterieren können.

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 erwartet, IF (@rownum = height, 0, @rownum: = height) AS von (SELECT @rownum: = 0) AS ein JOIN-Block ORDER BY height) AS z WO z.got! = 0;

Dies erzeugt eine Ausgabe wie diese

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

Sie können es dann kopieren und in eine for-Schleife in einem Bash-Terminal einfügen, um für jede ID einen Befehl auszuführen

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

Es ist dasselbe wie oben, nur dass es lesbar und ausführbar ist. Durch Ändern des Befehls "CONCAT" oben kann die Syntax für andere Programmiersprachen generiert werden. Oder vielleicht sogar SQL.

Schnelle und schmutzige Abfrage, die den Trick ausführen sollte:

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

Dadurch erhalten Sie eine Tabelle mit der ID, über der IDs fehlen, und Next_id, die existiert, und wie viele zwischen ... fehlen.

  
 id next_id missing_inween zwischen 
 1 4 2 
 68 70 1 
 75 87 11 

Eine alternative Lösung, die eine Abfrage und etwas Code erfordert, der eine Verarbeitung durchführt, wäre:

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;

Beachten Sie, dass die Abfrage keine Unterauswahl enthält, von der wir wissen, dass sie vom MySQL-Planer nicht performant gehandhabt wird.

Das gibt einen Eintrag pro centralValue (cValue) zurück, der keinen kleineren Wert (lValue) oder einen höheren Wert (rValue) hat, dh:

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

Ohne auf weitere Details einzugehen (wir werden sie in den nächsten Absätzen sehen) bedeutet diese Ausgabe:

• Keine Werte zwischen 0 und 2
• Keine Werte zwischen 9 und 22
• Keine Werte zwischen 24 und 29
• Keine Werte zwischen 29 und 33
• Keine Werte zwischen 33 und MAX VALUE

Die Grundidee ist also, eine RECHTE und eine LINKE Verknüpfung mit derselben Tabelle durchzuführen, wobei zu sehen ist, ob wir benachbarte Werte pro Wert haben (dh: Wenn der zentrale Wert '3' ist), überprüfen wir 3-1 = 2 links und 3 + 1 at right), und wenn eine Zeile einen NULL-Wert bei RECHTS oder LINKS hat, wissen wir, dass es keinen benachbarten Wert gibt.

Die vollständige Rohausgabe meiner Tabelle ist:

select * from arrc_vouchers order by id asc;

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

Einige Notizen:

1. Die SQL-IF-Anweisung in der Join-Bedingung ist erforderlich, wenn Sie das Feld 'id' als UNSIGNED definieren. Daher können Sie es nicht unter Null verringern. Dies ist nicht unbedingt erforderlich, wenn Sie den Wert für c.value> 0 wie in der folgenden Anmerkung angegeben beibehalten. Ich füge ihn jedoch nur als doc hinzu.
2. Ich filtere den Nullmittelwert, da uns kein vorheriger Wert interessiert und wir den Postwert aus der nächsten Zeile ableiten können.

Erstellen Sie eine temporäre Tabelle mit 100 Zeilen und einer einzelnen Spalte mit den Werten 1-100.

Äußere Verknüpfen Sie diese Tabelle mit Ihrer arrc_vouchers-Tabelle und wählen Sie die einzelnen Spaltenwerte aus, bei denen die arrc_vouchers-ID null ist.

Das Codieren dieses Blinds sollte aber funktionieren.

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

basierend auf der Antwort von Lucek können Sie anhand dieser gespeicherten Prozedur die Tabellen- und Spaltennamen angeben, die Sie testen möchten, um nicht zusammenhängende Datensätze zu finden. So können Sie die ursprüngliche Frage beantworten und zeigen, wie Sie mit @var Tabellen &/oder Spalten in einer gespeicherten Prozedur.

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

Wenn Sie eine MariaDB verwenden, haben Sie eine schnellere (800%) Option

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

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

Obwohl dies alles zu funktionieren scheint, wird die Ergebnismenge bei 50.000 Datensätzen in einer sehr langen Zeit zurückgegeben.

Ich habe dies verwendet, und es findet die Lücke oder die nächste verfügbare (zuletzt verwendete + 1) mit einer viel schnelleren Rückkehr von der Abfrage.

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;

Sie können Erzeugungsserien verwenden, um Zahlen von 1 bis zur höchsten ID Ihrer Tabelle zu generieren. Führen Sie dann eine Abfrage aus, deren ID nicht in dieser Serie enthalten ist.

Wahrscheinlich nicht relevant, aber ich suchte nach etwas Ähnlichem, um die Lücken in einer Folge von Zahlen aufzulisten, und fand diesen Beitrag, der mehrere unterschiedliche Lösungen hat, je nachdem, wonach Sie genau suchen. Ich habe nach der ersten verfügbaren Lücke in der Sequenz gesucht (d. H. Nach der nächsten verfügbaren Nummer), und dies scheint in Ordnung zu sein.

SELECT MIN (l.number_sequence + 1) as nextavabile von Patienten as l LEFT OUTER JOIN Patienten as r on l.number_sequence + 1 = r.number_sequence WHERE r.number_sequence is NULL. Ab 2005 wurden dort noch einige andere Szenarien und Lösungen besprochen!

So finden Sie fehlende Werte in einer Sequenz mit SQL

Wenn es eine Sequenz gibt, die eine Lücke zwischen zwei Zahlen (wie 1,3,5,6) hat, dann kann folgende Abfrage verwendet werden:

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);

• tabellenname - source1
• spaltenname - id

Ich ausprobiert es auf verschiedene Arten und die beste Leistung, die ich fand, war diese einfache Abfrage:

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
;

... ein linker Join, um zu prüfen, ob die nächste ID vorhanden ist. Wird die nächste ID nicht gefunden, findet die Unterabfrage die nächste vorhandene ID finde das Ende der Lücke. Ich habe es getan, weil Abfrage mit gleich (=) eine bessere Leistung als ist größer als (>) Operator.

Mit der sqlfiddle zeigt es nicht so unterschiedliche Leistung von anderen Abfrage, aber in einer realen Datenbank diese Abfrage über Ergebnis 3-mal schneller als andere.

Das Schema:

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)
;

Befolgen Sie die folgenden Fragen, um die Leistung zu vergleichen:

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
;

Vielleicht hilft es jemandem und nützlich.

Sie können meine Abfrage mit diesem sqlfiddle sehen und testen:

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