MySQL sequence Solution

Differences between MySQL auto-growth and Oracle sequence:
Auto-increment can only be used for one field in the table
Auto-growth can only be assigned to a fixed field of a fixed table, and cannot be shared by multiple tables.
Auto-increment will automatically fill in a field with unspecified or null values.

To add a sequence in MySQL, see the following example:
There is such a table in MySQL:

 
Create Table movie (ID int not null auto_increment, name varchar (60) not null, released year not null, primary key (ID) engine = InnoDB;
 

 

 
Insert into movie (name, released) values ('gladiator', 2000); insert into movie (ID, name, released) values (null, 'The Bourne Identity ', 1998 );
 

 

In ORACLE:

 
Create Table movie (ID int not null, name varchar2 (60) not null, released int not null, primary key (ID); Create sequence movieseq;
 

 

 
Insert into movie (ID, name, released) values (movieseq. nextval, 'gladiator', 2000 );
 

Add a trigger to the table in Oracle to implement the MySQL auto-growth function:

 
Create or replace trigger bri_movie_trgbefore insert on moviefor each rowbegin select movieseq. nextval into: New. ID from dual; end bri_movie_trg; run;
 

In this way, the plug-in record can become a MySQL style:

 
Insert into movie (name, released) values ('the Lion', 1994 );
 

Next let's take a look at how to use ORACLE sequence syntax. nextval and. curval in MySQL data.
We assume that the sequence syntax in MySQL is:

 
Nextval ('sequence '); currval ('sequence'); setval ('sequence ', value );
 

Below isCurrrvalImplementation Scheme:

Drop table if exists sequence; Create Table sequence (name varchar (50) not null, current_value int not null, increment int not null default 1, primary key (name) engine = InnoDB; insert into sequence values ('movieseq ', 3,5); drop function if exists currval; delimiter $ create function currval (seq_name varchar (50) returns integercontains sqlbegin declare value integer; set Value = 0; select current_value into value from sequence where name = seq_name; return value; end $ delimiter;
 

Test results:

Mysql> select currval ('movieseq '); + ------------------- + | currval ('movieseq') | + --------------------- + | 3 | + --------------------- + 1 row in SET (0.00 Sec) mysql> select currval ('x'); + -------------- + | currval ('x') | + ---------------- + | 0 | + -------------- + 1 row in set, 1 warning (0.00 Sec) mysql> show warnings; + --------- + ------ + ---------------- + | level | code | message | + --------- + ------ + ------------------ + | warning | 1329 | no data to fetch | + --------- + ------ + ------------------ + 1 row in set (0.00 Sec)
 

Nextval

 
Drop function if exists nextval; delimiter $ create function nextval (seq_name varchar (50) returns sqlbegin update sequence set current_value = current_value + increment where name = seq_name; return currval (seq_name ); end $ delimiter;
 

 

 
Mysql> select nextval ('movieseq '); + --------------------- + | nextval ('movieseq') | + --------------------- + | 15 | + ------------------- + 1 row in SET (0.09 Sec) mysql> select nextval ('movieseq '); + --------------------- + | nextval ('movieseq') | + --------------------- + | 20 | + ------------------- + 1 row in SET (0.01 Sec) mysql> select nextval ('movieseq '); + --------------------- + | nextval ('movieseq') | + --------------------- + | 25 | + ------------------- + 1 row in SET (0.00 Sec)
 

Setval

 

Drop function if exists setval; delimiter $ create function setval (seq_name varchar (50), value integer) returns integercontains sqlbegin update sequence set current_value = value where name = seq_name; return currval (seq_name); End $ delimiter;
 

 

 
Mysql> select setval ('movieseq ', 150); + ------------------------ + | setval ('movieseq', 150) | + records + | 150 | + ---------------------- + 1 row in SET (0.06 Sec) mysql> select curval ('movieseq '); + ------------------- + | currval ('movieseq ') | + --------------------- + | 150 | + ------------------- + 1 row in SET (0.00 Sec) mysql> select nextval ('movieseq '); + --------------------- + | nextval ('movieseq ') | + --------------------- + | 155 | + --------------------- + 1 row in SET (0.00 Sec)