MySQL implements a solution similar to Oracle sequence

MySQL implements Oracle-like sequences

Oracle generally uses sequences to process primary key fields, while MySQL provides increment to achieve similar purposes.

However, in actual use, it is found that MySQL's auto-increment has many disadvantages: it is impossible to control the step size, start index, loop, etc.; if the database needs to be migrated, the primary key is also a big problem.

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

The use of Oracle sequences is nothing more than using the .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 value 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)  
);

Implementing a simulation of 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 usage: select currval('MovieSeq');

Implementing the simulation scheme of 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;

The function is used as: select nextval('MovieSeq');


Function that increases the value of a setting

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, functions for step size operations can be added, which will not be described here.

Pay attention to the syntax, the database fields should correspond to

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

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links