0

I'm trying to select the last updated rows' id of my table, then I find this question How to get ID of the last updated row in MySQL?.

SET @uids := null;
UPDATE footable
   SET foo = 'bar'
 WHERE fooid > 5
   AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;

The solution works, but I don't understand why I have to set @uids to null instead of empty string. Why this statement only select affected rows instead of matched rows even with a limit clause appended. Is this a MySQL defined behavior? Which page of the MySQL manual shoud I look up?

mysql> select * from transaction_test;
+----+--------+------------+
| id | value1 | value2     |
+----+--------+------------+
|  1 |      1 | 1460600984 |
|  2 |      2 | 1460598960 |
+----+--------+------------+
2 rows in set (0.00 sec)

mysql>  set @ids=null;update  transaction_test set value2=unix_timestamp(now()) where 1=1 and  ( SELECT @ids:=concat_ws(',',@ids,value1)  ) limit 1;select @ids;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+------+
| @ids |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

mysql>  set @ids='';update  transaction_test set value2=unix_timestamp(now()) where 1=1 and  ( SELECT @ids:=concat_ws(',',@ids,value1)  ) limit 1;select @ids;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

+------+
| @ids |
+------+
| ,1,2 |
+------+
1 row in set (0.00 sec)
Community
  • 1
  • 1
rox
  • 525
  • 7
  • 16
  • Are you applying the `LIMIT` to the `UPDATE`? – Michael Berkowski Apr 14 '16 at 02:20
  • @MichaelBerkowski Yes, I need to update part of the match, and select them. – rox Apr 14 '16 at 02:22
  • [user-variables](http://dev.mysql.com/doc/refman/5.7/en/user-variables.html) says ***In a SELECT statement, each select expression is evaluated only when sent to the client. *** – rox Apr 14 '16 at 02:25
  • @MichaelBerkowski I tried empty string, It didn't work as expected with a limit clause. – rox Apr 14 '16 at 02:26

1 Answers1

2

If you initialize @uids to an empty string, then CONCAT_WS(',', fooid, @uids) will result in fooid, instead of just fooid when it does the first concatenation, because it will concatenate fooid and '' with a comma between them. But when any of the arguments to CONCAT_WS() is NULL, it's ignored completely and no comma is put between it and the adjacent elements.

It only selects the affected rows because AND performs short-circuiting. This means that the parameters are evaluated from left to right, and if the first parameter is FALSE it doesn't evaluate the second parameter at all. So it only executes the @uids := ... assignment when fooid > 5 is TRUE.

If you have a LIMIT clause, the behavior depends on whether the column you're ordering by is indexed. If it is, it will scan the index in order, and then evaluate the WHERE clause, and stop when the limit count is reached, so only the rows within the limit will be included in @uids. Or if you have no ORDER BY clause, it just scans the table until the limit is reached.

However, you can't depend on this reliably, it depends on how the query optimizer analyzes the query.

But if the ordering column is not indexed, or the query optimizer isn't able to perform the above optimization, it may have to scan the entire table, selecting all the rows that match the WHERE clause into a temporary table, and then perform the ordering on that. In that case, @uids will contain all the matching IDs, not just the ones within the limit. You can solve this by using a subquery.

SET @uids := null;
UPDATE footable AS t1
JOIN (SELECT fooid
      FROM footable
      WHERE fooid > 5
      ORDER BY somecolumn
      LIMIT 10) AS t2
ON t1.fooid = t2.fooid
SET t1.foo = 'bar'
WHERE @uids := CONCAT_WS(',', fooid, @uids);
SELECT @uids;
Barmar
  • 741,623
  • 53
  • 500
  • 612