Ich habe eine MySQL-Tabelle mit einer autoincrement-id-spalte. da ich jedoch manchmal zeilen aus der Tabelle lösche, entstehen dann Lücken in der id-Folge. Ich könnte die gesammte Tabelle mit einem Script durchlaufen und die Zeilen nachrücken. Aber mySql speichert den zuletzt vergebenen autoincrement-Wert und macht bei der nächsten zahl weiter. Ich müsste also den autoincrement-Folgewert zurücksetzen.(in phpMyAdmin nennt er sich „nächster autoincrement-Wert“) Ich hab es mit ALTER tabelle1 SET AUTOINCREMENT=7 versucht, aber das ging nicht.
Wer kann helfen?
Hi Johannes,
Das TABLE fehlt, SET braucht es nicht, AUTO_INCREMENT mit Unterstrich.
ALTER TABLE tabelle1 AUTO\_INCREMENT=7
Gruß,
Rudy
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
nur so nebenbei:
Was hat das mit PHP zu tun? 
–
Gruss
Dirk
http://phpmybackuppro.net/mozilla_search_plugins/
Jetzt habe ich mir gerade mal ueberlegt, wann man eigentlich den auto increment Wert aendert oder zureuecksetzt.
Mir ist dazu nur beim Entwicklen oder Debuggen eingefallen.
Eigentlich ist diese Funktion bei MySQL doch dafuer da, sicherzustellen, dass auf keinen Fall versucht wird einen Eintrag anzulegen mit einer ID, die schon existiert. Sobald man den autoincrement Wert allerdings aendert ist das nicht mehr garantiert.
Aendert man den auto increment Wert im laufenden Betrieb der db, kann man doch gleich die Ids selbst setzten, oder?
Kennt jemand noch andere Einsatzgebiete?
–
Gruss
Dirk
http://phpmybackuppro.net/mozilla_search_plugins/
Jetzt habe ich mir gerade mal ueberlegt, wann man eigentlich
den auto increment Wert aendert oder zureuecksetzt.Mir ist dazu nur beim Entwicklen oder Debuggen eingefallen.
Eigentlich ist diese Funktion bei MySQL doch dafuer da,
sicherzustellen, dass auf keinen Fall versucht wird einen
Eintrag anzulegen mit einer ID, die schon existiert. Sobald
man den autoincrement Wert allerdings aendert ist das nicht
mehr garantiert.
Aendert man den auto increment Wert im laufenden Betrieb der
db, kann man doch gleich die Ids selbst setzten, oder?Kennt jemand noch andere Einsatzgebiete?
Hi Dirk,
Wenn man mit MySQL-Tabellen vom Typ MyISAM arbeitet, wird der AUTO_INCREMENT-Zähler (natürlich nur einer pro Tabelle) nicht automatisch zurückgesetzt. Wenn Du beispielsweise 10000 Werte mit einer AUTO_INCREMENT-ID hast, dann ein DELETE FROM TABELLE WHERE ID>999 durchführst, ist der nächste Wert, den ID beim Insert bekommt, 10001. Dies ist aber nicht sinnvoll, da ID nur mehr 1000 groß sein muss, so hat das Ändern des Auto-Increment Werts den Zweck, die ID klein zu halten. Das kann auch während des Betriebs geschehen, nicht nur beim Debuggen - natürlich mit Bedacht wegen möglicher doppelter Schlüssel, wie Du richtig erwähnt hast. Wenn man im Programm nun die IDs von vornherein selbst setzen will, dann ist der Auto-Increment etwas überflüssig, denn das sollte er ersparen. Programme für Tabellen mit AUTO_INCREMENT-IDs sind meist gar nicht darauf ausgelegt, eine ID selbst einzufügen, sondern setzen, wenn überhaupt, nur an kritischen Stellen (nach Löschungen) den AUTO_INCREMENT-Wert wieder zurecht. Ich gehöre noch zu der altmodischen Sorte, der das nicht gefällt und selektiere deshalb meine Tabellen-IDs vor den Inserts mit IFNULL(MAX(ID)+1, 1).
Schönen Gruß,
Rudy
Wenn man mit MySQL-Tabellen vom Typ MyISAM arbeitet, wird der
AUTO_INCREMENT-Zähler (natürlich nur einer pro Tabelle) nicht
automatisch zurückgesetzt. Wenn Du beispielsweise 10000 Werte
mit einer AUTO_INCREMENT-ID hast, dann ein DELETE FROM TABELLE
WHERE ID>999 durchführst, ist der nächste Wert, den ID beim
Insert bekommt, 10001. Dies ist aber nicht sinnvoll, da ID nur
mehr 1000 groß sein muss, so hat das Ändern des Auto-Increment
Werts den Zweck, die ID klein zu halten. Das kann auch während
des Betriebs geschehen, nicht nur beim Debuggen - natürlich
mit Bedacht wegen möglicher doppelter Schlüssel, wie Du
richtig erwähnt hast. Wenn man im Programm nun die IDs von
vornherein selbst setzen will, dann ist der Auto-Increment
etwas überflüssig, denn das sollte er ersparen. Programme für
Tabellen mit AUTO_INCREMENT-IDs sind meist gar nicht darauf
ausgelegt, eine ID selbst einzufügen, sondern setzen, wenn
überhaupt, nur an kritischen Stellen (nach Löschungen) den
AUTO_INCREMENT-Wert wieder zurecht. Ich gehöre noch zu der
altmodischen Sorte, der das nicht gefällt und selektiere
deshalb meine Tabellen-IDs vor den Inserts mit
IFNULL(MAX(ID)+1, 1).Schönen Gruß,
Rudy
Ok, das koennte ein Grund sein. Doch eigentlich ist es doch egal, wie gross die ID ist (zumindest in diesen Groessenordnungen - ich weiss gerade nicht auswendig wieviele Stellen die ID haben darf)
–
Gruss
Dirk
18.446.744.073.709.551.615
Ok, das koennte ein Grund sein. Doch eigentlich ist es doch
egal, wie gross die ID ist (zumindest in diesen
Groessenordnungen - ich weiss gerade nicht auswendig wieviele
Stellen die ID haben darf)
Wenn Dein ID-Feld INT ist, darf es maximal 32 Bit haben. Ohne Vorzeichenbit, also nur Werte >= 0, sind das 11111111111111111111111111111111 ~ 4294967295, mit Vorzeichen nur 31 Bit. Mit BIGINT ist es dasselbe, nur ist der maximale absolute Wert 64 Bit lang, ein positiver Wert über 18 Trillionen.
Aber wer braucht schon so hohe IDs? 
Sie sollte immer so klein wie möglich sein. Da liegen dann sonst irgendwann 2 Datensätze in der DB, der eine mit ID 1, der andere mit ID 32409854, nur weil der Zähler nie zurückgesetzt wurde
Schönen Gruß,
Rudy
Kriegstn Stern
.
Hi!
Aber wer braucht schon so hohe IDs?
klein wie möglich sein. Da liegen dann sonst irgendwann 2
Datensätze in der DB, der eine mit ID 1, der andere mit ID
32409854, nur weil der Zähler nie zurückgesetzt wurdeSchönen Gruß,
Rudy
Kurze Frage:
Der Autoincrement wird gesetzt, weil man dadurch einen eindeutigen Schlüssel hat (so habe ich es jedenfalls weiter oben verstanden - ansonsten würde der Autoincrement auch kaum Sinn machen).
Wenn der Wert des Autoincrements in einer Tabelle zurückgesetzt wird, wie sieht es dann mit der Referentiellen Integrität zu anderen Tabellen aus? Korrigieren die sich von alleine oder ist dann alles im Eimer?
Grüße
Heinrich
Hi!
Kurze Frage:
Der Autoincrement wird gesetzt, weil man dadurch einen
eindeutigen Schlüssel hat (so habe ich es jedenfalls weiter
oben verstanden - ansonsten würde der Autoincrement auch kaum
Sinn machen).Wenn der Wert des Autoincrements in einer Tabelle
zurückgesetzt wird, wie sieht es dann mit der Referentiellen
Integrität zu anderen Tabellen aus? Korrigieren die sich von
alleine oder ist dann alles im Eimer?
Hallo Heinrich,
Für die Wahrung der referentiellen Integrität gibt es bei MySQL Foreign-Key Constraints, die bei Deletes und Updates greifen. Das aber nur beim Tabellentyp INNODB, bei MyISAM gibt es keine Fremdschlüssel, da musst Du für die Integrität der Daten mit einer guten Programmierung sorgen. Die Obergrenze des Autoincrement-Werts für eine Tabelle hat nur in zweiter Linie was mit der Integrität der Daten zu tun. Du kannst nie davon ausgehen, dass zwei Tabellen den Zähler immer auf dem gleichen Stand haben und dann von Integrität sprechen. Datensatzbezüge werden immer bei Deletes oder Updates auf den Primärschlüssel abgemurkst, an diesen Stellen ist es wichtig, verbundene Datensätze in anderen Tabellen nicht verwaisen zu lassen, sprich Fremdschlüsselfeld mit aktualisieren, untergeordnete Datensätze löschen.
Schönen Gruß,
Rudy
Wenn Dein ID-Feld INT ist, darf es maximal 32 Bit haben. Ohne
Vorzeichenbit, also nur Werte >= 0, sind das
11111111111111111111111111111111 ~ 4294967295, mit Vorzeichen
nur 31 Bit. Mit BIGINT ist es dasselbe, nur ist der maximale
absolute Wert 64 Bit lang, ein positiver Wert über 18
Trillionen.
Aber wer braucht schon so hohe IDs?
klein wie möglich sein. Da liegen dann sonst irgendwann 2
Datensätze in der DB, der eine mit ID 1, der andere mit ID
32409854, nur weil der Zähler nie zurückgesetzt wurdeSchönen Gruß,
Rudy
Genau das hab ich vermutet. Man kann auf jeden Fall ein paar Milliarden Stellen verschenken.
Aber ich habe immer noch nicht kapiert, was so schlimm daran ist eine ID 1 und eine Id 32409854 zu haben. Das kann mir doch absolut wurscht sein. Beide Ids funktionieren auf die selbe Art und Weisse…
–
Gruss
Dirk
http://www.phpmybackuppro.net/mozilla_search_plugins
Genau das hab ich vermutet. Man kann auf jeden Fall ein paar
Milliarden Stellen verschenken.
Aber ich habe immer noch nicht kapiert, was so schlimm daran
ist eine ID 1 und eine Id 32409854 zu haben. Das kann mir doch
absolut wurscht sein. Beide Ids funktionieren auf die selbe
Art und Weise…
Ein System soll darauf ausgerichtet sein, möglichst lange zu funktionieren. Wenn Du den Zähler nie zurücksetzt, kannst Du nur soviele verschiedene IDs generieren, wie die Zählervariable es erlaubt. Die ID wird bei jedem Insert hochgesetzt, wenn der auto_increment-Wert generiert wird. Bei Löschvorgängen ohne Rücksetzen sind dann horrende Unterschiede bei den Werten der IDs bei zugleich wenig Datensätzen das Resultat. Eine normale Abfrage nach der nächsthöheren ID (MAX(ID)+1) ergibt dann wieder einen sehr hohen Wert. Diese Löcher will man bei der ID-Vergabe verhindern, gelingt aber leider nicht immer. Ein blöder Fall wäre z.B. wenn viele Datensätze eingefügt werden, anschließend alle bis auf den letzten Datensatz gelöscht werden, und danach wieder sehr viele Datensätze eigefügt würden, alle bis auf den letzten gelöscht, usw. Die ID würde so sehr hoch werden, auch wenn man die größte ID heraussucht und den Zähler auf diesen Stand setzt. Hier hat autoincrement auch die Grenze erreicht. Es gibt aber Mittel und Wege, mit denen sich die Löcher stopfen bzw. verhindern lassen. Wenn Du z.B. beim Einfügen nach der niedrigsten freien ID suchst und diese beim Insert angibst, wird der Zähler nicht höher gesetzt (Wert ist kleiner als der Wert des Zählers). Eine Möglichkeit hierzu bietet ein Select an, der die Tabelle einfach mit sich selbst Joint.
SELECT A.ID+1 AS NEXTID
FROM tabelle A LEFT JOIN tabelle B ON A.ID+1 = B.ID
WHERE B.ID IS NULL ORDER BY A.ID LIMIT 1;
Sind nun z.B. in ‚tabelle‘ die IDs 1,2,90,10000 vergeben, wird 3 als nächste ID vorgeschlagen, und nicht, wie autoincrement es machen würde, 10001. So garantierst Du, dass deine IDs auch einigermaßen der Anzahl der wirklich enthaltenen Datensätze gerecht werden.
Schönen Gruß,
Rudy
SELECT A.ID+1 AS NEXTID
FROM tabelle A LEFT JOIN tabelle B ON A.ID+1 = B.ID
WHERE B.ID IS NULL ORDER BY A.ID LIMIT 1;Sind nun z.B. in ‚tabelle‘ die IDs 1,2,90,10000 vergeben, wird
3 als nächste ID vorgeschlagen, und nicht, wie autoincrement
es machen würde, 10001. So garantierst Du, dass deine IDs auch
einigermaßen der Anzahl der wirklich enthaltenen Datensätze
gerecht werden.Schönen Gruß,
Rudy
Da können aber auch große Probleme auftauchen :\
Wenn zB ein Datensatz gelöscht wird und ein anderer wird mit der ID erstellt, dann kann es passieren wenn abhängige Daten von anderen Tabellen nicht korrekt geändert bzw gelöscht worden sind, das dieser jetzt mit dem neuen Datensatz verknüpft ist, obwohl er garnichts damit zu tun hat!
Das kann ohne zurücksetzen des Auto_increment-wertes nicht vorkommen, da selbst historisch gesehen jeder werd einmalig ist!
Hi,
Da können aber auch große Probleme auftauchen :\
Wenn zB ein Datensatz gelöscht wird und ein anderer wird mit
der ID erstellt, dann kann es passieren wenn abhängige Daten
von anderen Tabellen nicht korrekt geändert bzw gelöscht
worden sind, das dieser jetzt mit dem neuen Datensatz
verknüpft ist, obwohl er garnichts damit zu tun hat!
Das Problem der referentiellen Integrität. Das kannst Du entweder mit Foreign Key Constraints oder bedachter Programmierung lösen.
Das kann ohne zurücksetzen des Auto_increment-wertes nicht
vorkommen, da selbst historisch gesehen jeder werd einmalig
ist!
Das stimmt, allerdings bis zu einem gewissen Punkt. Es gibt ja nicht nur INT und BIGINT als Feldwerte. Wenn Du aus Speichergründen bei den Masterdatensätzen nur eine TINYINT (0-255) verwendest, kommst Du schnell an die Grenze. Da Du davon ausgehst, dass Du nicht mehr Masterdatensätze haben wirst, dafür sehr viele verbundene Datensätze, welche die ID immer mittragen müssen, sparst Du viel Speicher, wenn Du sie klein wählst. Dann kannst Du dir in keinem Fall Löcher bei der ID-Vergabe leisten, wenn die Daten geändert/gelöscht werden. Für die referentielle Integrität musst Du selbst oder das DBS sorgen.
Schönen Gruß,
Rudy
Das stimmt, allerdings bis zu einem gewissen Punkt. Es gibt ja
nicht nur INT und BIGINT als Feldwerte. Wenn Du aus
Speichergründen bei den Masterdatensätzen nur eine TINYINT
(0-255) verwendest, kommst Du schnell an die Grenze. Da Du
davon ausgehst, dass Du nicht mehr Masterdatensätze haben
wirst, dafür sehr viele verbundene Datensätze, welche die ID
immer mittragen müssen, sparst Du viel Speicher, wenn Du sie
klein wählst. Dann kannst Du dir in keinem Fall Löcher bei der
ID-Vergabe leisten, wenn die Daten geändert/gelöscht werden.
Für die referentielle Integrität musst Du selbst oder das DBS
sorgen.Schönen Gruß,
Rudy
Ok, wenn man eine tinyint als typ verwendet, kann ich einsehen, dass man den autoincrement Wert vielleicht mal zuruecksetzten muss. Aber da wuerde ich wohl ganz auf auto increment verzichten und die IDs von Hand anlegen und selbst sicherstellen, dass die referentielle Integrität sichergestellt ist.
So mach ichs zumindest bei meinen Oracle und db2 Projekten…
–
Gruss
Dirk
http://www.phpmybackuppro.net/mozilla_search_plugins
Ok, wenn man eine tinyint als typ verwendet, kann ich
einsehen, dass man den autoincrement Wert vielleicht mal
zuruecksetzten muss. Aber da wuerde ich wohl ganz auf auto
increment verzichten und die IDs von Hand anlegen und selbst
sicherstellen, dass die referentielle Integrität
sichergestellt ist.
So mach ichs zumindest bei meinen Oracle und db2 Projekten…
Das ist auch gut so, aus der Sparte komme ich auch. MySQL ist leider lange nicht so mächtig wie Oracle. Keine Generatoren können getrennt angelegt werden, es gibt nur einen auto_increment Wert pro Tabelle. Programmierern wird immer ans Herz gelegt, mit den Ressourcen sparsam umzugehen. Da gefällt es wenig, wenn ein DELETE FROM TABLE ausgeführt wurde, die Tabelle leer ist, und der nächste angelegte Datensatz mit ID 2345 beginnt. Der Befehl TRUNCATE TABLE leert die Tabelle und setzt den Zähler zurück. Leider kannst Du nicht immer TRUNCATE verwenden, wenn Du beispielsweise ein System machst, in dem Datensätze innerhalb eines gewissen Datums löschen lässt. Sind dann nur Daten gelöscht worden, welche am Anfang der Tabelle standen, hast Du Pech und der Zähler wird mit einem normalen Abgleich mit der maximalen ID nicht zurückgesetzt. Sind alle Datensätze gelöscht oder vom Ende der Tabelle, macht der Abgleich durchaus Sinn, der Autoincrement-Wert ist sonst irgendwann an der Grenze angelangt. Da das unter Umständen aber auch mit dem Zurücksetzen des Zählers passieren kann, hat man die Wahl:
- nimm ID möglichst groß, verschwendet zwar Speicher, aber es läuft lange und es ist unwahrscheinlich, dass der auto_increment-Wert an die Grenze kommt
oder - nimm eine ID von der Größe wie Du sie wirklich brauchst (sehr oft reicht SMALLINT oder MEDIUMINT), Suche nach der niedrigsten freien ID vor jedem Insert. Spart Speicher, keine ‚Löcher‘ in den Datensätzen, hat aber (zumindest beim Einfügen) mehr Datenbankzugriffe, in dem Fall kannst Du aber auf auto_increment und Zähler zurücksetzen verzichten.
Schönen Gruß,
Rudy