MySQL SEQUENCE

A sequence in MySQL is an arrangement of integers generated in the ascending order (1, 2, 3, and so on) on specific demand. Sequences are used in the databases to generate unique numbers. Many applications require each row of a table to contain a distinct value, such as student roll number in student_table, employee numbers in HR, customer ID in CRM, etc. To fulfill this type of arrangement, we use sequences that provide an easy way to generate them.

MySQL does not provide any built-in function to create a sequence for a table’s rows or columns. But we can generate it via SQL query. In this article, we are going to describe how to create a sequence in MySQL using SQL query.

Create Sequence Using AUTO_INCREMENT

The simplest way for creating a sequence in MySQL is by defining the column as AUTO_INCREMENT during table creation, which should be a primary key column.

The following are the rules which should be considered when we use the AUTO_INCREMENT attribute for the column:

We can create only one AUTO_INCREMENT column in each table, and the data type of this column is an integer.
The AUTO_INCREMENT column should also have either PRIMARY or UNIQUE KEY indexing.
The AUTO_INCREMENT column must contain NOT NULL However, MySQL automatically adds the NOT NULL constraint to the column implicitly when we set the column as an AUTO_INCREMENT attribute.

Example:

Let us understand it with the help of the following example. First, we need to create a new table and make sure that there is one column with the AUTO_INCREMENT attribute and that too, as PRIMARY KEY.

Execute the below query to create a table:

  mysql> CREATE TABLE Insects (     Id INT UNSIGNED NOT NULL AUTO_INCREMENT,     PRIMARY KEY (id),     Name VARCHAR(30) NOT NULL,      Type VARCHAR(30) NOT NULL,     Origin VARCHAR(30) NOT NULL   );  

Next, we will insert a few rows into this table where no need to provide the id for each row because it is auto-incremented by MySQL.

  mysql> INSERT INTO Insects (Name, Type, Origin) VALUES  (‘Cockroach’, ‘Crawling’, ‘Kitchen’),  (‘Mosquito’, ‘Flying’, ‘Driveway’),  (‘Spider’ ,’Crawling’, ‘Court yard’),  (‘Grasshopper’, ‘Flying’, ‘Front yard’);  

Now execute the SELECT statement to verify the records:

We can see the results in the below image.

MySQL SEQUENCE

In the above image, we have defined the Id column with PRIMARY KEY and AUTO_INCREMENT option that automatically incremented this column and always stored unique values in it.

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

How MySQL Sequence Works?

The AUTO_INCREMENT column in MySQL contains the following attributes:

The AUTO_INCREMENT column’s starting value is 1. This column is always incremented by 1 when we omit its value in the INSERT statement or insert a NULL
We can use the LAST_INSERT_ID() function to get the last generated sequence number. However, we can also use the last insert ID for the subsequent statements that should be unique across sessions.
If we will insert a new row into a table along with specifying a value for the sequence column, then MySQL first checks it whether the specified value has already existed or not. If it does not exist, it will insert the sequence number in the column; otherwise, issue an error. Again, if we insert a value greater than the next sequence number, MySQL will use it as the starting sequence number. Now, MySQL will generate the next sequencing value from the current sequence number. It is to note that it will create gaps in our sequence.
If we update the AUTO_INCREMENT column’s value that already exists by using the UPDATE statement, MySQL will issue a duplicate-key error if the column stores only distinct value. If we update an AUTO_INCREMENT column with a value greater than the existing values, MySQL inserts the next value of the last sequence number for the next row. For example, the AUTO_INCREMENT column’s last sequence value is 3, and we want to update it with 10, then the sequence number for the next row should be 4.
If we want to 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 we use the MyISAM table and remove the last insert Id that is 5, MySQL still inserts the next sequence number as 6 for the new row.

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

Insert two new records into the table.

  mysql> INSERT INTO Insects (Id, Name, Type, Origin) VALUES  (6, ‘Bee’, ‘Flying’, ‘Court yard’),  (7, ‘Ant’, ‘Crawling’, ‘Front yard’);  

And execute the SELECT statement to verify the output:

MySQL SEQUENCE

Next, we will delete the insect whose id is 6 using the below query:

Again, we will insert a new row into the table with the below statement:

  mysql> INSERT INTO Insects (Name, Type, Origin) VALUES  (‘Cricket’, ‘Crawling’, ‘Front yard’);  

We will execute the SELECT statement again to see the output:

MySQL SEQUENCE

In the above image, we can see that MySQL does not reuse the deleted sequence number. It is because the storage engine of the Insects table is InnoDB. Therefore, the insert query will add the new sequence in the Insects table as 8.

Now, we will update an existing insect whose Id is 3 to the Id = 2:

MySQL issued an error: Duplicate entry ‘2’ for key ‘insects.PRIMARY’ column. Let’s fix it

See the below image.

MySQL SEQUENCE

Next TopicMySQL JSON

MySQL Sequence

MySQL SEQUENCE

Create Sequence Using AUTO_INCREMENT

How MySQL Sequence Works?

MySQL regexp_instr() function

Learn MySQL Tutorial

You may also like