MySQL AUTO_INCREMENT mit Beispielen

Was ist automatisches Inkrementieren?

Auto Increment ist eine Funktion, die mit numerischen Datentypen arbeitet. Sie generiert automatisch fortlaufende numerische Werte, wenn ein Datensatz für ein als Auto Increment definiertes Feld in eine Tabelle eingefügt wird.

Das Attribut funktioniert mit allen Integer-Datentypen, von TINYINT bis BIGINT. Die Spalte muss außerdem indiziert sein; dies geschieht automatisch, wenn sie als Primärschlüssel deklariert wird.

Wann wird die automatische Inkrementierung verwendet?

In der Lektion über DatenbanknormalisierungWir haben uns angeschaut, wie Daten mit minimaler Redundanz gespeichert werden können, indem die Daten in vielen kleinen Tabellen gespeichert werden, die über Primär- und Fremdschlüssel miteinander verknüpft sind.

Ein Primärschlüssel muss eindeutig sein, da er eine Zeile in einer Datenbank eindeutig identifiziert. Doch wie können wir sicherstellen, dass der Primärschlüssel immer eindeutig ist?

Eine mögliche Lösung wäre die Verwendung einer Formel zur Generierung des Primärschlüssels, die vor dem Einfügen von Daten prüft, ob der Schlüssel bereits in der Tabelle vorhanden ist. Dies mag funktionieren, ist aber komplex und nicht narrensicher. Zwei Sitzungen, die gleichzeitig Daten einfügen, können immer noch denselben Maximalwert lesen und dadurch einen Konflikt verursachen.

Um diese Komplexität zu vermeiden und sicherzustellen, dass der Primärschlüssel immer eindeutig ist, können wir Folgendes verwenden: MySQL Die automatische Inkrementierung dient zur Generierung von Primärschlüsseln. Sie wird mit dem Datentyp INT verwendet. Der Datentyp INT unterstützt sowohl vorzeichenbehaftete als auch vorzeichenlose Werte. Vorzeichenlose Datentypen können nur positive Zahlen enthalten. Es empfiehlt sich, die Einschränkung „vorzeichenlos“ für den automatisch inkrementierten Primärschlüssel zu definieren.

Syntax für die automatische Inkrementierung

Nachdem die Argumentation geklärt ist, schauen wir uns das Skript an, das zur Erstellung der Filmkategorientabelle verwendet wurde.

CREATE TABLE `categories` (
  `category_id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_name` varchar(150) DEFAULT NULL,
  `remarks` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`category_id`)
);

Beachten Sie den Eintrag „AUTO_INCREMENT“ im Feld category_id. Dadurch wird die Kategorie-ID jedes Mal automatisch generiert, wenn eine neue Zeile in die Tabelle eingefügt wird. Sie wird beim Einfügen von Daten in die Tabelle nicht angegeben. MySQL erzeugt es.

Hinweis: Das Schlüsselwort UNSIGNED verdoppelt den positiven Wertebereich der Spalte, und die Anzeigebreite, die einmal als int(11) geschrieben wurde, ist veraltet. MySQL 8.0.17 und folgende. Einfach int ist die aktuelle Form.

Standardmäßig ist der Startwert für AUTO_INCREMENT 1 und wird für jeden neuen Datensatz um 1 erhöht.

Betrachten wir nun den aktuellen Inhalt der Kategorientabelle.

SELECT * FROM `categories`;

Ausführen des obigen Skripts in MySQL Die Workbench-Analyse von myflixdb liefert folgende Ergebnisse.

category_id
category_name
remarks




1
Comedy
Movies with humour


2
Romantic
Love stories


3
Epic
Story acient movies


4
Horror
NULL


5
Science Fiction
NULL


6
Thriller
NULL


7
Action
NULL


8
Romantic Comedy
NULL

Es existieren acht Zeilen, daher sollte die nächste generierte ID 9 sein. Fügen wir nun eine neue Kategorie in die Tabelle „Kategorien“ ein und geben wir nur den Namen an.

INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');

Führen Sie das obige Skript für die myflixdb aus MySQL Werkbank gibt uns die folgenden unten gezeigten Ergebnisse.

category_id
category_name
remarks




1
Comedy
Movies with humour


2
Romantic
Love stories


3
Epic
Story acient movies


4
Horror
NULL


5
Science Fiction
NULL


6
Thriller
NULL


7
Action
NULL


8
Romantic Comedy
NULL


9
Cartoons
NULL

Bitte beachten Sie, dass wir die Kategorie-ID nicht angegeben haben. MySQL wurde automatisch generiert, da die Kategorie-ID als automatisch inkrementierend definiert ist.

