SQL Auto-Increment aktualisieren

[chinamaschiene]

Hallo,

ich habe eine Tabelle in der ich nachträglich eine auto_increment id eingefügt habe für jeden eintrag. also von 1-100 z.b.
wenn ich jetzt z.b. eintrag 99 lösche will ich das danach die ids aktualisiert werden so das es nur noch die ids 1-99 gibt. also so das ich zu jedem datensatz eine id habe und es keine fehlenden ids gibt

kann mir da jemand sagen wie das geht?

vielen dank schonmal :-)

Ergänzung (8. Oktober 2009)

hat sich erledigt. für die die es interesiert: das geht mit:

ALTER TABLE table1 DROP id;
ALTER TABLE table1 ADD id INT(3) NOT NULL AUTO_INCREMENT KEY FIRST;

jetzt würde ich aber auch noch gerne die tabelle alphabetisch nach einer spalte sortieren können und dann die nummern neu setzen. geht das auch?

 

[Rossibaer]

Theoretisch gibt es da sicher eine Lösung für das Problem.
z.B. Könntest du eine "temporäre" Tabelle erzeugen die alle Spalten der Quelltabelle hat, dann mit

INSERT INTO tempTabelle(Spalte1,Spalte2,Spalte3) SELECT Spalte1,Spalte2,Spalte3 from sourceTabelle

die Daten rüber kopieren. Danach die sourceTabelle droppen, Tabelle neu erstellen, dann mit

INSERT INTO neueTabelle(Spalte1,Spalte2,Spalte3) SELECT Spalte1,Spalte2,Spalte3 from tempTabelle ORDER BY Spalte1 ASC

sortiert wieder zurück übertragen und zum Schluß die temp Tabelle droppen.

Die Frage ist jedoch, warum du das machen möchtest, denn es ist nicht ratsam ID Spalten immer wieder neu zu befüllen bzw. zu reorganisieren. Das Problem ist, das du dir damit sehr leicht eine inkonsistente Datenbank erzeugst, wenn die Werte der ID Spalte in weiteren Tabellen zur Herstellung von Beziehungen verwendet werden. Stichwort Foreign Keys / Primary Keys. So kann es sein das du, z.B. in einem theoretischen "Verkaufssystem" die Rechnungsposten eines Auftrages schnell mal einem anderen Auftrag zu ordnest oder Rechnungsposten auf einmal gar keinem Auftrag mehr angehören. Im Allgemeinen sollte die Datenbank zwar dies verhindern in dem entsprechende Meldungen dein Vorgehen dann als "Fehler" quittieren. Aber es gibt Mittel und Wege auch das zu umgehen. Alles in allem ist es keine gute Praxis. Was willst du eigentlich machen? Vielleicht gibt es eine Alternative zu deinem bisherigen Weg, der eben nicht die Integrität der Daten verletzt und trotzdem das gleiche Ziel erreicht.

PS: Geht es um eine MySQL Datenbank?

 

[chinamaschiene]

wow. vielen dank für deine ausführliche antwort. mit der lösung bin ich zufrieden. super!
das mit der konsistenz habe ich gar nicht gedacht. die tabelle wird auch nur privat verwendet und (noch nicht?) übergreifend. es ist eine liste von firewall ausnahmeregeln (ip, hostname, port, comment, datum, enabled usw.) die von einem python skript ausgelesen werden um sie dann an iptables zu übergeben. (ich weiß das es auch iptables-save gibt...) das ist ne schleife die von id 1 bis id X läuft. anders weiß ich nicht wie ich alle datensätze nacheinander kriegen soll.

vorher habe ich das mit bash und tools wie sed aus einer textdatei gelesen. entsprechend langsam war das natürlich. daher habe ich das jetzt lieber mit mysql gemacht. verwende das aber zum ersten mal.

nochmal danke für den tip

 

[BerniG]

Unter der Annahme dass du nach der Spalte t2 sortieren willst:

ALTER TABLE table1 DROP id;
UPDATE table1 t1 SET id = (SELECT count(*) FROM table1 t2 WHERE t1.text <= t2.text);
ALTER TABLE table1 ADD id INT(3) NOT NULL AUTO_INCREMENT KEY FIRST;

Edit: Und wieso machst du bei der Abfrage der Datenbank nicht einfach ein entsprechendes "order by"? Hochzählen kannst du ja auch in Python in der Schleife, die das Ergebnis abholt...

 

[Rossibaer]

Hab mal noch ein bisschen gegoogelt und das hier gefunden: http://forums.mysql.com/read.php?61,402,99378#msg-99378

Sieht so aus, das du dann die ID so lassen kannst, wie sie ist und stattdessen dein SELECT Befehl etwas abänderst um hier eine Nummerierung der Datensätze zu erreichen. Sollte das nicht den Trick tun, dann würde ich mich BerniG anschließen und clientseitig, sprich im Python Skript, beim Fetchen der Datensätze einfach eine Variable mit hochzählen. Siehe BerniG letzten Edit. Beide Varianten sollten um einiges schneller sein als diese Kopier, Drop und Update Aktionen.

Also jetzt zähle ich schon 4 Varianten, wie man das Ziel erreichen kann...

Viel Erfolg!