Mysql:从表中选择*，其中可能在没有OR的情况下输入col IN(空，“") [英] Mysql: select * from table where col IN (null, "") possible without OR

问题描述

是否可以在不使用or的情况下选择Mysql中的空字符串和NULL值?

is its somehow possible to do a select for empty strings and NULL values in Mysql without using or?

此:

   select * from table where col IN (null, "");

不起作用，它会忽略null(或者可能将其与字符串'null'匹配

doesnt work, it ignores the null (or possibly matches it with the string 'null'

谢谢， P.V.哥迪恩

thanks, P.V. Goddijn

推荐答案

SELECT  *
FROM    mytable
WHERE   COALESCE(col, '') = ''

但是，请注意，如果对索引建立索引，则比OR查询的效率要高得多:

Note, however, than OR query will be much more efficient if the column is indexed:

SELECT  *
FROM    mytable
WHERE   col = '' OR col IS NULL

这将使用索引上的ref_or_null访问路径.

This will use ref_or_null access path on the index.

如果需要与NULLs一起从值列表中进行选择，只需将所有非空值放入列表中并添加单个OR IS NULL条件:

If you need to select from a list of values along with NULLs, just put all not-null values into the list and add a single OR IS NULL condition:

SELECT  *
FROM    mytable
WHERE   col IN ('val1', 'val2', 'val3') OR col IS NULL

这也会在col上使用索引.

这篇关于Mysql:从表中选择*，其中可能在没有OR的情况下输入col IN(空，“")的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！