Implement nextval functions in MySQL similar to those in Oracle

We know that sequence is not supported in MySQL. Generally, this field is auto-incrementing by the time the table is created.

For example, create table table_name (ID int auto_increment primary key ,...);

Or alter table table_ame add ID int auto_increment primary key // The field must be set to primary key.

Or reset the starting value of the auto-increment field. alter table table_name auto_increment = N

However, sequence_name.nextval is often used in Oracle, orProgramSelect sequence_name.value from dual. If our development framework must support both Oracle and MySQL. We generally propose sequence. If a similar function is provided in MySQL, it is more convenient to propose it. This is an application scenario. The following describes how to implement a nextval function in MySQL.

1. Create a table first

 1   Create   Table 'Sys _ Sequence '(  2 'Name' Varchar ( 50 ) Not   Null  ,  3 'Current _ value' Int ( 11 ) Not   Null   Default   '  0 '  ,  4 'Credentials' Int ( 11 ) Not   Null   Default   '  1  '  ,  5      Primary   Key  ('Name ')  6 )

 

2. Create a function

 delimiter $ drop function if exists 'currval' $ create definer = 'root' @ '%' function 'currval' (seq_name varchar (50) returns int (11) begin declare value integer; set value = 0; select current_value into value from sys_sequence where name = seq_name; return value; end $ delimiter; delimiter $ 
 drop function if exists 'nextval' $ create functioncreate definer = 'root' @ '% 'function' nextval' (seq_name varchar (50 )) returns int (11) Begin update sys_sequence set current_value = current_value + increment where name = seq_name; return currval (seq_name); End 
 delimiter; 
 delimiter $ 
 drop function if exists 'setval' $ 
 Create definer = 'root' @ '% 'function' setval '(seq_name varchar (50 ), value integer) returns int (11) Begin update sys_sequence set current_value = value where name = seq_name; return currval (seq_name); End 
 delimiter; 

Test select nextval ('name. Remove the <br> line feed in it, modify the style, and display it.

 

MySQL documentation see http://dev.mysql.com/doc/refman/5.1/zh/index.html