3

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?

3

My solution is in migrating from myisam to innodb. It goes something like:

  BEGIN;
  SELECT @id := IFNULL(MAX(id),0) + 1 FROM foo WHERE other = 123 FOR UPDATE;
  INSERT INTO foo
     (other, id, ...)
     VALUES
     (123, @id, ...);
  COMMIT;

Having a transaction is mandatory to prevent another thread from grabbing the same id.

-1

at least MySQL 5.6 - support auto increment column as part of composite index

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `rid` int(11) DEFAULT NULL,
  `int1` int(11) DEFAULT NULL,
  `test` varchar(255) NOT NULL,
  PRIMARY KEY (`id`,`uid`,`test`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

perfect work, so may be simples way - just upgrade MySQL

  • You are correct in that this is possible. But the result is not the same as with MyISAM. In your example id will be unique over the whole table. I want to keep the MyISAM-behaviour, which starts at 1 for every combination of uid and test. Otherwise I would have made id the atomic primary key and only put a usual unique index on the composite key. – bodo Nov 17 '15 at 14:47
  • Thanks, it other task. Just afraid (sure) - using trigger with select MAX() from same table will seriously slowdown work on big table and multi user environment. – a_vlad Nov 17 '15 at 15:11
  • I think he means add a hidden key, so you have 3 keys. id, keyA, keyB. Then you can use last_insert_id() as an index into the row that you need the KeyPartB of. – malhal Feb 11 '18 at 21:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.