Mysql count changes on a row

Question

I have a query where I INSERT on unique but UPDATE on duplicate.

Now I'd like to count the times a row get changed/updated. Is this possible to do in the same query?

$query = "INSERT INTO table(
                      column1,
                      column2)
                VALUES(
                      value1,
                      value2)
                ON DUPLICATE KEY UPDATE
                      column1 = VALUES(column1),
                      column2 = VALUES(column2),
                      column3 = column3+1";  //SOMETHING LIKE THIS?

UPDATE Actually this seems to work as is!

@Strawberry ok. Thanks. Do you have any thoughts on how you have solved it? — Björn C, Oct 08 '15 at 14:09
I may be confused but why doesn't it work with `IODKU` with merely incrementing `column3` ? And col3 on insert is just say a 1 ? You need a unique key to generate the clash such that `IODKU` will even work — Drew, Oct 08 '15 at 14:17
Maybe take a look at triggers... http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html — Strawberry, Oct 08 '15 at 14:19
to visualize it with `IODKU` with see [this](http://stackoverflow.com/a/32468519) example i wrote up. I would be happy to write yours up in its entirety, but a schema of the table is required to see if it is even possible — Drew, Oct 08 '15 at 14:26
@Drew Thank you. Your example shows me this code actually works. Please combind with your answer and i will accept it! — Björn C, Oct 09 '15 at 07:36

score 0 · Answer 1 · answered Oct 08 '15 at 14:18

0

My tought would be to create a new table logs where you would add a row each time you insert/update with the query. To see the result just use a COUNT function on the logs table.

answered Oct 08 '15 at 14:18

SdR

67
2
8

I´m sorry this is not possible, in my case! – Björn C Oct 09 '15 at 07:26

score 0 · Accepted Answer · edited May 23 '17 at 11:48

Ok, as requested,

If your schema has a unique key of any kind (such as a Primary Key, or any other unique key, composite or not), then Insert On Duplicate Key Update a.k.a. IODKU will work, as I see it.

A simple case to test against would be to insert a 1 into column3 or in the case of the Update firing, incrementing column3.

An example of IODKU can be seen here that closely follows what I think you are doing, yet it has a unique composite key for that gent's question.

Please note, without a unique key that would create a unique key clash, IODKU will not work. It is such a clash that causes the Update part of IODKU to succeed as desired. Meaning, without such a unique key, you merely get another row added.

I say this because many people try it without such a key in place in the schema, then wonder why another row appears against their wishes.

Mysql manual page on Insert on Duplicate Key Update.

Mysql count changes on a row

2 Answers2