Wenn Sie die letzte Einfügungs-ID erhalten möchten, die von generiert wurde MySQL, können Sie dazu die Funktion LAST_INSERT_ID verwenden. Das unten gezeigte Skript ruft die zuletzt generierte ID ab.

SELECT LAST_INSERT_ID();

Die Ausführung des obigen Skripts liefert die letzte durch die INSERT-Abfrage generierte automatische Inkrementnummer. Die Ergebnisse werden unten angezeigt.

TIPP: LAST_INSERT_ID() ist auf Ihre eigene Verbindung beschränkt, sodass ein Wert, der durch eine Einfügung eines anderen Benutzers generiert wurde, niemals versehentlich an Sie zurückgegeben werden kann.

So legen Sie den Startwert für AUTO_INCREMENT fest oder setzen ihn zurück

Die Standardsequenz beginnt bei 1, aber das entspricht nicht immer den Projektanforderungen. Rechnungsnummern müssen möglicherweise aus einem Altsystem übernommen werden, und eine Testtabelle muss oft zurückgesetzt werden. MySQL Der Zähler wird direkt zugänglich gemacht, sodass beide Fälle mit einer einzigen Klausel behandelt werden. Führen Sie die folgenden Schritte aus, um die Startzahl zu steuern.

1. Den Wert bei der Erstellung festlegen. Fügen Sie die AUTO_INCREMENT-Klausel zur CREATE TABLE-Anweisung hinzu. Die erste eingefügte Zeile erhält dann diese Zahl anstelle von 1.
2. Ändern Sie den Wert in einer bestehenden Tabelle. Arbeiten jederzeit weiterbearbeiten können. Jede Präsentation und jeder KI-Avatar, den Sie von Grund auf neu erstellen oder hochladen, ALTER TABELLE mit der gleichen Klausel. MySQL Akzeptiert die neue Nummer nur, wenn sie größer ist als die größte aktuell gespeicherte Kennung.
3. Eine geleerte Tabelle zurücksetzen. TRUNCATE TABLE entfernt alle Zeilen und setzt den Zähler in einem einzigen Vorgang auf 1 zurück, was DELETE allein nicht leistet.
4. Bestätigen Sie die Änderung. Fügen Sie eine Zeile ein und lesen Sie die Kennung mit LAST_INSERT_ID() wieder aus, bevor Sie sich auf die neue Sequenz verlassen.

-- Start a brand-new table at 1000
CREATE TABLE `invoices` (
  `invoice_id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `amount` decimal(10,2),
  PRIMARY KEY (`invoice_id`)
) AUTO_INCREMENT = 1000;

-- Move the counter on an existing table
ALTER TABLE `categories` AUTO_INCREMENT = 100;

-- Empty the table and reset the counter to 1
TRUNCATE TABLE `categories`;

Die Schrittweite lässt sich auch mit der Systemvariable `auto_increment_increment` ändern, diese gilt jedoch für den gesamten Server und nicht nur für eine einzelne Tabelle. Sie wird hauptsächlich bei der Replikation verwendet, wo zwei Server nicht denselben Bezeichner generieren dürfen.

Warum treten Lücken in einer AUTO_INCREMENT-Sequenz auf?

Früher oder später erscheinen in einer Tabelle Kennungen wie 1, 2, 5, 6. Das ist völlig in Ordnung. Der Zähler ist so konzipiert, dass er Eindeutigkeit gewährleistet, nicht aber eine ununterbrochene Zahlenfolge, und er gibt niemals zweimal denselben Wert aus.

Lücken entstehen aus folgenden Gründen.

• Gelöschte Zeilen: Wenn eine Zeile aus einer Tabelle gelöscht wird, wird ihre automatisch inkrementierte ID nicht wiederverwendet. MySQL generiert weiterhin sequenziell neue Zahlen.
• Rückgängig gemachte Transaktionen: Die Nummer wird im Moment des Einfügens beansprucht. Wird die Transaktion rückgängig gemacht, verschwindet die Zeile, aber die Nummer ist bereits verbraucht.
• Fehlgeschlagene Einfügungen: Eine Anweisung, die aufgrund einer UNIQUE-Beschränkung abgelehnt wird, kann dennoch einen Bezeichner verbrauchen, bevor sie fehlschlägt.
• Großpackungen: InnoDB kann einen Nummernblock für einen Mehrzeilen-Einfügevorgang reservieren und die nicht verwendeten Nummern verwerfen.

Der Versuch, diese Lücken zu schließen, ist ein Fehler. Durch das Umnummerieren von Zeilen werden alle Fremdschlüssel, die auf diese Zeilen verweisen, ungültig, und der Wert selbst ist bedeutungslos. Benötigt ein Bericht eine fortlaufende Liste, sollte die Zeilennummer in der Abfrage generiert werden, anstatt gespeicherte Daten zu überschreiben.

Häufig gestellte Fragen