[MySQL] AutoInc fehlende Einträge auffühlen

[gsh]

Hi DPler

Ich hab ein Problem mit meiner Datenbank. Ich möchte nämlich das die id nummer in meiner MyISAM Tabllen wenn eine neue Zeile dazu kommt die id werte auffüllt.

Also wenn ich in meiner Datenbank die Werte hab:

Delphi-Quellcode:

id
---
1
2
3
5
6
8

So jetzt soll aber bei einem insert nicht die id 9 vergeben werden sonderen 4. Dannach 7 und so halt. Aber wie sag ich des er des tun soll wenn auf id auf AutoInc steht?

[mkinzler]

Kannst du nur manuell machen, autoinc erhöht der höchsten Wert im 1.

[gsh]

hmm hab ich fast befürchtet
und wie mach ich des am besten mit einem SQL Befehl?

[mkinzler]

Mit einem einfachen SQL-Statement wirst du das nicht hinkriegen.
Warum willst du die Lücken auffüllen?
Gibt es FK anderen Tabellen auf die Tabelle?Wenn nicht könntest du die Tabelle neu nummerieren.

[gsh]

Zitat:

Zitat von mkinzler

Mit einem einfachen SQL-Statement wirst du das nicht hinkriegen.

Des muss doch irgendwie gehen

Zitat:

Zitat von mkinzler

Warum willst du die Lücken auffüllen?

Weil die ids nicht zu groß werden sollten. Und es werden ja meistens nicht viele zeilen in der tabelle sein aber sie ändern sich täglich vllt. 100-500 mal (ändern heisst nicht UPDATE sondern DELETE und INSERT (Alles verschiedene Zeilen))

Zitat:

Zitat von mkinzler

Gibt es FK anderen Tabellen auf die Tabelle?Wenn nicht könntest du die Tabelle neu nummerieren.

FK? meinst du Fremdschlüssel ja und die kann ich nicht neu einstellen da sie vllt. schon auf einem Blat papier stehen. Also die ids dürfen nicht verändert werden solange sie gültig sind. D.h. neu nummerieren kommt auf keinen fall in frage

[OG Karotte]

Du kannst es evtl. so versuchen:

Delphi-Quellcode:

    
 i := 0;
 repeat
   i := i + 1; // Nächste ID
   qry.Active := False;
   qry.SQL.Text := 'SELECT id FROM tbl_mit_ids WHERE id = ' + IntToStr(i); // ID abfragen
   qry.Active := True;
   s := qry.FieldByName('id').AsString;
 until s = ''; // Abbruch wenn ID noch nicht vorhanden

Im Ereignis OnNewRecord der Tabelle kannst Du dann den Wert aus 'i' als neue ID verwenden.

Natürlich muss zuvor das Attribut 'AutoInc' aus der entspr. Tabelle entfernt werden, denn sonst kannst Du das nicht manuell setzen.

Gruß
OG Karotte

[gsh]

hmm ja diese möglicheit hab ich mir schon überlegt aber dann müssen unter umständen 2000 Queris ausgeführt werden
geht es optimierter?

[Jürgen Thomas]

Hallo,

ich habe folgende Idee (ungetestet):

SQL-Code:

SELECT ID FROM Tabelle
  WHERE (ID+1) NOT IN (SELECT ID FROM Tabelle)

Voraussetzung ist, dass ID+1 in der Where-Klausel zulässig ist und MySQL solche verschachtelten Abfragen kennt.

Bei einer Schleife: Wäre es passender, sie als StoredProcedure in die DB zu legen (vorausgesetzt, es gibt dort Schleifen)? Vorgehen etwa so (in Pseudo-Firebird):

SQL-Code:

DECLARE VARIABLE ActualID INTEGER;
DECLARE VARIABLE PrevID INTEGER;
DECLARE VARIABLE Diff INTEGER;
PrevID = 0;
FOR SELECT ID FROM Tabelle INTO :ActualID
DO BEGIN
  Diff = ActualID - PrevID;
  IF (Diff > 1)
  THEN BEGIN
    NextID = PrevID + 1;
    BREAK;
  END
END
SUSPEND

RETURN-Wert wäre dann NextID, und das kann sofort verwendet werden.

Viel Erfolg! Jürgen

[OG Karotte]

