CODEWITHSUNDEEP
mysqlincrement
freddy
freddy

Reputation: 157

MySQL auto increment refresh with new ID

I have a table, in MySQL where events are posted by users. Their individual ID is posted, so all the events they post can be related to their account. However, I want to put an auto increment field, that only count the events for 1 user at the time.

Is it possible? If yes, how can I achieve it?

For example, if the user with the ID 544 post 4 events, the 4th one will be marked as 4 in the auto increment column, even if it's the 421 events in the table...

Upvotes: 0

Views: 621

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

You could put in such a field, but you would need a trigger to keep it up-to-date. Then, if rows are updated or deleted, maintaining becomes challenging.

You can simulate this in a query pretty easily though, using variables:

select e.*,
       @rn := if(@userid = userid, 1, @rn + 1) as UserSequenceNumber,
       @userid := userid
from events e cross join
     (select @rn := 0) const
order by userid;

You can also incorporate this into an update statement, if you want to actually store the values in the table. Once again, let me say, keeping the value up to date requires a bunch of triggers.

Also, the above works in practice. However, MySQL does not guarantee that arguments are processed in order. So, in theory, the @userid could be assigned before @rn, but this doesn't seem to happen in practice.

Upvotes: 1

Related Questions