mysql 用户变量不能赋予null值

来源：互联网发布：monaco for windows 编辑：程序博客网时间：2024/06/07 17:36

查询的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 的方法也不可行。

0 0