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

Question

我有一个表，它有一个复合主键，由一个非auto_increment列和一个auto_increment列组成。auto_increment列需要为每个非auto_increment列值单独递增（稍后将详细介绍）。存储引擎是InnoDB。由于性能问题，我不希望锁定表。插入值后，检索最后一个auto_increment值的方法必须可用。

下面的脚本开始工作，但是最后一个INSERT会产生id，checkingaccounts_id是3,2，但是需要1,2。这就是我所说的auto_increment列需要为每个非auto_increment列值单独递增

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

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>

Answer 1

创建MyISAM表以仅创建auto_increment id，并使用触发器将此id用于目标表。如果有多个InnoDB表需要复合自动递增主键，请向MyISAM表添加额外的主键。

弊：

1. 但是，MyISAM表不允许外键约束，希望触发器可以消除风险
2. 需要一个附加表

优势

1. 如果删除主键阀，则不会重复使用。
2. 客户端仅使用普通的 SQL 查询，而不是存储过程。
3. 可能比存储过程维护更少，因为向表中添加列不需要修改触发器。

-- MySQL Script generated by MySQL Workbench
-- 07/08/16 05:12:11
-- Model: New Model    Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`a`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`a` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`t1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t1` (
  `a_id` INT NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`, `a_id`),
  CONSTRAINT `fk_t1_a1`
    FOREIGN KEY (`a_id`)
    REFERENCES `mydb`.`a` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`t2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t2` (
  `a_id` INT NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`, `a_id`),
  CONSTRAINT `fk_t2_a1`
    FOREIGN KEY (`a_id`)
    REFERENCES `mydb`.`a` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`t3`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t3` (
  `a_id` INT NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`, `a_id`),
  CONSTRAINT `fk_t3_a1`
    FOREIGN KEY (`a_id`)
    REFERENCES `mydb`.`a` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`inc`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`inc` (
  `a_id` INT NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  `type` CHAR(4) NOT NULL,
  PRIMARY KEY (`a_id`, `type`, `id`))
ENGINE = MyISAM;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
USE `mydb`;

DELIMITER $$
USE `mydb`$$
CREATE TRIGGER `t1_BINS` BEFORE INSERT ON `t1` FOR EACH ROW
BEGIN 
INSERT INTO inc(a_id,type) VALUES(NEW.a_id,'t1');
SET NEW.id=LAST_INSERT_ID();
END$$

USE `mydb`$$
CREATE TRIGGER `t2_BINS` BEFORE INSERT ON `t2` FOR EACH ROW
BEGIN 
INSERT INTO inc(a_id,type) VALUES(NEW.a_id,'t2');
SET NEW.id=LAST_INSERT_ID();
END$$

USE `mydb`$$
CREATE TRIGGER `t3_BINS` BEFORE INSERT ON `t3` FOR EACH ROW
BEGIN 
INSERT INTO inc(a_id,type) VALUES(NEW.a_id,'t3');
SET NEW.id=LAST_INSERT_ID();
END$$


DELIMITER ;

测试

mysql> insert into a(id) VALUES(null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(a_id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+----+
| a_id | id |
+------+----+
|    1 |  1 |
+------+----+
1 row in set (0.00 sec)

mysql> insert into t1(a_id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(a_id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+----+
| a_id | id |
+------+----+
|    1 |  1 |
|    1 |  2 |
|    1 |  3 |
+------+----+
3 rows in set (0.00 sec)

mysql> insert into t2(a_id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t2;
+------+----+
| a_id | id |
+------+----+
|    1 |  1 |
+------+----+
1 row in set (0.00 sec)

mysql> insert into a(id) VALUES(null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into a(id) VALUES(null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(a_id) VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(a_id) VALUES(3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(a_id) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+----+
| a_id | id |
+------+----+
|    1 |  1 |
|    1 |  2 |
|    1 |  3 |
|    1 |  4 |
|    2 |  1 |
|    3 |  1 |
+------+----+
6 rows in set (0.00 sec)

mysql>

Answer 2

要删除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

和

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