MySQL implements sequence_mysql similar to Oracle

MySQL implements a sequence like Oracle bitsCN.com

MySQL implements a sequence similar to Oracle

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.

BitsCN.com