<< android:layout_alignBaseline vs android:layout_alignBottom | Home | Common Android Baseband Version Numbers Explained >> | JADe Central | Contact Us

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 ::

create function seq(seq_name char (20)) returns int
- or create a separate function for each - similar what I did below:


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,
  ...

Tags : ,

Export this post as PDF document  Export this post to PDF document




Add a comment Send a TrackBack