A sequence is a set of integers 1, 2, 3, … that are generated in order on a specific demand. Sequences are frequently used in the databases because many applications require each row in a table to contain a unique value and sequences provide an easy way to generate them. 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> <strong>CREATE</strong> <strong>TABLE</strong> Insects ( Id <strong>INT</strong> UNSIGNED NOT NULL AUTO_INCREMENT, <strong>PRIMARY</strong> <strong>KEY</strong> (id), na<strong>me</strong> <strong>VARCHAR</strong>(30) NOT NULL, Type <strong>VARCHAR</strong>(30) NOT NULL, Origin <strong>VARCHAR</strong>(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> <strong>INSERT</strong> <strong>INTO</strong> Insects (<strong>Name</strong>, Type, Origin) <strong>VALUES</strong> ('Cockroach', 'Crawling', 'Kitchen'), ('Mosquito', 'Flying', 'Driveway'), ('Spider' ,'Crawling', 'Court yard'), ('Grasshopper', 'Flying', 'Front yard'); Now execute the SELECT statement to verify the records:
mysql> <strong>SELECT</strong> * <strong>FROM</strong> Insects;
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.
Using AUTO_INCREMENT Column
The simplest way in MySQL to use Sequences is to define a column as AUTO_INCREMENT and leave the remaining things to MySQL to take care.
Example
Try out the following example. This will create table and after that it will insert few rows in this table where it is not required to give record ID because it is auto incremented by MySQL.
mysql> CREATE TABLE insect -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, # type of insect -> date DATE NOT NULL, # date collected -> origin VARCHAR(30) NOT NULL # where collected ); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO insect (id,name,date,origin) VALUES -> (NULL,'housefly','2001-09-10','kitchen'), -> (NULL,'millipede','2001-09-10','driveway'), -> (NULL,'grasshopper','2001-09-10','front yard'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM insect ORDER BY id; +----+-------------+------------+------------+ | id | name | date | origin | +----+-------------+------------+------------+ | 1 | housefly | 2001-09-10 | kitchen | | 2 | millipede | 2001-09-10 | driveway | | 3 | grasshopper | 2001-09-10 | front yard | +----+-------------+------------+------------+ 3 rows in set (0.00 sec)
Obtain AUTO_INCREMENT Values
The LAST_INSERT_ID( ) is a SQL function, so you can use it from within any client that understands how to issue SQL statements. Otherwise, PERL and PHP scripts provide exclusive functions to retrieve the auto incremented value of the last record.
PERL Example
Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value generated by a query. This attribute is accessed through either a database handle or a statement handle, depending on how you issue the query.
The following example references it through the database handle.
$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};PHP Example
After issuing a query that generates an AUTO_INCREMENT value, retrieve the value by calling the mysql_insert_id( ) command.
mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);Renumbering an Existing Sequence
There may be a case when you have deleted many records from a table and you want to re-sequence all the records. This can be done by using a simple trick, but you should be very careful to do so if your table is having joins with the other table. If you determine that the resequencing of an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the column from the table, then add it again.
The following example shows how to renumber the id values in the table using this technique.
mysql> ALTER TABLE insect DROP id; mysql> ALTER TABLE insect -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, -> ADD PRIMARY KEY (id);
Starting a Sequence at a Particular Value
By default, MySQL will start sequence from 1, but you can specify any other number as well at the time of the table creation.
The following program is an example which shows how MySQL will start the sequence from 100.
mysql> CREATE TABLE insect -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, # type of insect -> date DATE NOT NULL, # date collected -> origin VARCHAR(30) NOT NULL # where collected );
Alternatively, you can create the table and then set the initial sequence value with the ALTER TABLE command.
mysql> ALTER TABLE t AUTO_INCREMENT = 100;
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> <strong>INSERT</strong> <strong>INTO</strong> Insects (Id, <strong>Name</strong>, Type, Origin) <strong>VALUES</strong> (6, 'Bee', 'Flying', 'Court yard'), (7, 'Ant', 'Crawling', 'Front yard');
And execute the SELECT statement to verify the output:
Next, we will delete the insect whose id is 6 using the below query:
mysql> <strong>DELETE</strong> <strong>FROM</strong> Insects <strong>WHERE</strong> Id = 6;
Again, we will insert a new row into the table with the below statement:
mysql> <strong>INSERT</strong> <strong>INTO</strong> Insects (<strong>Name</strong>, Type, Origin) <strong>VALUES</strong>. ('Cricket', 'Crawling', 'Front yard'); 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> <strong>UPDATE</strong> Insects <strong>SET</strong> <strong>Name</strong> = 'Bee', Id = 2 <strong>WHERE</strong> Id = 3;
MySQL issued an error: Duplicate entry ‘2’ for key ‘insects.PRIMARY’ column. Let’s fix it
mysql> <strong>UPDATE</strong> Insects <strong>SET</strong> <strong>Name</strong> = 'Bee', Id = 5 <strong>WHERE</strong> Id = 3;
See the below image.
