A sequence column, also known as an auto-increment column, in a database table is a column that gets a unique value generated automatically whenever a new row is inserted. Unlike in earlier versions of Oracle and some other databases, a sequence column in MySQL can be created very easily using the AUTO_INCREMENT attribute.
When you create a column with AUTO_INCREMENT attribute, MySQL will automatically assign a sequence number to that particular column. Here is an example:
CREATE TABLE employee ( employee_no SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, employee_name VARCHAR(40) NOT NULL, job_title VARCHAR(40), PRIMARY KEY employee_pk(employee_no) );
The CREATE TABLE statement in the above example creates a table named employee with three fields - employee_no, employee_name and job_title. ALso the employee_no column is defined as auto-increment column.
We can insert a record into this database table using the insert query like below.
insert into employee(employee_name,job_title) values('Jack Dorsey', 'CEO');
Now if you query the records in the employee table you will find that employee_no column also has got a value even though we have only specified values for employee_name and job_title columns in the insert query.
select * from employee; +-------------+---------------+-----------+ | employee_no | employee_name | job_title | +-------------+---------------+-----------+ | 1 | Jack Dorsey | CEO | +-------------+---------------+-----------+ 1 row in set (0.00 sec)
Try inserting another record on the employee table.
insert into employee(employee_name,job_title) values('Lisa Brummel', 'Chief People Officer');
select * from employee;
+-------------+---------------+----------------------+
| employee_no | employee_name | job_title |
+-------------+---------------+----------------------+
| 1 | Jack Dorsey | CEO |
| 2 | Lisa Brummel | Chief People Officer |
+-------------+---------------+----------------------+
2 rows in set (0.00 sec)
The value for employee_no column was automatically incremented to 2 when the new record was inserted.
Starting auto-increment from a specific value
In the above example the value for employee_no columns starts with 1 and increment by 1. If you want employee numbers to start from a different value, say 100, you can do so by assigning that value to AUTO_INCREMENT table option in CREATE TABLE statement.
CREATE TABLE employee
( employee_no SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
employee_name VARCHAR(40) NOT NULL,
job_title VARCHAR(40),
PRIMARY KEY employee_pk(employee_no)
)AUTO_INCREMENT = 100;
Server System Variables
There are two system variables that can be used to modify the default behaviour of auto-increment functionality in MySQL. These variables are auto_increment_increment and auto_increment_offset
- auto_increment_offset defines the start value of auto_increment column. Default start value is 1.
- auto_increment_increment defines the difference between two auto_increment values(step). Default is increment by 1.
Run the following query to view the currently set values for these variables.
show variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.01 sec)
You can assign values to the variable using SET.
SET @@auto_increment_offset=5; SET @@auto_increment_increment=2;
The above statements sets the start value of auto-increment columns to 5 and the values are incremented by 2.
Note that any changes you make to the system variables will change the behaviour of all auto-increment columns in all the tables in the server.
