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 tab开发者_如何学Gole 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?

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

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.

you can look up at the trigger

or can do with the extra mysql query

update table set count=count+1 ;

UPDATE table SET name='new value', count=count+1 WHERE id=...

An SQL update can use fields in the record being updated as a source of data for the update itself.