The Apache Mahout projects goal is to build a scalable machi

发布时间：2020-05-23 15:30:41 所属栏目：MySql 来源：互联网

导读：问题：使用游标遍历时，发现使用select var into tmp where var=?然后判断if tmp is null时，不能走完所有的遍历。经debug发现，当var为空时，则跳出游标的遍历。解决方式：使用if not exists(select var into tmp where var=?)时，则ok。这个可以从mysql官方

问题：

使用游标遍历时，发现使用

select var into tmp where var=?

然后判断if tmp is null时，不能走完所有的遍历。经debug发现，

当var为空时，则跳出游标的遍历。

解决方式：

使用if not exists(select var into tmp where var=?)时，则ok。

这个可以从mysql官方文档中找到原因：

1. select var into tmp where var=? 中where 条件不支持为空，如下面红色部分所示。

Problems the value a common source confusion newcomers SQL,who often think that the same thing an empty string . This the . mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');
Both statements insert a value into the phone column,but the first inserts a NULL value and the second inserts an empty string. The meaning of the first can be regarded as “phone number is not known” and the meaning of the second can be regarded as “the person is known to have no phone,and thus no phone number.”

To help with NULL handling,you can use the IS NULL and IS NOT NULL operators and the IFNULL() function.

In SQL,the NULL value is never true in comparison to any other value,even NULL. An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression. All columns in the following example return NULL:

mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);
To search for column values that are NULL,you cannot use an expr = NULL test. The following statement returns no rows,because expr = NULL is never true for any expression:

mysql> SELECT * FROM my_table WHERE phone = NULL;
To look for NULL values,you must use the IS NULL test. The following statements show how to find the NULL phone number and the empty phone number:

mysql> SELECT FROM my_table WHERE phone IS NULL;
mysql> SELECT FROM my_table WHERE phone = '';
See Section 3.3.4.6,“Working with NULL Values”,for additional information and examples.

You can add an index on a column that can have NULL values if you are using the MyISAM,InnoDB,or BDB,or MEMORY storage engine. Otherwise,you must declare an indexed column NOT NULL,and you cannot insert NULL into the column.

When reading data with LOAD DATA INFILE,empty or missing columns are updated with ''. To load a NULL value into a column,use N in the data file. The literal word “NULL” may also be used under some circumstances. See Section 13.2.6,“LOAD DATA INFILE Syntax”.

When using DISTINCT,GROUP BY,or ORDER BY,all NULL values are regarded as equal.

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!