How To Create Sequence in MySQL

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!

mm

About Ubiq

Ubiq makes it easy to build dashboards, charts & reports for your business. Get insights from data. Track key metrics. Try ubiq for free!