SQL 使用序列

SQL中的 序列 是一种数据库对象，用于生成一系列唯一的整数值。它们在数据库中经常被使用，因为许多应用程序要求表中的每一行都包含一个唯一的值，并且序列提供了一种轻松生成它们的方式。序列是许多SQL数据库管理系统的特性，如Oracle，PostgreSQL，SQL Server和IBM DB2。

使用 CREATE SEQUENCE 语句在 SQL 中创建序列。该语句指定序列的名称，起始值，增量和序列的其他属性。

语法

以下是在 SQL 中创建序列的语法-

CREATE SEQUENCE Sequence_Name
START WITH Initial_Value
INCREMENT BY Increment_Value
MINVALUE Minimum_Value
MAXVALUE Maximum_Value
CYCLE|NOCYCLE;

在这里，

• Sequence_Name - 这指定了序列的名称。

• Initial_Value - 这指定了序列应该从哪个值开始。

• Increment_Value - 这指定了序列将自身递增的值。可以为正值或负值。

• Minimum_Value - 这指定了序列的最小值。

• Maximum_Value - 这指定了序列的最大值。

• Cycle - 当序列达到其最大值时，它会从头开始重复。

• Nocycle - 如果序列超过了最大值，将抛出一个异常。

示例

首先，让我们尝试使用以下查询创建一个名为“STUDENTS”的表 –

SQL> CREATE TABLE STUDENTS (ID INT, NAME CHAR(20), AGE INT NOT NULL);

现在，让我们使用如下查询中的INSERT语句向表中插入一些记录 –

SQL> INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL, 'Dhruv', '20');
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL, 'Arjun', '23');
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL, 'Dev', '25');
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL, 'Riya', '19');
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL, 'Aarohi','24');
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL, 'Lisa', '20');
INSERT INTO STUDENTS(ID, NAME, AGE) VALUES(NULL, 'Roy', '24');

让我们使用以下查询来验证表STUDENTS是否已创建：

SQL> SELECT * FROM STUDENTS;

表在SQL数据库中成功创建。

+------+-----------+------+
|  ID  |     NAME  |  AGE |
+------+-----------+------+
| NULL |    Dhruv  |   20 |
| NULL |    Arjun  |   23 |
| NULL |    Dev    |   25 |
| NULL |    Riya   |   19 |
| NULL |    Aarohi |   24 |
| NULL |    Lisa   |   20 |
| NULL |    Roy    |   24 |
+------+-----------+------+

现在，让我们尝试使用以下语句在SQL中创建一个序列−

SQL> CREATE SEQUENCE My_Sequence AS INT
   START WITH 1
   INCREMENT BY 1
   MINVALUE 1
   MAXVALUE 5
   CYCLE;

在上面的查询中，该序列被命名为“ My_Sequence ”，并且从值1开始，每次生成一个值时递增1。该序列的最大值为5，并且在达到最大值时循环回到起始值。

创建序列后，可以使用它来生成唯一的整数值。现在，让我们尝试使用以下查询更新“ ID ”列的STUDENTS表中的数据-

SQL> UPDATE STUDENTS SET ID = NEXT VALUE FOR My_Sequence;

输出

当您执行上述查询时，输出结果如下所示 −

Commands completed successfully.

验证

让我们使用以下查询来验证表中的ID列是否已更新序列：

SQL> SELECT * FROM STUDENTS;

+------+-----------+------+
|  ID  |     NAME  |  AGE |
+------+-----------+------+
|    1 |    Dhruv  |   20 |
|    2 |    Arjun  |   23 |
|    3 |    Dev    |   25 |
|    4 |    Riya   |   19 |
|    5 |    Aarohi |   24 |
|    1 |    Lisa   |   20 |
|    2 |    Roy    |   24 |
+------+-----------+------+

使用AUTO_INCREMENT列

在MySQL中使用序列的最简单方式是将列定义为AUTO_INCREMENT，并让MySQL来处理其余部分。

示例

试试以下示例。这将创建一个表，然后在该表中插入几行，其中不需要提供记录ID，因为它是由MySQL自动递增的。

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)

获取AUTO_INCREMENT的值

LAST_INSERT_ID()是一个SQL函数，因此您可以在任何可以发出SQL语句的客户端中使用它。否则PERL和PHP脚本提供了独立的函数来检索最后一条记录的自增值。

PERL示例

使用 mysql_insertid 属性来获取由查询生成的AUTO_INCREMENT值。通过数据库句柄或语句句柄访问此属性，取决于您如何发出查询。以下示例通过数据库句柄引用它。

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

PHP示例

发出一个生成AUTO_INCREMENT值的查询后，通过调用 mysql_insert_id() 函数来检索该值。

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

重新编号现有序列

有可能存在这样一种情况：您从表中删除了许多记录，想要对所有记录重新编号。可以通过使用一个简单的技巧来实现，但您应该非常小心，并检查一下您的表是否与另一个表进行了连接。

如果确定无法避免重新编号 AUTO_INCREMENT 列，可以通过从表中删除该列，然后再添加该列来完成。

以下示例显示了如何使用此技术对昆虫表中的 id 值进行重新编号。

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

在特定值上开始序列

默认情况下，MySQL会从1开始序列，但您也可以在创建表时指定任何其他数字。

以下代码块是一个示例，其中MySQL将从100开始序列。

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
);

或者，您可以先创建表，然后使用ALTER TABLE设置初始序列值。

mysql> ALTER TABLE t AUTO_INCREMENT = 100;