MySQL Realizes Sequence Similar to Oracle
- 2021-11-14 07:22:59
- OfStack
MySQL implements a sequence similar to Oracle
Oracle1 uses sequences (Sequence) to handle primary key fields, while MySQL provides self-growth (increment) for similar purposes;
However, in the actual use process, it is found that the self-growth of MySQL has many drawbacks: it can't control the step size, start indexing, whether it is circular or not, etc.; If you need to migrate the database, it is also a big problem for the primary key.
This paper records a scheme to simulate Oracle sequence, with emphasis on ideas and code second.
The use of Oracle sequence is nothing more than the use of. nextval and.currval pseudo-columns. The basic idea is:
1. Create a new table in MySQL to store sequence names and values;
2. Create a function to get the values in the sequence table;
The details are as follows:
The table structure is:
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 length ( Span )
PRIMARY KEY (seq_name)
);
Simulation scheme for realizing currval
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;
Function is used as:
select currval('MovieSeq');
Simulation scheme for realizing nextval
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;
Function is used as:
select nextval('MovieSeq');
Function to increase setting value
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, the function of step size operation can be added, which will not be described here.
Pay attention to the syntax, and the database fields should correspond to the
use bvboms;
DELIMITER $$
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);
end $$
DELIMITER $$
Summarize