How to create and use sequences in MySQL?

To create a sequence in MySQL, you can use auto_increment on a column. it starts from The value is 1, and is incremented by 1 each time a record is inserted.

First, create a table with CREATE table. The query is as follows -

mysql> CREATE table SequenceDemo
-> (
-> SequenceId int auto_increment,
-> primary key(SequenceId)
-> );
Query OK, 0 rows affected (1.22 sec)

After creating the table, you can use the insert command to insert records The following is given -

mysql> INSERT into SequenceDemo values();
Query OK, 1 row affected (0.19 sec)

mysql> INSERT into SequenceDemo values();
Query OK, 1 row affected (0.14 sec)

mysql> INSERT into SequenceDemo values();
Query OK, 1 row affected (0.10 sec)

mysql> INSERT into SequenceDemo values();
Query OK, 1 row affected (0.12 sec)

mysql> INSERT into SequenceDemo values();
Query OK, 1 row affected (0.09 sec)

After inserting the record, you can use the select statement to display the record, that is Given below -

mysql> SELECT * from SequenceDemo;

Following is the output obtained -

+------------+
| SequenceId |
+------------+
| 1          |
| 2          |
| 3          |
| 4          |
| 5          |
+------------+
5 rows in set (0.00 sec)

The sequence can be set with the help of alter command. Its syntax is as follows Now, the above syntax is used in the following query to set the sequence value as -

alter table yourTableName auto_increment=Somevalue;

Now, the above syntax is used in the following query to set the sequence value as -

mysql> alter table SequenceDemo auto_increment = 500;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

After that, records are inserted starting from the value 500 in the table. Given below-

mysql> INSERT into SequenceDemo values();
Query OK, 1 row affected (0.15 sec)

mysql> INSERT into SequenceDemo values();
Query OK, 1 row affected (0.15 sec)

mysql> INSERT into SequenceDemo values();
Query OK, 1 row affected (0.05 sec)

All the records can be displayed with the select statement as is given below-

mysql> SELECT * from SequenceDemo;

The output is as follows

+------------+
| SequenceId |
+------------+
| 1          |
| 2          |
| 3          |
| 4          |
| 5          |
| 500        |
| 501        |
| 502        |
+------------+
8 rows in set (0.00 sec)

As can be seen from the above output, in 5 After records, the sequence id starts from 500 and is Increase by 1.

The above is the detailed content of How to create and use sequences in MySQL?. For more information, please follow other related articles on the PHP Chinese website!