How to create a sequence column in MySQL

Posted on 16th March 2020

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.


Post a comment

Comments

Nothing yet..be the first to share wisdom.