SEQUENCE in SQL

SEQUENCE is a set of integers created in order. The SEQUENCE is often used in databases because meeting the requirements of many applications is to require that each row in a table contain a unique value similar to the primary key.

The article will give you the syntax, usage and examples of SEQUENCE in MySQL.

Use the AUTO_INCREMENT column

The simplest way in MySQL to use the SEQUENCE is to define a column as AUTO_INCREMENT.

Example : Create a table and insert some rows into this table, it is not required to provide ID because it is automatically numbered by MySQL.

1. Create NHANVIEN table :

 mysql> CREATE TABLE NHANVIEN ( 
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  PRIMARY KEY (id), 
  ten VARCHAR(30) NOT NULL, 
  ngaysinh DATE NOT NULL, 
  quequan VARCHAR(30) NOT NULL 
 ); 
 
 Query OK, 0 rows affected (0.02 sec) 

1. Insert data into the table:

 mysql> INSERT INTO NHANVIEN (id,ten,ngaysinh,quequan) VALUES 
  (NULL,'Huy','1999-06-23','Hanoi'), 
 INSERT INTO NHANVIEN (id,ten,ngaysinh,quequan) VALUES 
  (NULL,'Hung','1990-05-21','Haiduong'), 
 INSERT INTO NHANVIEN (id,ten,ngaysinh,quequan) VALUES 
  (NULL,'Huyen','1995-02-12','Hanoi'); 
 
 Query OK, 3 rows affected (0.02 sec) 
 Records: 3 Duplicates: 0 Warnings: 0 

1. Select data from NHANVIEN table :

mysql> SELECT * FROM NHANVIEN ORDER BY id; +----+-------------+------------+------------+ | id | ten | ngaysinh | quequan | +----+-------------+------------+------------+ | 1 | Huy | 1999-06-23 | Hanoi | | 2 | Hung | 1990-05-21 | Haiduong | | 3 | Huyen | 1995-02-12 | Hanoi | +----+-------------+------------+------------+ 3 rows in set (0.00 sec) 

Changing the serial number of a SEQUENCE exists

There will be cases where you have to delete multiple records from a table and want to reorder the records. To do this you just need to use a simple tip, but be careful and check if your table is joining another table.

If you have decided to renumber an AUTO_INCREMENT column, you do so by deleting this column from the table, then adding it again. The following example illustrates how to renumber id values ​​in the NHANVIEN table by using this technique.

The following example illustrates how to re-number id values ​​in the NHANVIEN table using this technique:

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

Start SEQUENCE at a specific value

By default, MySQL will start SEQUENCE from 1, but you can also specify any other number at the time of creating the table.

The following example will start SEQUENCE from 100:

 mysql> CREATE TABLE NHANVIEN ( 
  id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100, 
  PRIMARY KEY (id), 
  ten VARCHAR(30) NOT NULL, 
  ngaysinh DATE NOT NULL, 
  quequan VARCHAR(30) NOT NULL 
 ); 

Alternatively, you can create the table and then set the initialization value for SEQUENCE with the ALTER TABLE command.

 mysql> ALTER TABLE t AUTO_INCREMENT = 100; 

Previous article: Subquery - SUBQUERY in SQL