Mysql 使用一个特定的主键自动增加一列

2022-01-15 00:00:00 sql mariadb mysql

假设我有一个具有以下方案的表:(注意邮政编号栏)

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          |

这是第二种方式的demo.

相关文章