Wie behandelt man die Fragmentierung der auto_increment ID-Spalte in MySQL?

Question

Ich habe eine Tabelle mit einem auto_increment Feld und manchmal werden Zeilen gelöscht, so dass auto_increment Lücken hinterlässt. Gibt es eine Möglichkeit, dies zu vermeiden, oder wenn nicht, zumindest, wie man eine SQL-Abfrage schreiben, die:

1. Ändert die auto_increment Wert ist der maximale Wert (aktueller Wert) + 1
2. Geben Sie die neue auto_increment Wert?

Ich weiß, wie man einen Teil schreibt 1 y 2 aber kann ich sie in dieselbe Abfrage einfügen?

Wenn das nicht möglich ist:

Wie "wähle" (gebe) ich die auto_increment Wert oder auto_increment Wert + 1?

Answer 1

Die Umnummerierung wird zu Verwirrung führen. Bestehende Berichte beziehen sich auf den Datensatz 99, doch wenn das System diesen Datensatz neu nummeriert, wird er möglicherweise auf 98 umnummeriert, so dass alle Berichte (und ausgefüllten Benutzeroberflächen) falsch sind. Wenn Sie einmal eine eindeutige ID zugewiesen haben, muss diese fest bleiben.

Die Verwendung von ID-Feldern für etwas anderes als eine einfache eindeutige Nummerierung wird problematisch sein. Die Anforderung "keine Lücken" ist einfach unvereinbar mit der Anforderung, löschen zu können. Vielleicht könnten Sie Datensätze als gelöscht markieren, anstatt sie zu löschen. Dann gibt es wirklich keine Lücken. Nehmen wir an, Sie erstellen nummerierte Rechnungen: Sie würden eine Rechnung mit dieser Nummer als nullwertig stornieren, anstatt sie zu löschen.

Answer 2

Es gibt eine Möglichkeit, die ID auch in einer automatischen Tabelle manuell einzufügen. Dazu müssen Sie lediglich die fehlende ID ermitteln.

Allerdings, Tun Sie das nicht . Dies kann sehr gefährlich sein, wenn Ihre Datenbank relational ist. Es ist möglich, dass die gelöschte ID an anderer Stelle verwendet wurde. Wenn sie entfernt wird, würde sie kein großes Problem darstellen, vielleicht würde sie einen Datensatz verwaisen lassen. Wenn sie ersetzt wird, wäre das ein großes Problem, weil die falsche Beziehung vorhanden wäre.

Nehmen wir an, ich habe einen Tisch mit Autos und einen Tisch mit Menschen

car
carid
ownerid
name

person
personid
name

Und dass es einige einfache Daten gibt

car
1 1 Van
2 1 Truck
3 2 Car
4 3 Ferrari
5 4 Pinto

person
1 Mike
2 Joe
3 John
4 Steve

und jetzt lösche ich Person John.

person
1 Mike
2 Joe
4 Steve

Wenn ich eine neue Person, Jim, in die Tabelle einfüge und er eine ID erhält, die die Lücke füllt, dann würde er am Ende die ID 3 erhalten

1 Mike
2 Joe
3 Jim
4 Steve

und wäre damit der Eigentümer des Ferrari.

Answer 3

Ich stimme im Allgemeinen mit den klugen Leuten auf dieser Seite überein (und Duplikat Fragen), in denen von der Wiederverwendung automatisch inkrementierter IDs abgeraten wird. Das ist ein guter Ratschlag, aber ich glaube nicht, dass es an uns ist, über Recht und Unrecht der Frage zu entscheiden, sondern davon auszugehen, dass der Entwickler weiß, was er tun will und warum.

Die Antwort ist, wie von Travis J. erwähnt, dass Sie eine auto-increment id wiederverwenden können, indem Sie die id-Spalte in eine Einfügeanweisung aufnehmen und den gewünschten Wert zuweisen.

Hier ist ein Punkt, der die Sache ins Wanken bringt: MySQL selbst (mindestens 5.6 InnoDB) sera in den folgenden Fällen eine automatisch inkrementierende ID wiederverwenden:

• eine beliebige Anzahl von Zeilen mit der höchsten auto-increment id löschen
• Anhalten und Starten von MySQL
• eine neue Zeile einfügen

Die eingefügte Zeile hat eine id, die als max(id)+1 berechnet wird, sie hat pas von der gelöschten ID aus fortsetzen.

Answer 4

Als djna sagte in seiner/ihrer Antwort, dass es keine gute Praxis ist, Datenbanktabellen auf diese Weise zu ändern, und dass es auch keine Notwendigkeit dafür gibt, wenn Sie das richtige Schema und die richtigen Datentypen gewählt haben. Übrigens, entsprechend einem Teil Ihrer Frage:

Ich habe eine Tabelle mit einem auto_increment Feld und manchmal werden Zeilen gelöscht, so dass auto_increment Lücken hinterlässt. Gibt es eine Möglichkeit, dies zu vermeiden?

1. Wenn Ihre Tabelle zu viele Lücken in ihrer Auto-Inkrement-Spalte hat, wahrscheinlich als Ergebnis von so vielen Test INSERT Abfragen
2. Und wenn Sie verhindern wollen, dass die id-Werte überhand nehmen, indem Sie die Lücken entfernen
3. Und auch wenn die id Spalte ist nur ein Zähler und hat keinen Bezug zu einer anderen Spalte in Ihrer Datenbank

Dies könnte genau das sein, wonach Sie (oder eine andere Person, die so etwas sucht) gesucht haben:

LÖSUNG

1. das Original entfernen id Spalte
2. fügen Sie es wieder mit auto_increment auf

Wenn Sie aber nur die auto_increment auf den ersten verfügbaren Wert:

ALTER TABLE `table_name` AUTO_INCREMENT=1

Answer 5

Ich bin mir nicht sicher, ob das hilft, aber in Sql Server können Sie die Identitätsfelder neu sortieren. Es scheint, dass es eine ALTER TABLE-Anweisung in mySql gibt, um dies zu erreichen. Z.B. um die ID auf 59446 zu setzen.

ALTER TABLE table_name AUTO_INCREMENT = 59446; 

Ich denke, Sie sollten in der Lage sein, eine Abfrage zu kombinieren, um den größten Wert des Feldes auto_increment zu erhalten, und verwenden Sie dann die Tabelle ändern, um nach Bedarf zu aktualisieren.