CODEWITHSUNDEEP
mysqlsql
Gianluca Ghettini
Gianluca Ghettini

Reputation: 11628

MySQL auto increment on insert and update

I have a MySQL database and I need to auto increment a column by 1 every time I do an insert or update. If I had to increment the column only during insert I could have used the built-in autoincrement option (usually used for primary keys). How can I do it for insert and updates?

EDIT

Sorry, I posted the wrong question, what I actually need is to increase a counter by 1 every time I do an insert or update, the current value of the counter has to be stored in the row being created or updated. The counter starts from 1 and never comes back, it just keep increasing "forever" (BIGINT). Think of this counter as a lastupdate timestamp but instead of using real unix timestamps I use an ever increasing integer (monotonic increasing value).

P.S. I'm implementing a syncronization mechanism between many local SQLite databases and one master MySQL database so the behavior has to be implemented on both dbms.

The current state of the counter can be stored on a separate table of course

Upvotes: 1

Views: 4742

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562230

There's no declarative auto-increment-on-update feature. And the auto-increment must be part of your primary key, so this is probably not your counter.

You can do this with triggers.

CREATE TRIGGER MyTrigger BEFORE INSERT ON MyTable
FOR EACH ROW
  SET NEW.counter = 1;

CREATE TRIGGER MyTrigger BEFORE UPDATE ON MyTable
FOR EACH ROW
  SET NEW.counter = OLD.counter+1;

These must be BEFORE triggers, because you can't set column values in an AFTER trigger.

Re your comments:

I don't get the "for each row on the second statement"

This is a required clause for all MySQL triggers, because the trigger runs for each row inserted. You can insert multiple rows in a single INSERT statement:

INSERT INTO MyTable VALUES (...), (...), (...), ...

INSERT INTO MyTable SELECT ... FROM ...

The insert trigger will initialize each row inserted.

Re your updated question:

The solution with triggers I show above will actually work for the scenario you describe, where you want a counter column to start at 1 at INSERT time, and increase by 1 every time you update.

The solution with INSERT...ON DUPLICATE KEY UPDATE does not work, because it won't increment the counter if a user simply does an UPDATE statement. Also the user is required to include the initial counter value 1 in their INSERT statement.

The insert trigger sets the initial value to 1 even if a user tries to give a different value in their INSERT statement. And the update trigger will increment the counter even if the user uses INSERT...ON DUPLICATE KEY UPDATE or UPDATE.

But don't use a REPLACE statement, because this would do a DELETE followed by a new INSERT, and thus it would run the insert trigger, and reset the counter to 1.

Upvotes: 1

Pritam Banerjee
Pritam Banerjee

Reputation: 18923

Simply use triggers.

Something like this:

CREATE TRIGGER trgIU_triggertestTable_UpdateColumnCountWhenColumnB
   ON  dbo.triggertestTable
   AFTER INSERT,UPDATE
AS 
BEGIN ...

OR you can do something like this:

INSERT INTO TableA (firstName, lastName, logins) VALUES ('SomeName', 'SomeLastName', 1)
ON DUPLICATE KEY UPDATE count = count + 1;

Upvotes: 3

Víctor López
Víctor López

Reputation: 817

I see two ways to do what you want.

The first is for inserts, when you should use the autoincrement key. But, when we talk about autoincrement updates, it's a little bit more complicated. For me, the best solution is to do a trigger.

You could use a trigger like this:

CREATE TRIGGER update_trigger
AFTER UPDATE
ON `your_table`
FOR EACH ROW
    BEGIN
        UPDATE `your_table`
        SET `the_field_you_want_autoincrement` = `the_field_you_want_autoincrement` + 1
        WHERE `pk` = NEW.pk
    END

Upvotes: 1

Related Questions