MySQL-Auto increment

Question

I have a table t1 like this

no    name    number
1      A        25
1      C        25
2      DF       36
3      JS       79
3      H        79
3      KO       79

Initially the table is blank. I will import data into columns name and number. I want to set column no to start at 1 and automatically increase by 1 everytime the value in number change.

Really appreciate if someone could help me with this

Answer 1

You can use trigger on your table:

CREATE TRIGGER gen BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
     IF NEW.number <> OLD.number THEN
             SET NEW.no = OLD.no + 1;
     END IF;
END;