In contrast to the MySQL MyISAM-Engine, the InnoDB-Engine can not handle when one column of a composite primary key is declared as AUTO_INCREMENT
. For example consider the following table:
CREATE TABLE `ExampleTable` (
`KeyPartA` mediumint(8) unsigned NOT NULL,
`KeyPartB` smallint(5) unsigned NOT NULL,
`Data` text NOT NULL,
PRIMARY KEY (`KeyPartA`,`KeyPartB`)
) ENGINE=InnoDB;
Now to simulate AUTO_INCREMENT
, I learned, it is possible to create the following trigger
DELIMITER $$
CREATE TRIGGER ExampleAutoIncrement BEFORE INSERT ON ExampleTable
FOR EACH ROW BEGIN
SET NEW.KeyPartB = (
SELECT IFNULL(MAX(KeyPartB), 0) + 1
FROM ExampleTable
WHERE KeyPartA = NEW.KeyPartA
);
END $$
DELIMITER ;
Now inserts work correctly, generating auto-incremented values. Unfortunately I can not think of a sensible way to get the newly created key value.
Neither LAST_INSERT_ID()
nor its C counterpart return it, as there is no actual AUTO_INCREMENT
column. Also it is not possible to set the value with LAST_INSERT_ID(IFNULL(MAX(KeyPartB), 0) + 1)
inside the ExampleAutoIncrement
trigger, because MySQL resets the value after the trigger finishes.
The only way I thought of was to issue a SELECT MAX(KeyPartB) FROM ExampleTable WHERE KeyPartA = ?
after an insert, which defeats the whole purpose of the trigger. I.e. I could just as well do this before the insert and increment manually.
Is there any way to get the incremented key value from the trigger or some other means of simulating the AUTO_INCREMENT
I want?
Best Answer
My solution is in migrating from myisam to innodb. It goes something like:
Having a transaction is mandatory to prevent another thread from grabbing the same id.