Using a dynamic variable that may be null in the where clause of a mysql statement

Question

I select a value into a mysql variable. Eg...

select          @userId := userId
from            myTable 
where           id = ?;

The value of @userId may be null.

Now I want to update the rows in another table based on the value of @userId.

Simply stating "where userId = @userId" will not always work, since "userId = null" will never come back true in mysql, even if userId is null.

The following statement appears to work fine instead...

update          myOtherTable
set             something = "something"
where           (
                    userId = @userId or 
                    (@userId is null and userId is null)
                );

...however I just wanted to run this by the gurus here, to make sure it is mostly "okay". (Evaluating a variable apart by itself in the where clause feels a bit strange to me.)

Thoughts?

Answer 1

See https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_equal-to

<=>

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

mysql> select null <=> 1;
+------------+
| null <=> 1 |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql> select null <=> null;
+---------------+
| null <=> null |
+---------------+
|             1 |
+---------------+