Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.)

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

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>
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.