MySQL implements a sequence similar to Oracle

Welcome to the Linux community forum and interact with 2 million technical staff to access Oracle. Generally, Sequence is used to process primary key fields, while MySQL provides increment for similar purposes; however, in actual use, it is found that MySQL's self-growth has many drawbacks: it cannot control the step size, start index, and whether it is a loop.

Welcome to the Linux community forum and interact with 2 million technical staff> in Oracle, Sequence is generally used to process primary key fields, while MySQL provides increment) but in actual use, we found that MySQL's auto-growth has many drawbacks: unable to control the step size, start the index, and whether it is a loop.

Welcome to the Linux community forum and interact with 2 million technicians>

Oracle generally uses Sequence to process primary key fields, while MySQL provides increment for similar purposes;

However, in actual use, it is found that MySQL's auto-growth has many drawbacks: it cannot control the step size, start the index, and whether it is a loop. If you need to migrate the database, for the primary key, this is also a big problem.

This article records a solution for simulating Oracle sequences, focusing on ideas and code.

The use of Oracle sequence is nothing more than use. Nextval and. The basic idea of the currval pseudo column is: 1. Create a table in MySQL to store the sequence name and value; 2. Create a function to obtain the values in the sequence table;

The details are as follows:

The table structure is

[SQL]

Table Structure:

Drop table if exists sequence;

Create table sequence (

Seq_name

VARCHAR (50) not null, -- sequence name

Current_val

INT

Not null, -- Current Value

Increment_val

INT

NOT NULL

DEFAULT 1, -- step size (SPAN)

Primary key (seq_name ));

Implementation of currval simulation scheme

[SQL]

Create function currval (v_seq_name VARCHAR (50 ))

Returns integer begin

Declare value integer;

Set value = 0;

Select current_value into value

From sequence

Where seq_name = v_seq_name;

Return value;

End;

[SQL]

Function usage: select currval ('movieseq ');

Implementation of nextval simulation scheme

[SQL]

Create function nextval (v_seq_name VARCHAR (50 ))

Return integer

Begin

Update sequence

Set current_val = current_val + increment_val

Where seq_name = v_seq_name;

Return currval (v_seq_name );

End;

[SQL]

Function usage: select nextval ('movieseq ');

Add a set Value Function

[SQL]

Create function setval (v_seq_name VARCHAR (50), v_new_val INTEGER)

Returns integer begin

Update sequence

Set current_val = v_new_val

Where seq_name = v_seq_name;

Return currval (seq_name );

Similarly, you can add a function for Step operations, which is not described here.