KfWiki: NEXTVAL procedure

HomePage » Database » MySQL » MysqlProcedures

NEXTVAL procedure

Generate increments using a sequence table and a procedure to ensure there is no duplicates:

# create the sequence table

create table seqtable (seq_name varchar(100), seq_current bigint(20),primary key (seq_name));
insert into seqtable values ('ccn',1);

# create a test table

create table seqtest (ccn bigint(20));

# test by running multiple instance of the following loop

while true; do mysql -uroot test -Nse "insert into seqtest (ccn) select nextval('ccn');"; done

# check the seqtest table for duplicates

select ccn from seqtest group by ccn having count(1) > 1;
select count(1) from seqtest;

The nextval function:

drop procedure if exists nextval;
delimiter #

CREATE FUNCTION nextval (s_name varchar(100)) RETURNS bigint(20) 
CONTAINS SQL
	BEGIN
	DECLARE cur_val bigint(20) unsigned;
 
	SELECT 	seq_current INTO cur_val
	FROM 	seqtable
	WHERE 	seq_name = s_name
	;

	UPDATE seqtable SET seq_current = seq_current + 1
	WHERE seq_name = s_name;
 
 	SELECT 	seq_current INTO cur_val
	FROM 	seqtable
	WHERE 	seq_name = s_name
	;
	
	RETURN cur_val;
	
end #
delimiter ;

KfWiki : MysqlProcedures

NEXTVAL procedure