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:
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:
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.
Now execute the SELECT statement to verify the records:
We can see the results in the below image.
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:
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.
And execute the SELECT statement to verify the output:
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:
We will execute the SELECT statement again to see the output:
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.