基于外键的MySQL第二个自动增量字段

Question

我已经多次遇到此问题，但是还没有找到解决这种问题的“ MySQL方式”-我有一个包含 用户 和 报告 的数据库。每个 报告 都有一个
ID ，我将其作为 报告编号 显示给我的用户。

主要的抱怨是，用户对于为什么系统中缺少报告感到困惑。实际上并非如此。实际上，他们正在识别其ID之间的间隙，并假定这些ID缺少报告，而实际上，这仅仅是因为其他用户已填补了此自动递增的间隙。

我需要知道在MySQL中是否有办法做到这一点：

我是否可以有一个 第二个 自动递增字段，称为 report_number ，它 基于一个user_id
字段，该字段具有每个用户不同的一组自动递增值？

例如

|------|---------|---------------|
|  id  | user_id | report_number |
|------|---------|---------------|
|  1   |    1    |       1       |
|  2   |    1    |       2       |
|  3   |    1    |       3       |
|  4   |    2    |       1       |
|  5   |    1    |       4       |
|  6   |    1    |       5       |
|  7   |    2    |       2       |
|  8   |    3    |       1       |
|  9   |    3    |       2       |
|------|---------|---------------|

我为此使用 InnoDB ，因为它非常重视外键。当我添加第二个自动递增字段时，似乎出现了抱怨，但是我不确定是否有其他方法可以执行此操作？

Answer 1

MyISAM支持自动递增的第二列，但 InnoDB 不支持。

对于InnoDB，您可以创建一个触发器BEFORE INSERT以获取reportid的最大值并将该值添加一个。

DELIMITER $$
CREATE TRIGGER report_trigger
BEFORE INSERT ON reports
FOR EACH ROW BEGIN
    SET NEW.`report_id` = (SELECT MAX(report_id) + 1 FROM reports WHERE user_id = NEW.user_id);
END $$
DELIMITER ;

如果可以改用MyISAM，则在MySQL页面的文档中有一个示例：

http://dev.mysql.com/doc/refman/5.0/en/example-auto-
increment.html

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

哪个返回：

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+