如何使用MySQL InnoDB实现自增复合主键？

我有一个表，它有一个复合主键，由一个非自动增量列和一个自动增量列组成。 auto_increment 列需要为每个非 auto_increment 列值单独递增（稍后会详细介绍）。存储引擎是InnoDB。出于性能方面的考虑，我不想锁定表。插入值后，必须有一种方法可以检索最后一个 auto_increment 值。

下面的脚本首先起作用，但最后一个 INSERT 结果是 id，checkingaccounts_id 是3, 2, but 1, 2是所期望的。这就是我的意思The auto_increment column needs to increment individually for each of the non-auto_increment column values

触发器和存储过程都是可以接受的，PHP/PDO 应用程序解决方案也是如此，它以某种方式模仿 MySQL 自动增量行为。

mysql> EXPLAIN checkingaccounts;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| data  | varchar(45) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN checks;
+---------------------+-------------+------+-----+---------+----------------+
| Field               | Type        | Null | Key | Default | Extra          |
+---------------------+-------------+------+-----+---------+----------------+
| id                  | int(11)     | NO   | PRI | NULL    | auto_increment |
| checkingaccounts_id | int(11)     | NO   | PRI | NULL    |                |
| data                | varchar(45) | YES  |     | NULL    |                |
+---------------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO checkingaccounts(id, data) VALUES(0,'bla');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO checkingaccounts(id, data) VALUES(0,'bla');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM checkingaccounts;
+----+------+
| id | data |
+----+------+
|  1 | bla  |
|  2 | bla  |
+----+------+
2 rows in set (0.00 sec)

mysql> INSERT INTO checks(id,checkingaccounts_id,data) VALUES(0,1,'bla');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO checks(id,checkingaccounts_id,data) VALUES(0,1,'bla');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM checks;
+----+---------------------+------+
| id | checkingaccounts_id | data |
+----+---------------------+------+
|  1 |                   1 | bla  |
|  2 |                   1 | bla  |
+----+---------------------+------+
2 rows in set (0.00 sec)

mysql> INSERT INTO checks(id,checkingaccounts_id,data) VALUES(0,2,'bla');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM checks;
+----+---------------------+------+
| id | checkingaccounts_id | data |
+----+---------------------+------+
|  1 |                   1 | bla  |
|  2 |                   1 | bla  |
|  3 |                   2 | bla  |
+----+---------------------+------+
3 rows in set (0.00 sec)

mysql>

删除 auto_increment 功能，尝试使用存储过程：

CREATE PROCEDURE insertChecks
     (IN AccID int(9), IN data varchar(50))
BEGIN
    DECLARE cid INT DEFAULT 1;

    SELECT (COUNT(*) + 1) INTO cid 
    FROM checks 
    WHERE checkingaccounts_id = AccID;

    INSERT INTO checks(id, checkingaccounts_id, data) 
    VALUES(cid, AccID, data);
END

And

call insertChecks(1,'bla');
call insertChecks(1,'bla');
call insertChecks(2,'bla');

解决方案2：

CREATE PROCEDURE insertChecks 
    (IN AccID int(9), IN data varchar(50))
BEGIN
    INSERT INTO checks(id, checkingaccounts_id, data) 
        SELECT (COUNT(*) + 1), AccID, data 
        FROM checks 
        WHERE checkingaccounts_id = AccID;
END