Zur Startseite

Alles über PHP und MySQL

Eintrag-Details: Verwendung von auto_increment in mySQL

28.01.2006

Verwendung von auto_increment in mySQL

Eine Frage taucht immer wieder in Newsgroups und Foren auf: Kann ich einmal verwendete Nummern aus einem auto_increment Feld erneut wiederverwenden und wenn nein, warum nicht?

[Mehr:]

Zur Erklärung: Ein Primärindex kann in mySQL mit dem Attribut "auto_increment" versehen werden. Das führt dann dazu, dass beim Anlegen eines neuen Datensatzes dieser Index automatisch um eine Ziffer erhöht und in diesem Feld abgespeichert wird, wenn der Wert mit "0" vorgegeben wird.

Advertisement/Werbung

Ein kleines Beispiel soll das verdeutlichen: Erstellen Sie in mySQL eine neue Tabelle mit diesen Feldern:

create table kunden (
id int primary key auto_increment,
vorname varchar(80),
nachname varchar(80)
)

Code herunterladen

Beim Einfügen eines neuen Datensatzes, wird der Index für "id" automatisch vergeben:

insert into kunden values (
0, 'Heinz', 'Meier');

Code herunterladen

Obwohl der erste Wert mit "0" angegeben ist, wird in der Datenbank die id von Heinz Meier mit "1" gespeichert. Wird ein zweiter Datensatz auf die gleiche Weise eingegeben, so erhält der Wert für id automatisch die Ziffer "2".

insert into kunden values (
0, 'Kurt', 'Müller');

Code herunterladen

So weit, so gut!

Wird nun ein Datensatz oder gar mehrere Datensätze aus dieser Tabelle gelöscht, so könnte man ja die frei gewordenen id-Nummern wieder verwenden.

delete from kunden where id = '2'

Code herunterladen

Wird nun jedoch ein neuer Datensatz eingefügt, so vergibt mySQL automatisch den nächsten Index mit "3".

insert into kunden values (
0, 'Kurt', 'Schmidt');

Code herunterladen

Advertisement/Werbung

Warum ist das so? Ein auto_increment Index wird üblicherweise gern dazu verwendet, um die Verbindung zwischen Tabellen herzustellen. So könnte man sich zum Beispiel eine Tabelle "adressen" vorstellen, die mit der Tabelle "kunden" über diese id verbunden ist. Wird nun ein Datensatz aus "kunden" gelöscht, so kennt mySQL bis zur Version 4 noch keine sogenannte "referenzielle Integrität". Das bedeutet, ich kann einen Datensatz aus "kunden" löschen, auch wenn noch verbundene Daten in "adressen" existieren. Der Datenbankserver erkennt das nicht, es ist vielmehr Aufgabe des Programmierers, solche "Karteileichen" im Datenbestand zu vermeiden.

Würde mySQL die Indexnummern wiederverwenden, so könnte dadurch weit mehr Schaden entstehen, als wenn die Nummer für immer unbelegt bliebe. In einer Auswertung würde dann nämlich dem neuen Kunden eine ganz falsche Adresse zugeordnet. Daher ist es meist keine gute Idee, gelöschte auto_increment Indexnummern erneut zu vergeben.

Welche Probleme können aber bei diesem Verhalten auftauchen? Ein int kann in mySQL maximal die Zahl 2147483647 speichern, in der unsigned Variante ist es 4294967295. Damit wir klar, dass ein Datenbestand nicht mehr als vier Milliarden Datensätze speichern darf, wenn der Index über einen int realisiert wird. Das schließt alle im laufe der Bearbeitung gelöschten Datensätze mit ein. Daher ist es meist keine Gute Idee, bei Tabellen mit auto_increment zu arbeiten, wenn darin sehr viele Daten gelöscht und wieder neu angelegt werden. Für solche Bewegungsdaten (z.B. Logdaten) sollte daher ein anderer eindeutiger Index (z.B. ein Timestamp) verwendet werden, der nicht nach oben hin begrenzt ist.

Für große Datenbestände (z.B. Kundentabellen in großen Unternehmen), kann man dann auch auf den Datentyp "bigint" ausweichen, der als "unsigned bigint" immerhin bis zur Zahl 18446744073709551615 reicht.

Nur als Überschlagswert: Wenn Sie in der Tabelle jede Sekunde einen neuen Index vergeben würden, so würde ein unsigned int immer noch 136 Jahre ausreichen, bis die Grenze erreicht ist. Beim unsigned bigint sind es dann schon 584942417355 Jahre, was in der Regel, auch ohne Wiederverwertung von alten Indexnummern für die meisten Anwendungen reichen sollte.

Problematisch kann es sein, wenn statt "0" eine Zahl übergeben wird. Dann speichert mySQL nämlich diese Zahl als id, sofern Sie nicht schon mit einem Datensatz belegt ist. Damit ermöglicht die Datenbank das wiedereinlesen von gelöschten Datensätzen. Schließlich könnte es passieren, dass ein Datensatz versehentlich gelöscht wurde, dann aber mit dem gleichen Index wieder eingefügt werden soll.

