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 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_INCREMENTfeature in only one column for each table, and the data type of the column should be an integer.
AUTO_INCREMENTcolumn must be indexed, and the indexing should be either PRIMARY or UNIQUE KEY indexing.
AUTO_INCREMENTcolumn 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.
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_INCREMENTcolumn is always
1in 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
- 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_INCREMENTcolumn, MySQL issues a duplicate-key error if the column has a unique index. If you update an
AUTO_INCREMENTcolumn 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
DELETEstatement, 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
7for 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
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.
Second, we will delete a row from the
PRODUCT table whose prod_id is
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.
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
Third, let’s update an existing product whose
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.