MySQL Sequence

Summary: in this tutorial, you will learn how to use the MySQL AUTO_INCREMENT feature to automatically generate a sequence for the column of MySQL tables.

What is a Sequence?

A sequence is a series of numbers such as 1,2,3, … that are generated on demand in a particular order followed by some rule. Sequences are used in the database to generate unique numbers. In a relational database, many times we need to generate unique numbers for a column such as a roll number in the student table, employee number in the employee table, and order id in the order table, etc. We need a mechanism to generate these unique numbers automatically in order to fulfill this need.

MySQL Sequence

MySQL does not provide any built-in function to generate unique numbers for the table’s column. However, it has one column attribute named AUTO_INCREMENT, using that we can easily generate unique numbers for a column.

Creating Sequence Using AUTO_INCREMENT

The easiest way to generate a sequence in MySQL is by adding the AUTO_INCREMENT attribute for a column that is generally a primary key during table creation.

The following rules should be remembered when we use AUTO_INCREMENT for a column:

  • We can use the AUTO_INCREMENT feature in only one column for each table, and the data type of the column should be an integer.
  • The AUTO_INCREMENT column must be indexed, and the indexing should be either PRIMARY or UNIQUE KEY indexing.
  • The AUTO_INCREMENT column should be declared as NOT NULL. However, MySQL adds NOT NULL constraint to the column implicitly when we use the AUTO_INCREMENT feature for the column.

Creating MySQL Sequence Example

Till now you have learned about the theoretical aspects of MySQL Sequence. Let’s understand the concept now with the help of a practical example.

First, we will create a new table and will make sure that one column will be having AUTO_INCREMENT attribute. Also, the column is a primary key.

You can use the following statement to create a new table.

CREATE TABLE PRODUCT (
  prod_id int AUTO_INCREMENT,
  prod_name varchar(45) NOT NULL,
  unit_price decimal(10,2) NOT NULL,
  description varchar(50) DEFAULT '',
  primary key(prod_id)
);

Second, insert a few rows into the table. No need to provide any value for the prod_id as the value for this column is generated by MySQL itself.

INSERT INTO PRODUCT (prod_name,unit_price,description) 
  VALUES ('Mouse','10.00','Optical Mouse'),
  ('Keyboard','15.50','Wareless Mouse'),
  ('17 Inch Monitor','105.00','17 inch monitor'),
  ('21 Inch Monitor','125.00','');

Third, verify the records from the table using the SELECT statement as below.

SELECT * FROM PRODUCT;

Following is the visual representation of all the steps performed above.

MySQL Sequence

In the above example, we have defined the prod_id column as a primary key with AUTO_INCREMENT option. This AUTO_INCREMENT feature automatically increments the column value when a new row is inserted and maintains the uniqueness among the rows.

When we execute the INSERT query, we do not provide any values for the prod_id column, but MySQL automatically generates a sequence for it.

How MySQL Sequence Works

Following are the important points that elaborate the working of the MySQL Sequence.

  • The starting value of the AUTO_INCREMENT column is always 1 in MySQL, if you insert a NULL value or do not insert any value while inserting a row.
  • To obtain the last generated Sequence number by MySQL, you can use the LAST_INSERT_ID() function. We generally use the last insert ID for a subsequent statement as the last generated ID Sequence is unique across the session. For example, if any other session is connected and generated the sequence number, from your session you get that by using the LAST_INSERT_ID() function.
  • During inserting of a new row if we specify a value for the sequence column, then MySQL will insert the value if the value is unique otherwise it throws an error. In another scenario, if we insert a value greater than the next sequence number, MySQL will insert the value and consider it as the starting sequence number for the subsequent insert statement. Please note that it will create gaps in the sequence.
  • If you use the update statement to update the value which already exists for the AUTO_INCREMENT column, MySQL issues a duplicate-key error if the column has a unique index. If you update an AUTO_INCREMENT column with a value that is greater than the existing values, MySQL will use the next number of the last insert sequence number for the next row. For example, the AUTO_INCREMENT column’s last sequence value is 5, and you want to update it with 10, then the sequence number for the next row is 6, not 11.
  • If you delete the last inserted row using the DELETE statement, it is not necessary that MySQL will reuse the removed sequence number again because it depends on the table’s storage engine. For example, if you use the MyISAM or InnoDB table and remove the last inserted sequence for example 6, MySQL still inserts the next sequence number as 7 for the new row.

After setting up the AUTO_INCREMENT attribute for a column, you can reset the auto-increment value in various ways e.g., by using the ALTER TABLE statement.

Let us look at some more examples for a better understanding of the use of the MySQL sequence.

First, insert two new rows into the PRODUCT table.

INSERT INTO PRODUCT (prod_id,prod_name,unit_price,description) VALUES 
(7,'4GB RAM','20.00',''),
(8,'8GB RAM','30.00','');

And, use the SELECT statement to verify the records.

MySQL Sequence

Second, we will delete a row from the PRODUCT table whose prod_id is 8.

DELETE FROM PRODUCT where prod_id=8;

Again, we will insert a new row into the PRODUCT table as below.

INSERT INTO PRODUCT (prod_name,unit_price,description) VALUES
('16GB RAM','50.00','16GB Samsung RAM');

You can execute the SELECT statement against the PRODUCT table to verify the output.

MySQL Sequence

From the above example, you can notice that MySQL does not reuse the deleted sequence number. It is because the storage engine of the PRODUCT table is InnoDB. The InnoDB and MyISAM do not reuse the deleted sequence. Therefore, the insert query will add the new sequence in the PRODUCT table as 9.

Third, let’s update an existing product whose prod_id is 9 to the prod_id = 7:

UPDATE PRODUCT SET prod_name='8GB RAM', prod_id=7 WHERE prod_id=9;

MySQL gives an error: ERROR 1062 (23000): Duplicate entry ‘7’ for key ‘product.PRIMARY’. We can fix it as below.

UPDATE PRODUCT SET prod_name='8GB RAM', prod_id=8 WHERE prod_id=9;

Refer to the below image.

MySQL Sequence