Down the dirty road
Ok. So it all begins with somebody who is using INSERT ON DUPLICATE KEY UPDATE. That guy wants to count the number of UPDATE actions that statement has taken, as opposed to INSERT actions.
We could have been using mysql_info() to fetch that information. But instead we rig the UPDATE clause:
We could have been using mysql_info() to fetch that information. But instead we rig the UPDATE clause:
CODE:
root@localhost [kris]> create table t (
id integer unsigned not null primary key,
d integer unsigned not null
) engine = innodb;
Query OK, 0 rows affected (0.16 sec)
root@localhost [kris]> insert into t values ( 1, 1), (2,2), (3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@localhost [kris]> set @x = 0;
Query OK, 0 rows affected (0.00 sec)
root@localhost [kris]> insert into t values (4,4), (2,1), (3, 1)
-> on duplicate key update
-> d= values (d) + 0\* ( @x := @x +1 );
Query OK, 5 rows affected (0.00 sec)
Records: 3 Duplicates: 2 Warnings: 0
root@localhost [kris]> select @x;
+------+
| @x |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
Wonderful side effects! And this is only the beginning.
id integer unsigned not null primary key,
d integer unsigned not null
) engine = innodb;
Query OK, 0 rows affected (0.16 sec)
root@localhost [kris]> insert into t values ( 1, 1), (2,2), (3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@localhost [kris]> set @x = 0;
Query OK, 0 rows affected (0.00 sec)
root@localhost [kris]> insert into t values (4,4), (2,1), (3, 1)
-> on duplicate key update
-> d= values (d) + 0\* ( @x := @x +1 );
Query OK, 5 rows affected (0.00 sec)
Records: 3 Duplicates: 2 Warnings: 0
root@localhost [kris]> select @x;
+------+
| @x |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
We can employ the same technique to collect the primary keys of the rows we have been changing.
But, well, if you do it, you might go all the way down the dirty road and get rid of the need for triggers as well. Here we are collecting the old values of the row as well:
CODE:
root@localhost [kris]> set @id = "";
Query OK, 0 rows affected (0.00 sec)
root@localhost [kris]> set @x = 0;
Query OK, 0 rows affected (0.00 sec)
root@localhost [kris]> insert into t
-> values (4,4), (2,1), (3, 1)
-> on duplicate key update
-> d= values (d) + 0\* ( @x := @x +1 ),
-> id = concat(values(id),
-> substring(@id := concat(@id, ",", values(id)) , 1, 0));
Query OK, 5 rows affected (0.00 sec)
Records: 3 Duplicates: 2 Warnings: 0
The result: Query OK, 0 rows affected (0.00 sec)
root@localhost [kris]> set @x = 0;
Query OK, 0 rows affected (0.00 sec)
root@localhost [kris]> insert into t
-> values (4,4), (2,1), (3, 1)
-> on duplicate key update
-> d= values (d) + 0\* ( @x := @x +1 ),
-> id = concat(values(id),
-> substring(@id := concat(@id, ",", values(id)) , 1, 0));
Query OK, 5 rows affected (0.00 sec)
Records: 3 Duplicates: 2 Warnings: 0
CODE:
root@localhost [kris]> select @x, @id;
+------+------+
| @x | @id |
+------+------+
| 2 | ,2,3 |
+------+------+
1 row in set (0.00 sec)
Now some you may interject: "I did not know you could do that." To which I respond: Neither did I. And I am pretty sure you shouldn't do that.+------+------+
| @x | @id |
+------+------+
| 2 | ,2,3 |
+------+------+
1 row in set (0.00 sec)
But, well, if you do it, you might go all the way down the dirty road and get rid of the need for triggers as well. Here we are collecting the old values of the row as well:
CODE:
set @counter = 0;
set @_id = "";
set @_d = "";
delete from t;
insert into t values (1,10), (2,20), (3,30);
insert into t
values (4,4), (2,1), (3, 1)
on duplicate key update
d= values (d) +
0* ( @counter := @counter +1 ) +
0* ( @_d := concat(@_d, ",", coalesce(d, ""))),
id = concat(values(id),
substring(@_id := concat(@_id, ",", values(id)) , 1, 0));
select @counter, @_id, @_d;
Yields: set @_id = "";
set @_d = "";
delete from t;
insert into t values (1,10), (2,20), (3,30);
insert into t
values (4,4), (2,1), (3, 1)
on duplicate key update
d= values (d) +
0* ( @counter := @counter +1 ) +
0* ( @_d := concat(@_d, ",", coalesce(d, ""))),
id = concat(values(id),
substring(@_id := concat(@_id, ",", values(id)) , 1, 0));
select @counter, @_id, @_d;
CODE:
root@localhost [kris]> select @counter, @_id, @_d;
+----------+------+--------+
| @counter | @_id | @_d |
+----------+------+--------+
| 2 | ,2,3 | ,20,30 |
+----------+------+--------+
1 row in set (0.00 sec)
Do not blame me when some day in the future after a minor update of the server it all stops working and takes your business empire with it. You are only getting what you deserve.
+----------+------+--------+
| @counter | @_id | @_d |
+----------+------+--------+
| 2 | ,2,3 | ,20,30 |
+----------+------+--------+
1 row in set (0.00 sec)
Trackbacks
No Trackbacks
Comments
Display comments as Linear | Threaded
Roland Bouman on :
I have a love-hate relationship with user-defined variables. On the one hand, they offer a marvelous shortcut for what is often cumbersome and hard to do in pure SQL.
At the same time, because the order of assignments is by definition affecting the result, it effectively cuts off any hope for any future optimization (that is, if you rely on the result).
Kristian Köhntopp on :