About designing a function that increments a field in mysql.

Question

I have a simple table with three fields, tag_id, user_id, user_tag_id. I want when I insert a record (insert known tag_id and user_id), user_tag_id must be under the condition that user_id = currently inserted user_id self-increment by 1, how to implement this, do I need to write a custom function of mysql?

Answer 1

DELIMITER $$
DROP TRIGGER IF EXISTS after_insert_table $$
CREATE TRIGGER after_insert_table
AFTER INSERT on table
FOR EACH ROW
BEGIN
  update table t set t.user_tag_id = t.user_tag_id+1 where t.user_id=new.user_id ;
END $$
DELIMITER ;

Answer 2

If only the user_tag_id in the newly inserted record is increased by 1, it can be divided into two sql statements:

First calculate the maximum value of user_id corresponding to user_tag_id

select max(user_tag_id) + 1 from t1 where user_id = :user_id

Then put the return value of the previous statement into the insert statement.