Sometimes you may need to generate sequence in MySQL. Since MySQL does not have a built-in function for creating sequence, you need to generate it via SQL query. Here’s how to create sequence in MySQL using MySQL sequence query.
How To Create Sequence in MySQL
Here are the steps to create sequence in MySQL. There are various ways to generate sequence in MySQL.
Create Sequence Using AUTO_INCREMENT
You can simply store your MySQL sequence in a column using AUTO_INCREMENT attribute during table creation. Let’s say you want to create table orders(id, order_date, amount) and store sequence in id column.
mysql> create table orders(id int AUTO_INCREMENT PRIMARY KEY,
order_date date, amount int);
mysql> insert into orders(order_date, amount)
values('2020-08-01', 250),
('2020-08-02',125),
('2020-08-03',300);
mysql> select * from orders;
+----+------------+--------+
| id | order_date | amount |
+----+------------+--------+
| 1 | 2020-08-01 | 250 |
| 2 | 2020-08-02 | 125 |
| 3 | 2020-08-03 | 300 |
+----+------------+--------+
In the above table, we define id column as our primary key and include AUTO_INCREMENT option to automatically increment and store primary key values in it.
Please note, MySQL allows you to have only one AUTO_INCREMENT column per table and that too, as primary key.
In the above INSERT query, we provide only values for order_date and amount columns but not id column but MySQL automatically generates sequence for it.
Bonus Read : How to Compare Two Tables in MySQL
Create Sequence Without Using AUTO_INCREMENT
Let’s say you want to create another sequence column without using AUTO_INCREMENT. In that case, you will need to use the UPDATE statement to mathematically calculate and populate this sequence column.
For example, let us add a sequence column to above table to store a new sequence.
mysql> alter table orders add column sequence int;
Bonus Read : How to Copy Table in MySQL
Next, we use UPDATE statement to create sequence in MySQL. We will create a sequence that is twice id column value
mysql> update orders
set sequence=2*id;
mysql> select * from orders;
+----+------------+--------+----------+
| id | order_date | amount | sequence |
+----+------------+--------+----------+
| 1 | 2020-08-01 | 250 | 2 |
| 2 | 2020-08-02 | 125 | 4 |
| 3 | 2020-08-03 | 300 | 6 |
+----+------------+--------+----------+
Bonus Read : How to Get Last One Month Data in MySQL
We can also create a non-linear sequence by updating the formula used in UPDATE statement. Here’s the SQL query to generate a sequence of Square values (y x y) of id column
mysql> update orders
set sequence=id*id;
mysql> select * from orders;
+----+------------+--------+----------+
| id | order_date | amount | sequence |
+----+------------+--------+----------+
| 1 | 2020-08-01 | 250 | 1 |
| 2 | 2020-08-02 | 125 | 4 |
| 3 | 2020-08-03 | 300 | 9 |
+----+------------+--------+----------+
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.