基于外键的MySQL第二个自动增量字段 [英] MySQL second auto increment field based on foreign key
问题描述
主要的抱怨是，用户对于为什么报告从系统中失踪而感到困惑。实际上并非如此。实际上，他们正在认识到他们的身份证号码之间的差距，并假设这些缺失报告，实际上只是因为另一个用户填补了这个自动递增的差距。
我需要知道在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 ，因为它与国外相当重键。当我添加第二个自动增量字段时，似乎抱怨，但我不知道是否有不同的方法来执行此操作。
MyISAM 支持自动增量的第二列，但 InnoDB 不。
对于InnoDB，您可以创建触发器 BEFORE INSERT 以获取reportid的最大值，并将其添加到价值。
DELIMITER $$
CREATE TRIGGER report_trigger
BEFORE INSERT ON报告
为每个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动物（
grp ENUM（'fish'，'mammalian'，'bird'）NOT NULL，
id MEDIUMINT NOT NULL AUTO_INCREMENT，
name CHAR（30）NOT NULL，
PRIMARY KEY（grp，id）
）ENGINE = MyISAM;
INSERT INTO动物（grp，name）VALUES
（'哺乳动物'，'狗'），（'哺乳动物'，'猫'），
（'鸟' ''''''''''''''''''''''''''''''
SELECT * FROM animals ORDER BY grp，id;
哪些返回：
+ -------- + ---- + --------- +
| grp | id |名称|
+ -------- + ---- + --------- +
|鱼| 1 |不舒服
|哺乳动物| 1 |狗|
|哺乳动物| 2 |猫|
|哺乳动物| 3 |鲸鱼|
|鸟| 1 |企鹅|
|鸟| 2 |鸵鸟|
+ -------- + ---- + --------- +
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.
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.
I need to know if there is a way to do this in MySQL:
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?
e.g.
|------|---------|---------------|
| 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 |
|------|---------|---------------|
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 supports the second column with auto increment, but InnoDB doesn't.
For InnoDB you might create a trigger BEFORE INSERT to get the max value of the reportid and add one to the value.
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 ;
If you can use MyISAM instead, in the documentation of MySQL page there is an example:
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;
Which returns:
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
这篇关于基于外键的MySQL第二个自动增量字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
