MYSQL Stored Function to generate 'Sequences'
Quite often you don't know what you've got until it is gone. But that doesn't necessarily have to be the case when you find you need a sequence but are working with the MySql database. Below is one solution that I used - briefly:
- Create a table to store your sequence or sequences
- Insert some 'sequences'
- Create a function to access your sequences
- Use your newly create 'Sequence'!
delimiter $$
CREATE TABLE `SEQUENCE` (
`SEQ_NAME` varchar(50) NOT NULL,
`SEQ_COUNT` decimal(38,0) DEFAULT NULL,
PRIMARY KEY (`SEQ_NAME`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1$$
If you require more than one sequence then insert futher records into the sequence table like below:
INSERT INTO SEQUENCE VALUES('PKSEQ_GEN',100);
INSERT INTO SEQUENCE VALUES('SECOND_SEQ',100);
You can either pass the sequence name as part of the function ::
- or create a separate function for each - similar what I did below:
create function seq(seq_name char (20)) returns int
delimiter $$
CREATE FUNCTION PKSEQ_NEXTVAL() RETURNS int(11)
begin
update SEQUENCE set SEQ_COUNT=last_insert_id(SEQ_COUNT+1) where SEQ_NAME='PKSEQ_GEN';
return last_insert_id();
end$$
Then use your sequence in selects, inserts....
select PKSEQ_NEXTVAL();
INSERT INTO MYTABLE
SELECT
PKSEQ_NEXTVAL() AS MYTABLE_ID,
...