MySQL Sequence

Sequences are frequently used in databases because many applications require that each row in a table contain a unique value. To use sequences in MySQL, all you need to do is to add AUTO_INCREMENT clause to a column in a MySQL table.
 

Example of MySQL Sequence:

First create a table called “Persons”.

CREATE TABLE Persons (
    PersonID int AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    Age int,
    Address VARCHAR(100)
);

 
Then inserts some rows into this table, where it is not necessary to mention the “PersonID” column because it is automatically incremented by MySQL.

INSERT INTO Persons (PersonID, Name, Age, Address) 
VALUES (NULL, 'Alex', 25, '819 Saint Francis Way');

INSERT INTO Persons (PersonID, Name, Age, Address) 
VALUES (NULL, 'Emily', 15, '171 Jarvisville Road Michigan');

INSERT INTO Persons (PersonID, Name, Age, Address) 
VALUES (NULL, 'Jean', 35, '188 Clay Street Indiana');

INSERT INTO Persons (PersonID, Name, Age, Address) 
VALUES (NULL, 'Bob', 40, '285 Java Lane Missouri');

Then display data inserted into “Persons” table. As you can see the “PersonID” is automatically generated by MySQL.

> SELECT * FROM Persons;

+----------+-----------+--------+-------------------------------+
| PersonID |    Name   |  age   |             Address           |
+----------+-----------+--------+-------------------------------+
|    1    |    Alex   |   25   | 819 Saint Francis Way         |
|    2    |   Emily   |   15   | 171 Jarvisville Road Michigan |
|    3    |   Jean    |   35   | 188 Clay Street Indiana       |
|    4    |    Bob    |   40   | 285 Java Lane Missouri        |
+----------+-----------+--------+-------------------------------+

 

Get the AUTO_INCREMENT value

The function LAST_INSERT_ID() is an SQL function, which returns the AUTO_INCREMENT identifier of the last row inserted or updated in a table:

SELECT LAST_INSERT_ID();

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More