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

问题描述

我多次遇到这个问题，但还没有找到解决这个问题的MySQL 方法"——我有一个包含用户和报告的数据库强>.每个报告都有一个id，我将其作为报告编号显示给我的用户.

I've come across this problem numerous times but haven't found a "MySQL way" to solve the issue as such - I have a database that contains users and reports. Each report has an id which I display as a report number to my users.

主要的抱怨是用户对为什么报告从他们的系统中丢失感到困惑.实际情况并非如此.实际上，他们意识到了他们的 ID 之间的差距，并假设这些是缺失的报告，而实际上，这只是因为另一个用户填补了这个自动递增的差距.

The main complaint is that users are confused as to why reports have gone missing from their system. This is not actually the case. It is actually that they are recognizing a gap between their IDs and assume that these are missing reports, when in actual fact, it is simply becasue another user has filled in this auto-incrementing gap.

我需要知道是否有办法在 MySQL 中执行此操作:

I need to know if there is a way to do this in MySQL:

我是否可以有一个名为 report_number 的 second 自动递增字段，它基于 user_id 字段，该字段具有不同的每个用户的一组自动增量?

Is it possible that I can have a second auto-increment field called report_number which is based on a user_id field which has a different set of auto-increments per user?

例如

|------|---------|---------------|
|  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，因为它的外键权重很大.当我添加第二个自动增量字段时，它似乎会抱怨，但我不确定是否有不同的方法来做到这一点?

I am using InnoDB for this as it is quite heavily weighted with foreign-keys. It appears to complain when I add a second auto increment field, but I wasn't sure if there was a different way to do this?

推荐答案

MyISAM 支持第二列自动递增，但 InnoDB 不支持强>.

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 ;

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 |
+--------+----+---------+