I have a slight problem with a table that I have converted which used to be MyISAM, I have now made it a InnoDB table.
id varchar(15), seqno int(11) auto increment Joint PK index on the above 2 fields
auto_id INT (11) auto increment, id varchar(15), seqno int(11)
The original structure had a joint PK on id and seqno with seqno being auto incremented for each unique id value. It would then return the seqno as the last_insert_id in my web application.
With the new innodb structure, because innodb doesnt support joint primary keys, I’ve had to add in an auto_id which now has the auto increment.
I then implemented a trigger to generate the seqno unique per id value. My question is how do I set the auto increment id so that it returns in place of the auto_id value? This trigger would be called when I insert into my_table, so that it can calculate the next seqno value to insert with.
DROP TRIGGER IF EXISTS `innodb_seqno_trigger`; // CREATE TRIGGER `innodb_seqno_trigger` BEFORE INSERT ON `my_table` FOR EACH ROW BEGIN SET @newseqno = 0; SELECT MAX(seqno) INTO @newseqno FROM my_table WHERE id = NEW.id; SET NEW.seqno = (@newseqno + 1); /* HERE I NEED HELP PLEASE TO GET THE AUTO INCREMENT VALUE TO ACTUALLY RETURN NEW.seqno */ END; //
I don’t think you can get the new value of
auto_id in a
BEFORE insert trigger. This will do what you want (if I have understood correctly):
CREATE TRIGGER innodb_seqno_trigger BEFORE INSERT ON my_table FOR EACH ROW BEGIN SELECT MAX(seqno) INTO @newseqno FROM my_table WHERE id = NEW.id; SET NEW.seqno = COALESCE(@newseqno + 1, 1); END;
Test at SQL-Fiddle
But have you thought what will happen if you have 2 separate connections/threads/transactions that try to insert the same
because innodb doesnt support joint primary keys
Unless a “joint primary key” describes something other than a composite key or compound key as primary key, then, of course,
InnoDB does support this.
mysql> CREATE TABLE `jpk` ( `id` varchar(15) NOT NULL, `seqno` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`,`seqno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
All right, fine… it’s not that InnoDB won’t allow it, it’s just that the
auto_increment columns has to be declared as a key (index)… so, make seqno a key, and InnoDB has no problem:
mysql> CREATE TABLE `jpk` ( `id` varchar(15) NOT NULL, `seqno` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`,`seqno`), KEY `seqno` (`seqno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.25 sec)
Or did I miss something?
I wish InnoDB could support auto_increment the same way MyISAM does. I wrote about this before
Apr 30, 2013: Issues converting MyISAM table to InnoDB (auto column issue)
Apr 21, 2012: How can you have two auto-incremental columns in one table?
This being the case, you must accept full responsibility for sequencing the two columns. For starters, you must remove the auto_increment option from all columns that involve your sequencing.
CREATE TABLE `jpk` ( `id` varchar(15) NOT NULL, `seqno` int(11) NOT NULL, ... PRIMARY KEY (`id`,`seqno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Your trigger has be just as ypercube specified. He gets +1 for that.
Based on your new structure, the
auto_id field holds the auto_increment value from where mysql would fetch the “next” if we need it.
The value you want as the auto_increment value can be got using:
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='table name';
You can directly do
SET NEW.seqno = SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='table name'
in your trigger definition.