3

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.

Thanks!

user3011922
  • 183
  • 3
  • 8
  • 1
    If you did something like that then it wouldn't be auto incrementing - in the mySQL sense anyway. – Steve Nov 27 '13 at 05:45
  • 1
    You can create an insertion trigger for the table and add the logic there. But as steve mentioned this won't be autoincrementing. – Pepe Nov 27 '13 at 05:46
  • @Steve Alright, but regardless of semantics can this be done? – user3011922 Nov 27 '13 at 05:46
  • @Pepe Okay, so I should research triggers then? – user3011922 Nov 27 '13 at 05:48
  • @user3011922 Yup that would be your best bet. – Pepe Nov 27 '13 at 05:49
  • *"something to say that this is the fifth entry for a specific username"* - It's what we call a "counter". It has nothing to do with SQL's `AUTO_INCREMENT` - You just need to log a specific user and increment +1 – Funk Forty Niner Nov 27 '13 at 05:50
  • @Fred-ii- And you agree the best way to go about that is with triggers? – user3011922 Nov 27 '13 at 05:51
  • @Pepe Any good resources you can recommend on that subject? – user3011922 Nov 27 '13 at 05:51
  • You could do it with a trigger but that may stand to be a lot of work, since you may have to set one up for each user. One way to do this, is assign the user to a session name and increase a column based on that. That's how I would do it. If others have any other suggestions, then wait and see what they'll come up with. – Funk Forty Niner Nov 27 '13 at 05:53

1 Answers1

2

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.

Pepe
  • 6,360
  • 5
  • 27
  • 29
  • Thank you very much. I am currently doing research on triggers so I can understand what's going on here and really appreciate your response. – user3011922 Nov 27 '13 at 06:10
  • @user3011922 Good luck! Frankly I haven't really tested the above example but hopefully it should work. I'm heading to sleep and if you have any questions or if this doesn't work shoot me a message and I'll reply in the morning. – Pepe Nov 27 '13 at 06:11
  • this doesn't look thread safe. – Steven Li Nov 27 '22 at 06:20