MySQL:基于列值的自动增量? [英] MySQL: Auto Increment based on Column Value?

问题描述

是否有一种方法可以根据插入行中另一列的值自动递增MySQL表中的条目?例如，有人说这是特定用户名的第五个条目?

Is there a way to auto increment entries to a MySQL table based on the value of another column in the inserted row? For example, something to say that this is the fifth entry for a specific username?

我知道查询后可以进行行计数，但这不是我想要的.我环顾四周，似乎似乎找不到任何东西.

I know I could do a row count after I make my query, but that's not what I'm looking for. I've looked around and just can't seem to find anything on this.

谢谢！

推荐答案

假设您有一个包含两列username和counter的表Users.您提到的案例的示例代码.运行此命令以创建触发器.它应该在每次插入时触发.

Assuming that you have a table Users with 2 columns, username and counter. Example code for the case you mentioned. Run this to create the trigger. It should fire on every insert.

CREATE TRIGGER insert_update BEFORE INSERT ON Users
FOR EACH ROW
BEGIN
   DECLARE name_count INTEGER;
   SELECT COUNT(*) INTO @name_count FROM Users WHERE username = NEW.username;
   INSERT INTO Users VALUES (NEW.username, @name_count+1);
   END
END

免责声明:这可能不是世界上最有效或最干净的东西.我会对其他人的想法感兴趣.

Disclaimer: this might not be the most efficient or cleanest thing in the world. I would be interested in what the other folks can come up with.

这篇关于MySQL:基于列值的自动增量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！