如何在表单行SQL中检查是否有任何值为Null

Each row of data in my table has about 8 elements. I want to check to see if any of the elements = NULL in a certain row, something like this:

我表中的每行数据大约有8个元素。我想检查某行中是否有任何元素= NULL,如下所示:

UPDATE item_list SET is_tradable='no' WHERE item_name=:itemname AND element_1 = NULL OR element_2 = NULL or element_3... etc.

Can I do the same thing but check if any of them are NULL without going through each item? Like:

我可以做同样的事情,但检查其中任何一个是否为NULL而不通过每个项目?喜欢:

UPDATE item_list SET is_tradable='no' WHERE item_name=:itemname AND anyElement = NULL;

2 个解决方案

#1

No, you cannot do what you want. You need to list all the columns out.

不,你不能做你想做的事。您需要列出所有列。

However, your query will not do this. You need to use IS NULL rather than = NULL:

但是,您的查询不会这样做。您需要使用IS NULL而不是= NULL:

UPDATE item_list
    SET is_tradable = 'no'
    WHERE item_name = :itemname AND
          (element_1 IS NULL OR element_2 IS NULL or element_3... etc.)

Also, remember the parentheses when mixing AND and OR.

另外,请记住混合AND和OR时的括号。

There are ways to "simplify" the calculation. For instance, concat() will return NULL if any argument is NULL:

有一些方法可以“简化”计算。例如,如果任何参数为NULL,则concat()将返回NULL:

UPDATE item_list
    SET is_tradable = 'no'
    WHERE item_name = :itemname AND
          concat(element_1, element_2, . . . ) IS NULL

As do the arithmetic operators (but your question does not state the type of the arguments).

和算术运算符一样(但你的问题没有陈述参数的类型)。

#2

There is a way but you need to mention all the column names in null comparison something as

有一种方法,但你需要提到null比较中的所有列名称

mysql> select least(null,'aa',null,'bb') ;
+----------------------------+
| least(null,'aa',null,'bb') |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.00 sec)

Here its getting the least values from a group of values.

这里从一组值中获取最少的值。

So you may use as

所以你可以使用as

WHERE item_name=:itemname 
AND least(element_1,element_2,element_3...) is null

#1