如何使用 MySQL InnoDB 实现 auto_increment 复合主键? [英] How to implement an auto_increment composite primary key with MySQL InnoDB?

问题描述

我有一个表，它有一个由一个非 auto_increment 列和一个 auto_increment 列组成的复合主键.auto_increment 列需要为每个非 auto_increment 列值单独增加(稍后会详细介绍).存储引擎是 InnoDB.由于性能问题，我不想锁定表.插入值后，必须有检索最后一个 auto_increment 值的方法.

I have a table which has a composite primary key made up of one non-auto_increment column and one auto_increment column. The auto_increment column needs to increment individually for each of the non-auto_increment column values (more on this later). The storage engine is InnoDB. I don't wish to lock the table because of performance concerns. After inserting a value, a means to retrieve the last auto_increment value must be available.

下面的脚本开始工作，但最后一个 INSERT 结果是 id，checkingaccounts_id 是 3, 2，但是 1, 2 是需要的.这就是我的意思 auto_increment 列需要为每个非 auto_increment 列值单独递增

The below script works at first, but the last INSERT results in id, checkingaccounts_id is 3, 2, but 1, 2 is desired. This is what I meant by The auto_increment column needs to increment individually for each of the non-auto_increment column values

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

Trigger and Stored Procedures are both acceptable, and so is a PHP/PDO application solution which somehow mimics the MySQL auto_increment behavior.

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 功能，尝试使用存储过程:

Remove the auto_increment feature, try a stored procedure instead:

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

还有

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

这篇关于如何使用 MySQL InnoDB 实现 auto_increment 复合主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！