@gsh:

Zitat:

Zitat von gsh

hmm ja diese möglicheit hab ich mir schon überlegt aber dann müssen unter umständen 2000 Queris ausgeführt werden
geht es optimierter?

Soll heißen, das Du derzeit ca. 2000 Datensätze in der Tabelle hast?
Sollte aber nichts ausmachen, wenn die Abfrage auf einer einigermaßen aktuellen Maschine läuft und die Netzwerklast nicht zu groß ist.

Und es werden ja nicht nur Datensätze am Ende gelöscht, oder?
Sonst kannst Du Dich ja auch von hinten nach vorn durcharbeiten...

Gruß
OG Karotte

[gsh]

Zitat:

Zitat von Jürgen Thomas

ich habe folgende Idee (ungetestet):

SQL-Code:

SELECT ID FROM Tabelle
  WHERE (ID+1) NOT IN (SELECT ID FROM Tabelle)

Voraussetzung ist, dass ID+1 in der Where-Klausel zulässig ist und MySQL solche verschachtelten Abfragen kennt.

Japp MySQL scheint sie zu unterstüzen

Aber es sind noch zwei kleine fehler:
So funkt sie komplett:

SQL-Code:

SELECT id+1 FROM test WHERE (id+1) NOT IN (SELECT id FROM test) LIMIT 1

1. am anfang id+1 da er sonst alle ids eins unter den fehlenden einträge gefunden hat.
2. LIMIT 1 am schluss weil sonst findet er alle Lücken und ich hoffe einfach mal damit das er weniger rechenen muss wenn er nur eine sucht.

Das schöne an der Funktion ... sie liefert immer ein ergebnis. Auch wenn keine lücken sind dann wird einfach die letzte id +1 ausgegeben.


VIELEN DANK Jürgen

[Jürgen Thomas]

Freut mich! Das "Limit 1" hatte ich im Kopf, aber beim Schreiben wieder vergessen. (Bei FB hätte es "SELECT FIRST Id" geheißen.)

Dass es auch ohne Lücken ein richtiges Ergebnis liefert, wundert mich, aber es ist natürlich schön. Jürgen

[gsh]

Zitat:

Zitat von Jürgen Thomas

Dass es auch ohne Lücken ein richtiges Ergebnis liefert, wundert mich, aber es ist natürlich schön

Was solls ich finds gut :mrgreen:

[Jürgen Thomas]

Nur der Vollständigkeit halber (für Interessenten, die das später einmal lesen):

Zitat:

Zitat von Jürgen Thomas

Dass es auch ohne Lücken ein richtiges Ergebnis liefert, wundert mich, aber es ist natürlich schön.

Es hat in der Tat seine Richtigkeit. Beispiel:

Code:

Vorhandene IDs: 1,2,3,4,5
Das "äußere" Select würde liefern: 2,3,4,5,6

Die where-Klausel zeigt, dass nur 6 eine zulässige Antwort ist. Jürgen

@Mod
Es handelt sich bei diesem Thema zwar nicht um eine FAQ. Aber vielleicht ist die Frage "Lücken in IDs finden" als praktischer Tipp für Datenbanken geeignet? Wo könnte man es zusammenfassend eintragen?

[gsh]

Zitat:

Zitat von Jürgen Thomas

@Mod
Es handelt sich bei diesem Thema zwar nicht um eine FAQ. Aber vielleicht ist die Frage "Lücken in IDs finden" als praktischer Tipp für Datenbanken geeignet? Wo könnte man es zusammenfassend eintragen?

Das könnte man doch in die Code-Libary packen? :zwinker:

[Jelly]

Ich würd den SQL Befehl eventuell noch ein Order verpassen, um auch die kleinst mögliche Lücke zu finden:

SQL-Code:

SELECT id+1 FROM test WHERE (id+1) NOT IN (SELECT id FROM test) order by id LIMIT 1

[Jürgen Thomas]

Zitat:

Zitat von gsh

Das könnte man doch in die Code-Libary packen? :zwinker:

Stimmt, habe ich jetzt gemacht (mit Jellys Tipp). Jürgen

[gsh]

Zitat:

Zitat von Jürgen Thomas

Stimmt, habe ich jetzt gemacht (mit Jellys Tipp). Jürgen

gut :-D