Using Multiple Sequences in a Table

Recently, I wanted to use multiple sequences in a table.  I wanted to generate a new sequence for every event for the id of its registrants. The table structure was something like this :

CREATE TABLE registrants ( event_id INT NOT NULL, id INT AUTO_INCREMENT NOT NULL, name VARCHAR(100), email VARCHAR(100), address VARCHAR(100), PRIMARY KEY ( event_id, id )

) ;

But I ran into a problem. This works only for MyISAM and BDB tables. You can use AUTO_INCREMENT on a secondary column in a multiple-column index only for MyISAM and BDB tables. BDB isn’t supported any longer from MySQL 5.1 and I didn’t want to use MyISAM as my whole schema is based on InnoDB engine and also for MyISAM having its own table-corruption issues.  After lot of trials, debates and finally referring Planet MySQL, I went ahead with this suggestion by Peter from MySQL Performance Blog and some help from here.

CREATE TABLE sequences ( event_id INT(10)  NOT NULL, val int(10)  NOT NULL, PRIMARY KEY  (event_id)

) ENGINE=MyISAM DEFAULT CHARACTER SET = utf8  COLLATE = utf8_general_ci;

delimiter $$

CREATE FUNCTION generate_registrant_id(event_id INT) RETURNS INT

BEGIN

INSERT INTO sequences VALUES (event_id, LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE val=LAST_INSERT_ID(val+1);

RETURN IF(LAST_INSERT_ID()=0, 1, LAST_INSERT_ID());

END$$

delimiter ;

I removed AUTO_INCREMENT from the table and call this function to generate the id. The same could be used for maintaining any other sequences in your schema.

Community to the rescue…  again.

For more help refer : Using AUTO_INCREMENT

[Permalink](the_permalink(); ?>)