MySQL implements a sequence similar to Oracle

MySQL implements a Sequence similar to Oracle. Oracle generally uses a Sequence to process primary key fields, while MySQL provides an increment for class 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,

MySQL implements a Sequence similar to Oracle. Oracle generally uses a Sequence to process primary key fields, while MySQL provides an increment for class 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,

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

Table Structure: drop table if exists sequence; create table sequence (seq_nameVARCHAR (50) not null, -- sequence name current_valINTNOT NULL, -- current value increment_valINTNOT NULLDEFAULT 1, -- step size (SPAN) primary key (seq_name ));

Implementation of currval simulation scheme

create function currval(v_seq_name VARCHAR(50))returns integerbegindeclare value integer;set value = 0;select current_value into valuefrom sequencewhere seq_name = v_seq_name;return value;end;

Function usage: select currval ('movieseq ');

Implementation of nextval simulation scheme

create function nextval (v_seq_name VARCHAR(50))return integerbegin  update sequence  set current_val = current_val + increment_val  where seq_name = v_seq_name;  return currval(v_seq_name);end;

Function usage: select nextval ('movieseq ');

Add a set Value Function

create function setval(v_seq_name VARCHAR(50), v_new_val INTEGER)returns integerbegin  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.

The above code is not tested, just to express an idea.

PS: I saw a blog post from the other two on the internet, expressing the same meaning and posting the link for your comparison.

Http://hi.baidu.com/sue_spring/item/6dada80adfb98e97a3df43bb

Http://meetrice.iteye.com/blog/89426