How do uninitialized variables differ from NULL in Mysql?

Question

Related, please read first for context: MySQL stored procedure: loop through table, delete rows. Logic problem: won't exit loop because of LIMIT option in query

I think this is really strange. I don't understand why the solution in that problem works the way it does, can someone explain the solution please?

This is stated in the mysql documentation:

If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.

why does it make a difference if you set a variable to NULL before you use it, if that is the default for uninitialized variables anyway?

(I am aware, that it is very dirty programming, not to initialize variables, but still I want to understand the behaviour.)

Answer 1

There are two ways to set a variable from a query, and they behave differently, especially when no record is matched.

You can SELECT ... INTO the variable...

select first_name into @z from actor where actor_id = 999999;

Or you can SET it to the value returned by a scalar subquery.

set @z = (select first_name from actor where actor_id = 999999);

Illustrating the difference with the sample database...

mysql> use sakila;
Database changed

Our variable starts out NULL.

mysql> select @z;
+------+
| @z   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> SELECT first_name INTO @z FROM actor WHERE actor_id = 1;
Query OK, 1 row affected (0.00 sec)

We matched a row and our variable is set.

mysql> select @z;
+----------+
| @z       |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)

We try to select a record that doesn't exist:

mysql> select first_name into @z from actor where actor_id = 999999;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

So what's in our variable now?

mysql> select @z;
+----------+
| @z       |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)

I think this explains the confusion. When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like SELECT ... INTO that won't reset the value if nothing is found.

On the other hand, if we use the other construct, the variable is reset when no record is matched.

mysql> select @z;
+----------+
| @z       |
+----------+
| PENELOPE |
+----------+
1 row in set (0.00 sec)

mysql> set @z = (select first_name from actor where actor_id = 999999);
Query OK, 0 rows affected (0.00 sec)

mysql> select @z;
+------+
| @z   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql>

Answer 2

And this means that you cannot get the NULL'ing behavior on more fields unless you use

SET @a=NULL;
SET @b=NULL;
SET @c=NULL;
SELECT a,b,c INTO @a,@b,@c FROM x WHERE y;

or is there a way to write

SET (@a,@b,@c) = (SELECT a,b,c FROM x WHERE y);

? Thanks Claus