Sử dụng Sequence trong SQL

Một Sequence là một tập hợp các số nguyên 1, 2, 3, … mà được tạo theo nhu cầu. Các Sequence thường xuyên được sử dụng trong Database bởi vì nhiều ứng dụng yêu cầu mỗi hàng trong bảng chứa một giá trị duy nhất, và các Sequence cung cấp một cách dễ dàng để tạo chúng.

Chương này trình bày cách sử dụng của Sequence trong MySQL.

Sử dụng AUTO_INCREMENT column

Cách đơn giản nhất trong MySQL để sử dụng các Sequence là để định nghĩa một cột dạng AUTO_INCREMENT và để phần còn lại cho MySQL xử lý.

Ví dụ

Ví dụ sau sẽ tạo bảng và sau đó nó sẽ chèn một số hàng vào trong bảng này, mà không bắt buộc cung cấp bản ghi ID bởi vì nó được tự động tăng bởi 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)

Lấy các giá trị AUTO_INCREMENT

Hàm LAST_INSERT_ID() là một hàm SQL, vì thế, bạn có thể sử dụng nó từ bên trong bất kỳ Client nào mà hiểu cách thông báo lệnh SQL. Riêng PERL và PHP Script cung cấp các hàm riêng để lấy giá trị AUTO_INCREMENT của bản ghi cuối.

Ví dụ cho PERL

Sử dụng thuộc tính mysql_insertid để lấy giá trị AUTO_INCREMENT được tạo bởi một truy vấn. Thuộc tính này được truy cập thông qua hoặc một Database Handle hoặc một Statement Handle, phụ thuộc vào cách bạn thông báo truy vấn. Ví dụ sau tham chiếu nó thông qua Database Handle.

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

Ví dụ cho PHP

Sau khi thông báo một truy vấn mà tạo một giá trị AUTO_INCREMENT, lấy giá trị này bằng cách gọi hàm 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);

Đánh số lại một Sequence đang tồn tại

Có một tình huống khi bạn đã xóa nhiều bản ghi từ một bảng và bạn muốn đánh số lại tất cả các bản ghi. Điều này được thực hiện bởi sử dụng một mẹo đơn giản, nhưng bạn nên cẩn thận khi thực hiện nếu trong trường hợp bảng của bạn đang kết hợp với bảng khác.

Nếu bạn quyết định rằng việc đánh số lại một cột AUTO_INCREMENT là không thể tránh khỏi, thì cách để thực hiện là xóa cột từ bảng, sau đó thêm lại. Ví dụ sau minh họa cách đánh số lại các giá trị id trong bảng INSECT bởi sử dụng kỹ thuật này.

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

Bắt đầu một Sequence tại một giá trị cụ thể

Theo mặc định, MySQL sẽ bắt đầu Sequence từ 1, nhưng bạn có thể xác định bất kỳ số nào khác bạn muốn tại thời điểm tạo bảng. Ví dụ sau sẽ bắt đầu Sequence từ 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
);

Cách khác, bạn có thể tạo bảng và sau đó thiết lập giá trị khởi tạo cho Sequence với lệnh ALTER TABLE.

mysql> ALTER TABLE t AUTO_INCREMENT = 100;