MySQL Auto Increment

[luke]

Hi,

ich habe mehrere PHP-Scripte, die in eine Tabelle Daten schreiben und diese Einträge bekommen automatisch eine ID per "Auto Increment" zugewiesen. Jetzt sind es aber schon etliche Einträge und die IDs werden wohl bald an ihre Obergrenze stoßen. Ich habe bei tinyint-Felder die Erfahrung gemacht, dass neue Einträge immer die ID 127 zugewiesen bekommen (sofern bereits 127 Datensätze vorhanden sind). Dieses Verhalten tritt auch auf, wenn bereits Einträge mit einer ID unter 127 wieder gelöscht wurden (also wieder IDs zur Verfügung stehen würden). Ich nehme an, dieses Verhalten tritt auch auf, wenn es sich um ein "int" Feld handelt. Aber irgendwas muss man doch dagegen machen können, oder?


So far!
#
Luke

 

[turbas]

Hallo,

da ID bei deiner Datenbank ein "unsigned tinyint" ist, ist der Höchstwert (im positiven Bereich) 127. Ändere den Datentyp einfach in (z.B.) einen "unsigned int" und Du solltest das Problem nicht mehr haben.

auto_increment nimmt immer den höchsten Wert+1, "Lücken" werden nicht aufgefüllt.

-Torsten

 

[Huschi]

tinyint = 1 Byte
smallint = 2 Byte
mediumint = 3 Byte
int = 4 Byte
bigint = 8 Byte

Die maximal Werte sind dann noch abhängig von signed oder unsigned.
Ansonsten kannst Du aber 2er-Potenzen rechnen, oder?

huschi.

 

[luke]

Ich weiß sehr wohl bis zu welchem Wert welche *int-Felder gehen.

Was ich wissen will ist, wie ich (My)SQL dazu veranlasse die Werte unterhalb der höchsten ID aufzufüllen, vor allem dann, wenn ich den maximalen Wert erreicht habe (und das habe ich am Beispiel tinyINT verdeutlichen wollen!).

Also, ich habe z.B. diesen Fall:

ID 1-50 (nicht vergeben, da Datensätze bereits gelöscht)
ID 51-127 (vergeben)

Jetzt will ich (bei einem tinyINT-Feld) noch 50 weiter Datensätze einfüllen (also die ersten 50, die schließlich noch frei sind.


So far!
#
Luke

 

[Huschi]

Was ich wissen will ist, wie ich (My)SQL dazu veranlasse die Werte unterhalb der höchsten ID aufzufüllen,

Nun, MySQL ist dahingehend etwas flexibler (zumindest alles was <4.0 ist).
Du kannst beim insert eine ID (!=0) angeben, die dann auch reingeschrieben wird. Genauso kannst Du bei bestehenden Einträge die ID durch ein entsprechendes update ändern.
Versuch das aber nicht bei Oracle...

Das Ganze hat ja auch Datenbank-technische Gründe, warum ein autoincrement keine Lücken schließt:
a) Datensicherheit
b) referenzielle Integrietät erhalten,
c) Performance einbusen beim insert.

huschi.

 

[luke]

Mein Problem ist ganz einfach das, das ich keine zu langen IDs generiert haben will, aber auch nicht gerade wenig Datensätze habe. Wenn ich einmal bei 11 Stellen langen IDs angelangt bin und nur 100 Datensätze habe, verstehe ich nicht warum idie IDs dann ncht auch einfach nur 3 oder 4 Stellen haben können, sondern immer zur zuletzt eingetragenen ID eins dazugezählt werden muss.

Ich mache mir einfach Sorgen, dass meine Scripte dann aussteigen, so wie sie es z.B. mit dem tinyINT Feld gemacht haben (das war allerdings ein Fehler in meinem Datenbankkonzept).


So far!
#
Luke

 

[Huschi]

Ich verstehe Dein Problem.
Ich nutze für Primarykeys grundsätzlich BIGINT. Und es gibt bisher keine Klagen, daß es mal an seine Grenzen gestossen wäre.
Wenn Du keine langen IDs habe willst, dann mußt Du Dir einen eignen Algorithmus programmieren, der erst vorhandene Lücken auffüllt. (Also ganz ohne autoincrement.)
Oder Du läßt regelmässig ein Script drüber laufen, welches die Lücken sucht und mit einem Datensatz mit einer hohen ID füllt.

Der Grund warum 'autoincrement' (es steckt ja schon im Namen) keine Lücken schließt liegt einfach in der möglichen Referenzierung. Insbesondere, da MySQL keine referenzielle Integrität behandelt, kann er nicht einfach anfangen und 'verbrauchte IDs' neu vergeben. Denn MySQL weiß ja nicht, welche anderen Datensätze evtl noch an diese ID gebunden sind. Größere DBs wie z.B. Oracle können dies. Tun es aber auch nicht.

huschi.

 

[luke]

Wenn Du keine langen IDs habe willst, dann mußt Du Dir einen eignen Algorithmus programmieren, der erst vorhandene Lücken auffüllt. (Also ganz ohne autoincrement.)
Oder Du läßt regelmässig ein Script drüber laufen, welches die Lücken sucht und mit einem Datensatz mit einer hohen ID füllt.

Die erste Methode habe ich bisher nur einmal angewandt, der Aufwand hat sich aber meiner Meinung nach nicht gelohnt.

Die zweite Methode wäre natürlich eine gute Möglichkeit, nur muss man dann aufpassen wenn man viele (LEFT) JOINs hat, was nicht gerade selten vorkommt. Bei einer einzigen Tabelle ist kein größeres Problem. Da das glücklicherweise in einem speziellen Fall so ist, werde ich dort wohl diese Methode versuchen. Wenn es nicht klappt, bekommt der User eben riesige IDs vorgesetzt, was dann nicht mehr besonders einfach zu handhaben ist (die IDs müssen sichtbar sein).

Ach ja, was ist, wenn ich alle Datensätze aus der Tabelle lösche und dann einen neuen Datensatz hinzufüge? Dieser bekommt ja dann trotzdem eine ID > der ID, die als letztes eingefügt wurde. Kann ich wenigstens das verhindern, also die Tabelle auf 0 zurücksetzten? Und das ganze natürlich ohne die Tabelle zu löschen. ;-)


So far!
#
Luke

 

[Huschi]

Kann ich wenigstens das verhindern, also die Tabelle auf 0 zurücksetzten? Und das ganze natürlich ohne die Tabelle zu löschen. ;-)

Vieleicht hilft Dir das aus der Doku weiter:

The AUTO_INCREMENT value can be reset with myisamchk. This will make AUTO_INCREMENT columns faster and old numbers will not be reused as with the old ISAM.

Oder Du machst (siehe SET):

SET INSERT_ID=0;

huschi.

 

[luke]

The AUTO_INCREMENT value can be reset with myisamchk. This will make AUTO_INCREMENT columns faster and old numbers will not be reused as with the old ISAM.

Das trifft es doch schon ziemlich genau, danke.

Allerdings bin ich mir über die Anwendung von MyISAMchk noch nicht ganz im klaren, da werde ich mal suchen müssen.


So far!
#
Luke