MySQL - Sequence

MySQL - Sequence

In this tutorial, we will show you how to use MySQL sequence to automatically generateunique numbers for ID columns of tables.

MySQL create sequence

 

In MySQL, a sequence is a list of integers generated in the ascending order i.e., 1,2,3… Many applications need sequences to generate unique numbers mainly for identification e.g., customer ID in CRM, employee number in HR, equipment number in services management system, etc.

 

To create a sequence in MySQL automatically, you set the AUTO_INCREMENT attribute to a column, which typically is primary key column. The following are rules that you must follow when you useAUTO_INCREMENT attribute:

 

  -  Each table has only one AUTO_INCREMENT column whose data type is typically integer or float which is very rare.

  -  The   AUTO_INCREMENT column must be indexed, which means it can be either PRIMARY KEY or UNIQUE index.

  -  The AUTO_INCREMENT column must have NOT NULL constraint. When you setAUTO_INCREMENT attribute to a column, MySQL will make it NOT NULL for you in case you don’t define it explicitly.

 

MySQL create sequence example

 

The following example creates employees table whose emp_no column is AUTO_INCREMENTcolumn:

 

CREATE TABLE employees(

    emp_no INT(4) AUTO_INCREMENT PRIMARY KEY,

    first_name VARCHAR(50),

    last_name  VARCHAR(50)

)ENGINE = INNODB;

 

How MySQL sequence works

 

The AUTO_INCREMENT column has the following attributes:

 

  -  The starting value of an AUTO_INCREMENT column is 1 and it is increased by 1 when you insertNULL value into the column or when you omit its value in the INSERT statement.

  -  To obtain the last generated sequence number, you use the LAST_INSERT_ID() function. You often use the last insert ID for the subsequent statements e.g., insert data into child tables. The last generated sequence is unique across sessions.In other words, if another connection generates a sequence number, from your connection you can obtain it by using the LAST_INSERT_ID()function. For more details on LAST_INSERT_ID() function, check it out the MySQL LAST_INSERT_ID() function tutorial.

  -  If you insert a new row into a table and specify a value for the sequence column, MySQL will insert the sequence number if the sequence number does not exist in the column or issue an error if it already exists. If you insert a new value that is greater than the next sequence number, MySQL will use the new value as the starting sequence number and generate a unique sequence number greater than the current one for the next use. This creates gaps in the sequence.

  -  If you use UPDATE statement to update an AUTO_INCREMENT column to a value that already exists, MySQL will issue a duplicate-key error if the column has a unique index. If you update anAUTO_INCREMENT column to a value that is larger than the existing values in the column, MySQL will use the next number of the last insert sequence number for the next row e.g., if the last insert sequence number is 3, you update it to 10, the sequence number for the new row is 4. See the example in the below section.

  -  If you use DELETE statement to delete the last insert row, MySQL may or may not reuse the deleted sequence number depending on the storage engine of the table. A MyISAM table does not reuse the deleted sequence numbers if you delete a row e.g., the last insert id in the table is 10, if you remove it, MySQL still generates the next sequence number which is 11 for the new row. Similar to MyISAM tables, InnoDB tables do use reuse sequence number when rows are deleted.

 

Once you set AUTO_INCREMENT attribute for a column, you can reset auto increment value in various ways e.g., by using  

ALTER TABLE statement.

 

Let’s practice with the MySQL sequence.

 

First, insert two new employees into the employees table:

 

INSERT INTO employees(first_name,last_name)

VALUES('abc','xyz'),

('xyz','abc');

 

Second, select data from the employees table:

 

SELECT * FROM employees;

 

Third, delete the second employee whose emp_no is 2:

 

DELETE FROM employees WHERE emp_no = 2;

 

Fourth, insert a new employee:

 

INSERT INTO employees(first_name,last_name)

VALUES('abc','xyz');

 

Because the storage engine of the employees table is InnoDB, it does not reuse the deleted sequence number. The new row has emp_no 3.

 

Fifth, update an existing employee with emp_no 3 to 1:

 

UPDATE employees SET first_name = 'abc', emp_no = 1

WHERE emp_no = 3;

 

MySQL issued an error of duplicate entry for the primary key. Let’s fix it:

 

UPDATE employees SET first_name = 'abc', emp_no = 10

WHERE emp_no = 3;

 

Sixth, insert a new employee after updating the sequence number to 10:

 

INSERT INTO employees(first_name,last_name)

VALUES('abc','xyz');

 

The next sequence number of the last insert is 4, therefore MySQL use 4 for the new row instead of 11.

 

In this tutorial, you have learned how to use MySQL sequence to generate unique numbers for a primary key column by assigning the column AUTO_INCREMENT attribute.