insert into kunde values (
6000, 'Gerda', 'Krause');

Code herunterladen

Dieser SQL-Befehl legt somit den indexwert auf 6000 fest. Das bedeutet, dass die nächste vergebene auto_increment Zahl 6001 sein wird.

Die letzte eingetragene Zahl für auto_increment erhält man übrigens mit diesem Befehl:

select last_insert_id()

Code herunterladen

Das funktioniert allerdings nur, wenn die Datenbank den Index automatisch vergeben hat. Andernfalls (wie in diesem Beispiel mit festgelegtem Indexwert) würden Sie 0 als Ergebnis erhalten. In der Praxis ist das nicht weiter tragisch, da Sie bei selbst festgelegtem Indexwert diesen ja schon kennen. Lediglich bei der automatischen Vergabe der nächsten Indexnummer müssen Sie diese in Erfahrung bringen.

Achten Sie aber bitte darauf, dass Sie zwischen dem letzten INSERT und der Abfrage von LAST_INSERT_ID() die Datenbank-Session nicht verlassen. Bei PHP-Skripten bedeutet das, die Abfragen müssen sich im gleichen Skript auf der gleichen Seite befinden und es darf zwischen diesen beiden Befehlen kein mysql_close() bzw. kein erneutes mysql_connect() verwendet werden. Andernfalls würden Sie nicht Ihre letzte insert_id erhalten, sondern eine 0.

Hinweis: Diesen Text und die enthaltenen Beispielprogramme und Skripte dürfen Sie zum Erlernen der einzelnen Techniken und auch als Grundlage für Ihre eigenen Projekte auf eigene Gefahr kostenlos nutzen.

Bitte denken Sie daran, dass alle Texte und Quellcodes trotzdem urheberrechtlich geschützt sind. Eine direkte Weitergabe ist nur nach ausdrücklicher schriftlicher Genehmigung gestattet.

28.01.2006 10:10:17, von Marian

, 867 Wörter, 18924 mal angeschaut

Kategorien: MySQL

6 Kommentare

Kommentare, Pingbacks:

Kommentar von: Michael Braun [Besucher]

Besser als das auf '0' -Setzen einer AUTO_INCREMENT-Spalte in MySQL, um zu erzwingen, dass MySQL in dieser Spalte aut. einen neuen Int-Value vergibt ist:

das einfache Weglassen dieser Spalte aus dem Insert

das explizite NULL-Setzen dieser Spalte

Quelle:
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

09.11.2006 @ 11:17

Kommentar von: Frank Berger [Besucher]

Supie, klipp und klar was ich wissen wollte....merci
MfG Frank

28.03.2007 @ 23:37

Kommentar von: Stephanmuc [Besucher]

Hallo,

beim Löschen von Feldern, wird die gelöschte id niht wieder befüllt. So entstehen "Lücken".
Wie kann ich diese Lücken automatisch befüllen lassen anstatt immer zur letzten id +1 automatisch eine grosse id zu vergeben?

Danke & Grüße
Stephan

12.11.2007 @ 00:51

Kommentar von: Marian [Mitglied] · http://www.heddesheimer.de

Das ist nicht vorgesehen. auto_increment soll ja immer eine neue ID erzeugen. Nur so ist gewährleistet, dass auch später die Integrität der Datenbank gewährt ist, selbst wenn früher gelöschte Datensätze aus einem älteren Backup wieder eingelesen werden.

12.11.2007 @ 09:05

Kommentar von: Andreas Duswald [Besucher] · http://www.radio-lechtal.de

aber was soll ich machen, wenn ich es irgendwie brauch das er das lehre sofort irgendwie voll macht wieder - Gibts da ein Workaround?

Hab mich schon dämlich gesuchgt. Problem ist nämnlich, dass z B js arrays keine lücken mögen, und in diesem speziellen fall, kommts dann zu fehlern

Danke für Tipps :-)

19.03.2008 @ 07:51

Kommentar von: Oliver Schneider [Besucher]

Vielen Dank, sehr hilfreiche Infos hier!

02.03.2009 @ 11:10

Kommentar schreiben:

Name:

eMail:

Ihre E-Mail-Adresse wird nicht angezeigt.

Seite/URL:

Ihre URL wird angezeigt.

Sorry, in letzter Zeit erhalte ich einfach zu viel Spam-Mails von automatischen Programmen. Das Veröffentlichen der Kommentare wird daher zeitlich verzögert und Sie müssen eine Frage beantworten.
Wenn Sie also ein Mensch sind, dann hier bitte als Zahl eingeben: wieviel ist drei mal fünf?:

Antispam-Feld

Kommentartext:

Erlaubte XHTML Tags: <p, ul, ol, li, dl, dt, dd, address, blockquote, ins, del, span, bdo, br, em, strong, dfn, code, samp, kdb, var, cite, abbr, acronym, q, sub, sup, tt, i, b, big, small>

Optionen:

Auto-BR (Zeilenumbrüche werden zu <br />)
Meine Daten merken (Setze Cookies für Name, eMail & URL)