MySQL自动增加具有一个特定主键的列 [英] Mysql auto-increment a column with one specific primary key

问题描述

假设我有一个具有以下方案的表: (请注意帖子编号"列)

Let's say I have a table with following scheme: (Note the Post Number column)

-- UserID  -- Post Id  -- Post Number*

-- 4       -- 85       -- 1
-- 4       -- 86       -- 2
....
-- 5       -- 9362     -- 1
-- 4       -- 9363     -- 3

是否有一种方法可以独立于所有其他条目自动递增列，并且仅尊重具有相同主键的那些列?如果是这样，有人将如何实现呢?

Is there a way to auto_increment a column independently from all other entries and only respect those with the same primary key? If so, how would someone implement this?

非常感谢.

推荐答案

为了实现所需的功能，必须使用

In order to achieve what you're looking for, you have to use triggers. There's no other direct way to accomplish this task (I guess).

我确实尝试了快速演示:

I did try a fast demo now:

Create Table SoQuestion (
  UserId int,
  PostId int,
  PostNumber int null
 );

 CREATE TRIGGER inc_post_num 
 BEFORE INSERT ON SoQuestion
 FOR EACH ROW
 set New.PostNumber = (select num 
                       From (select count(*) as num 
                             from SoQuestion 
                             where UserId = New.UserId) as b) 
                     + 1;


insert into SoQuestion (UserId, PostId) Values (1,1);
insert into SoQuestion (UserId, PostId) Values (1,10);
insert into SoQuestion (UserId, PostId) Values (1,20);
insert into SoQuestion (UserId, PostId) Values (2,1);
insert into SoQuestion (UserId, PostId) Values (2,10);
insert into SoQuestion (UserId, PostId) Values (3,1);
insert into SoQuestion (UserId, PostId) Values (4,1);

select * FROM SoQuestion;

这是我得到的输出:

UserId | PostId | PostNumber |
==============================  
1      | 1      | 1          |
1      | 10     | 2          |
1      | 20     | 3          |
2      | 1      | 1          |
2      | 10     | 2          |
3      | 1      | 1          |
4      | 1      | 1          |

这是演示.

经过 Auto_Increment 后文档中，我找到了无需使用触发器即可实现此目标的另一种方法.这个想法是关于创建一个Auto_Increment列，并将其与另一个列添加为PRIMARY KEY.在我们的情况下，它是UserId，而AUTO_INCREMENT是PostNumber，它们都构成主键.方法如下:

After going through the Auto_Increment documentation, I found another way to achieve this without using triggers. The idea is about creating an Auto_Increment column and add it with another column as PRIMARY KEY. In our case it would be UserId and the AUTO_INCREMENT would be PostNumber and they both form the primary key. This is how:

Create Table SoQuestion (
  UserId int,
  PostId int,
  PostNumber int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (UserId, PostNumber)
 );

insert into SoQuestion (UserId, PostId) Values (1,1);
insert into SoQuestion (UserId, PostId) Values (1,10);
insert into SoQuestion (UserId, PostId) Values (1,20);
insert into SoQuestion (UserId, PostId) Values (2,1);
insert into SoQuestion (UserId, PostId) Values (2,10);
insert into SoQuestion (UserId, PostId) Values (3,1);
insert into SoQuestion (UserId, PostId) Values (4,1);

select * FROM SoQuestion;

这将为我们提供与第一种方法相同的输出:

This would give us the same output that the first way gave:

UserId | PostId | PostNumber |
==============================  
1      | 1      | 1          |
1      | 10     | 2          |
1      | 20     | 3          |
2      | 1      | 1          |
2      | 10     | 2          |
3      | 1      | 1          |
4      | 1      | 1          |

这是第二种方法演示.

这篇关于MySQL自动增加具有一个特定主键的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！