MySQL implements a sequence similar to Oracle

MySQL implements a sequence similar to Oracle

2013-10-22 10:33:35 I'm going to say two footsteps.

MySQL implements Oracle-like sequence Oracle general use sequence (Sequence) to handle primary key fields, while MySQL provides self-growth (increment) for similar purposes, but in practice it is found that There are many drawbacks to MySQL's self-growth: the inability to control step size, start indexing, whether to cycle, and so on, if you need to migrate the database, it is also a big problem for the primary key. This document describes a scenario for simulating an oracle sequence, with an emphasis on ideas, code second. The use of Oracle sequences is nothing more than using. Nextval and. Currval pseudo-columns, the basic idea is: 1, a new table in MySQL, used to store the sequence name and value, 2, to create a function to get the values in the sequence table, as follows: Table structure for the [SQL] table structure is:?

1234567

drop table if exists sequence;   create table sequence (       seq_name        VARCHAR(50) NOT NULL, -- 序列名称       current_val     INTNOT NULL, --当前值       increment_val   INTNOT NULLDEFAULT 1, --步长(跨度)       PRIMARY KEY (seq_name)   );

Implementation of the Currval simulation program?

1234567891011

[sql]  create function currval(v_seq_name VARCHAR(50))   returns integerbegin     declare value integer;       set value = 0;       select current_value into value       from sequence     where seq_name = v_seq_name;       return value;   end;

The [SQL] function is used as: Select Currval (' Movieseq '); Implementation of the Nextval simulation program?

123456789

[sql]  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;

The [SQL] function is used as: Select Nextval (' Movieseq '); Add a function to set the value?

12345678

[sql]  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 increase the function of the step operation, which is no longer described here.

MySQL implements a sequence similar to Oracle