Databases Reference
In-Depth Information
The query increments the
@x
variable when there is a conflict that causes the
UPDATE
portion of the query to execute. It hides the variable's value inside an expression that
is multiplied by zero, so the variable doesn't affect the ultimate value assigned to the
column. The MySQL client protocol returns the total rows affected, so there is no need
to count that with a user variable.
Making evaluation order deterministic
Most problems with user variables come from assigning to them and reading them at
different stages in the query. For example, it doesn't work predictably to assign them
in the
SELECT
statement and read from them in the
WHERE
clause. The following query
might look like it will just return one row, but it doesn't:
mysql>
SET @rownum := 0;
mysql>
SELECT actor_id, @rownum := @rownum + 1 AS cnt
->
FROM sakila.actor
->
WHERE @rownum <= 1;
+----------+------+
| actor_id | cnt |
+----------+------+
| 1 | 1 |
| 2 | 2 |
+----------+------+
This happens because the
WHERE
and
SELECT
are different stages in the query execution
process. This is even more obvious when you add another stage to execution with an
ORDER BY
:
mysql>
SET @rownum := 0;
mysql>
SELECT actor_id, @rownum := @rownum + 1 AS cnt
->
FROM sakila.actor
->
WHERE @rownum <= 1
->
ORDER BY first_name;
This query returns every row in the table, because the
ORDER BY
added a filesort and the
WHERE
is evaluated before the filesort. The solution to this problem is to assign and read
in the
same
stage of query execution:
mysql>
SET @rownum := 0;
mysql>
SELECT actor_id, @rownum AS rownum
->
FROM sakila.actor
->
WHERE (@rownum := @rownum + 1) <= 1;
+----------+--------+
| actor_id | rownum |
+----------+--------+
| 1 | 1 |
+----------+--------+
Pop quiz: what will happen if you add the
ORDER BY
back to this query? Try it and see.
If you didn't get the results you expected, why not? What about the following query,
where the
ORDER BY
changes the variable's value and the
WHERE
clause evaluates it?
