Shulou(Shulou.com)06/01 Report--

MySQL implements Oracle-like sequences

Oracle generally uses Sequence to handle primary key fields, while MySQL provides increment for similar purposes

However, in the process of practical use, it is found that the self-growth of MySQL has many disadvantages: it can not control the step size, start the index, whether the loop and so on; if you need to migrate the database, it is also a big problem for the primary key.

This article records a scheme to simulate Oracle sequences, focusing on ideas, followed by code.

The use of Oracle sequences is nothing more than using .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 ordered list

The details are as follows:

The structure of the table 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 (span) PRIMARY KEY (seq_name))

A 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 = vaccounseqroomname; return value; end

The function used is: select currval ('MovieSeq')

A 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 = vaccounseqroomname; return currval (v_seq_name); end

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

A function that increases the set 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 = vaccounseqroomname; return currval (seq_name)

By the same token, functions for step size operations can be added, which are not described here.

Pay attention to the syntax. 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 = return currval (seq_name); end $$DELIMITER $$

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.