MYSQL auto increase column entity by 1 on update?

Question

I have a table: ID,name,count,varchar(255)

Now, what i'd like is to increase the "count" each time that row in the table is updated.

Of course, the easy way is to read first, get the value, increase by 1 in php, then update with the new value. BUT!

is there any quicker way to do it? is there a system in mysql that can do the ++ automatically? like autoincrement, but for a single entity on itself?

Answer 1

I see two options:

1.

Just add this logic to every update query

UPDATE `table` SET
   `data` = 'new_data',
   `update_counter` = `update_counter` + 1
WHERE `id` = 123

2.

Create a trigger that will do the work automatically:

CREATE TRIGGER trigger_name
AFTER UPDATE
ON `table`
FOR EACH ROW
    BEGIN
        UPDATE `table`
        SET `update_counter` = `update_counter` + 1
        WHERE `id` = NEW.id
    END

Answer 2

Create a trigger: http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html

Triggers are pieces of code that are "triggered" by the database on certain events. In your case, the event would be an update. Many RDBMS support triggers, so does MySQL. The advantage of using a trigger is that every piece of your PHP logic that updates this entity, will implicitly invoke the trigger logic, you don't have to remember that anymore, when you want to update your entity from a different piece of PHP logic.