mysql
  1. mysql-sequence
PreviousNext

Sequence - ( MySQL Misc )

In MySQL, a sequence is a database object that generates a sequence of numeric values. Unlike an identity column in SQL Server, a sequence can be defined separately from a table and can be referenced by multiple tables. In this tutorial, we'll discuss how to create, use, and drop sequences in MySQL.

Syntax

The syntax for creating a sequence in MySQL is as follows:

CREATE SEQUENCE sequence_name
    [START WITH n]
    [INCREMENT BY n]
    [MAXVALUE n | NOMAXVALUE]
    [MINVALUE n | NOMINVALUE]
    [CYCLE | NOCYCLE]

Here's an example of creating a sequence named "my_sequence" with a start value of 1, an increment of 1, and a maximum value of 100:

CREATE SEQUENCE my_sequence
    START WITH 1
    INCREMENT BY 1
    MAXVALUE 100;

Example

Let's say we want to create a sequence that generates order IDs for an online store. We can create a sequence named "order_id_seq" using the following SQL command:

CREATE SEQUENCE order_id_seq;

To use the sequence, we can call the NEXTVAL function to get the next value in the sequence:

INSERT INTO orders (order_id, customer_id, total_amount)
VALUES (NEXTVAL('order_id_seq'), 1, 100.00);

This will insert a new row into the "orders" table with an order ID retrieved from the "order_id_seq" sequence.

Output

When we run the example code above and query the "orders" table, we should see a new row with a unique order ID generated from the sequence.

Explanation

In the example above, we created a sequence named "order_id_seq" and used the NEXTVAL function to retrieve the next value in the sequence. We then inserted this value into the "order_id" column of a new row in the "orders" table.

Use

Sequences are useful for generating unique numeric values that can be shared across tables. They can be used to generate primary keys, order IDs, invoice numbers, and more.

Important Points

  • Sequences can be defined separately from a table and can be referenced by multiple tables.
  • The NEXTVAL function is used to retrieve the next value in a sequence.
  • Sequences can be customized to specify the start value, increment, maximum value, minimum value, and cycling behavior.

Summary

In this tutorial, we discussed how to create and use sequences in MySQL. We covered the syntax, example, output, explanation, use, and important points of sequences in MySQL. With this knowledge, you can now use sequences to generate unique numeric values for your MySQL databases and applications.

ExplainJSON
Published on: