- 查询的user_id 不为null
mysql> SELECT @userId :=user_id FROM ta_user_info WHERE account_name = '13870957087';
SELECT IF(@userId IS NOT NULL , 1,0);
SELECT @userId;
结果:
+---------------------+
| @userId :=user_id |
+---------------------+
| 5000000000001173892 |
+---------------------+
1 row in set
+-------------------------------+
| IF(@userId IS NOT NULL , 1,0) |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set
+---------------------+
| @userId |
+---------------------+
| 5000000000001173892 |
+---------------------+
1 row in set
从结果可以看出用户变量@userId获得了查询结果,并通过if函数判断不为null
- 查询的user_id 为null
mysql> SELECT @userId :=user_id FROM ta_user_info WHERE account_name = 'xxxx';
SELECT IF(@userId IS NOT NULL , 1,0);
SELECT @userId;
结果:
Empty set
+-------------------------------+
| IF(@userId IS NOT NULL , 1,0) |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set
+---------------------+
| @userId |
+---------------------+
| 5000000000001173892 |
+---------------------+
1 row in set
发现用户变量@userId确和我们想的不一样,而是上次查询的结果只值,而且if函数也不是null。推测是null不能赋值给用户变量
3.查询的user_id 不为null,换成了其他非null的值
mysql> SELECT @userId :=user_id FROM ta_user_info WHERE account_name = '490454361@qq.com';
SELECT IF(@userId IS NOT NULL , 1,0);
SELECT @userId;
结果:
+---------------------+
| @userId :=user_id |
+---------------------+
| 5889529241746227315 |
+---------------------+
1 row in set
+-------------------------------+
| IF(@userId IS NOT NULL , 1,0) |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set
+---------------------+
| @userId |
+---------------------+
| 5889529241746227315 |
+---------------------+
1 row in set
可以看出用户变量@userId又被赋予了新的值。说明第二步查询的推论是正确的,即null不能赋值给用户变量
所以想通过用户变量使用if函数判断查询的userId是否为null 的方法也不可行。