InnoDB 中復合鍵上的 AUTO_INCREMENT 技巧

與 MySQL MyISAM-Engine 相比，InnoDB-Engine 無法處理複合主鍵的一列被聲明為AUTO_INCREMENT. 例如考慮下表：

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;

現在來模擬AUTO_INCREMENT，我了解到，可以創建以下觸發器

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 ;

現在插入工作正常，生成自動遞增的值。不幸的是，我想不出一種明智的方法來獲取新創建的鍵值。

LAST_INSERT_ID()由於沒有實際的AUTO_INCREMENT列，因此它的 C 對應項都不會返回它。也不能LAST_INSERT_ID(IFNULL(MAX(KeyPartB), 0) + 1)在觸發器內部設置值ExampleAutoIncrement，因為 MySQL 在觸發器完成後重置值。

我想到的唯一方法是SELECT MAX(KeyPartB) FROM ExampleTable WHERE KeyPartA = ?在插入之後發出 a ，這違背了觸發器的全部目的。即我也可以在手動插入和增量之前執行此操作。

AUTO_INCREMENT有什麼方法可以從觸發器或其他一些模擬我想要的方法中獲取遞增的鍵值？

我的解決方案是從 myisam 遷移到 innodb。它類似於：

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

必須進行事務以防止另一個執行緒獲取相同的 id。

至少 MySQL 5.6 - 支持自動遞增列作為複合索引的一部分

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;

完美的工作，所以可能是簡單的方法 - 只需升級 MySQL

引用自：https://dba.stackexchange.com/questions/121345