MySQL implements a solution similar to Oracle sequence

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

You may also be interested in:
  • Detailed explanation of the solution for real-time synchronization from MySQL to Oracle
  • Summary of commonly used multi-table modification statements in Mysql and Oracle
  • Summary of the differences between MySQL and Oracle (comparison of functional performance, selection, SQL when using them, etc.)
  • Import backup between mysql database and oracle database
  • The implementation process of extracting oracle data to mysql database
  • Comparative Analysis of High Availability Solutions of Oracle and MySQL
  • Comparison of the differences between MySQL and Oracle: six triggers
  • Comparison of the differences between MySQL and Oracle: Five: Stored Procedures & Functions
  • MySQL and Oracle differences comparison three functions
  • Detailed explanation of the misunderstanding between MySQL and Oracle

<<:  How to implement MySQL master-slave replication based on Docker

>>:  JavaScript to achieve simple tab bar switching case

Recommend

MySQL installation tutorial under Centos7

MySQL installation tutorial, for your reference, ...

Analysis of Difficulties in Hot Standby of MySQL Database

I have previously introduced to you the configura...

JS thoroughly understands GMT and UTC time zones

Table of contents Preface 1. GMT What is GMT Hist...

Detailed explanation of jQuery's copy object

<!DOCTYPE html> <html lang="en"...

Vue components dynamic components detailed explanation

Table of contents Summarize Summarize When the ar...

MySQL table type storage engine selection

Table of contents 1. View the storage engine of t...

Vue implements form validation function

This article mainly describes how to implement fo...

Graphical explanation of the function call of proto file in Vue

1. Compile proto Create a new proto folder under ...

Docker network mode and configuration method

1. Docker Network Mode When docker run creates a ...

Summary of various forms of applying CSS styles in web pages

1. Inline style, placed in <body></body&g...

Vue template configuration and webstorm code format specification settings

Table of contents 1. Compiler code format specifi...

Bundling non-JavaScript static resources details

Table of contents 1. Custom import in packaging t...

HTML basics HTML structure

What is an HTML file? HTML stands for Hyper Text M...

Node+Express test server performance

Table of contents 1 Test Environment 1.1 Server H...