SQL - Verwenden von Sequenzen

Eine Sequenz ist eine Menge von Ganzzahlen 1, 2, 3, ..., die bei Bedarf in der richtigen Reihenfolge generiert werden. Sequenzen werden häufig in Datenbanken verwendet, da bei vielen Anwendungen jede Zeile in einer Tabelle einen eindeutigen Wert enthalten muss und Sequenzen eine einfache Möglichkeit bieten, sie zu generieren.

In diesem Kapitel wird die Verwendung von Sequenzen in MySQL beschrieben.

Verwenden der Spalte AUTO_INCREMENT

Die einfachste Möglichkeit in MySQL, Sequenzen zu verwenden, besteht darin, eine Spalte als AUTO_INCREMENT zu definieren und den Rest MySQL zu überlassen.

Beispiel

Probieren Sie das folgende Beispiel aus. Dadurch wird eine Tabelle erstellt und anschließend werden einige Zeilen in diese Tabelle eingefügt, in denen keine Datensatz-ID angegeben werden muss, da diese von MySQL automatisch erhöht wird.

mysql> CREATE TABLE INSECT
   -> (
   -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   -> PRIMARY KEY (id),
   -> name VARCHAR(30) NOT NULL, # type of insect
   -> date DATE NOT NULL, # date collected
   -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO INSECT (id,name,date,origin) VALUES
   -> (NULL,'housefly','2001-09-10','kitchen'),
   -> (NULL,'millipede','2001-09-10','driveway'),
   -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM INSECT ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

Erhalten Sie AUTO_INCREMENT-Werte

Die LAST_INSERT_ID () ist eine SQL-Funktion, sodass Sie sie von jedem Client aus verwenden können, der versteht, wie SQL-Anweisungen ausgegeben werden. Andernfalls bieten PERL- und PHP-Skripte exklusive Funktionen zum Abrufen des automatisch inkrementierten Werts des letzten Datensatzes.

PERL Beispiel

Verwenden Sie die mysql_insertidAttribut, um den von einer Abfrage generierten Wert AUTO_INCREMENT zu erhalten. Der Zugriff auf dieses Attribut erfolgt entweder über ein Datenbankhandle oder ein Anweisungshandle, je nachdem, wie Sie die Abfrage ausgeben. Das folgende Beispiel verweist darauf über das Datenbankhandle.

$dbh->do ("INSERT INTO INSECT (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

PHP Beispiel

Rufen Sie nach dem Ausgeben einer Abfrage, die einen AUTO_INCREMENT-Wert generiert, den Wert durch Aufrufen von ab mysql_insert_id( ) Funktion.

mysql_query ("INSERT INTO INSECT (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

Bestehende Sequenz neu nummerieren

Es kann vorkommen, dass Sie viele Datensätze aus einer Tabelle gelöscht haben und alle Datensätze neu sortieren möchten. Dies kann mit einem einfachen Trick geschehen. Sie sollten jedoch sehr vorsichtig sein und prüfen, ob Ihre Tabelle mit einer anderen Tabelle verknüpft ist oder nicht.

Wenn Sie feststellen, dass eine erneute Sequenzierung einer AUTO_INCREMENT-Spalte unvermeidbar ist, löschen Sie die Spalte aus der Tabelle und fügen Sie sie erneut hinzu.

Das folgende Beispiel zeigt, wie Sie die ID-Werte in der Insektentabelle mit dieser Technik neu nummerieren.

mysql> ALTER TABLE INSECT DROP id;
mysql> ALTER TABLE insect
   -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
   -> ADD PRIMARY KEY (id);

Starten einer Sequenz bei einem bestimmten Wert

Standardmäßig startet MySQL die Sequenz mit 1, Sie können jedoch zum Zeitpunkt der Tabellenerstellung auch eine andere Nummer angeben.

Der folgende Codeblock enthält ein Beispiel, in dem MySQL die Sequenz ab 100 startet.

mysql> CREATE TABLE INSECT
   -> (
   -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
   -> PRIMARY KEY (id),
   -> name VARCHAR(30) NOT NULL, # type of insect
   -> date DATE NOT NULL, # date collected
   -> origin VARCHAR(30) NOT NULL # where collected
);

Alternativ können Sie die Tabelle erstellen und dann mit ALTER TABLE den anfänglichen Sequenzwert festlegen.

mysql> ALTER TABLE t AUTO_INCREMENT = 100;