在 MySQL 中，您可以仅使用一次比较来检查字段是否为 NULL 或为空吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15980995/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In MySQL, can you check if a field is NULL or empty using only one comparison?
提问by vertigoelectric
If I want to check to see if a field is NULL or empty using a MySQL query, I know I can do something like this:
如果我想使用 MySQL 查询检查字段是否为 NULL 或为空，我知道我可以执行以下操作：
column = '' OR column IS NULL
However, is there any way to check this without doing two separate comparisons?
但是，有没有办法在不进行两次单独比较的情况下进行检查？
回答by thaJeztah
Use COALESCE()to 'normalize' the value (convert NULL values to an empty string);
使用COALESCE()以“正常化”的值（NULL转换值，为空字符串）;
WHERE COALESCE(mycolumn, '') = ''
Read the documentation: COALESCE()
阅读文档： COALESCE()
Or the other way around; convert empty strings to NULL;
或者反过来；将空字符串转换为 NULL；
WHERE NULLIF(mycolumn, '') IS NULL
Documentation: NULLIF()
文档： NULLIF()
Of those two, I would prefer COALESCE() as it is part of the ANSI SQL standard
在这两个中，我更喜欢 COALESCE() 因为它是 ANSI SQL 标准的一部分
You can experiment with it yourself, just do this;
你可以自己试验一下，就这样做；
SELECT
mycolumn AS orig_value,
COALESCE(mycolumn, '') AS coalesce_value,
(COALESCE(mycolumn, '') = '') AS compare_result
FROM mytable;
This will show the original value, the 'coalesce' value and the result of the comparison side by side for every row in the table
这将显示原始值、'coalesce' 值和表中每一行的比较结果
回答by Esailija
WHERE COALESCE(column, '') = ''
回答by PodTech.io
Another method without WHERE, try this..
另一种没有WHERE的方法，试试这个..
Will select both Empty and NULL values
将同时选择 Empty 和 NULL 值
SELECT ISNULL(NULLIF(fieldname,'')) FROM tablename
回答by Marcellus
Try this:
尝试这个：
WHERE NOT(column LIKE '_%')
Without the NOT, the value of column must at least have one character and can not be NULL.
如果没有 NOT，则 column 的值必须至少有一个字符，不能是NULL.
EDIT: MySQL still seems to swallow the NULL value this way. This should work better:
编辑：MySQL 似乎仍然以这种方式吞下 NULL 值。这应该工作得更好：
WHERE IFNULL(column, '') = ''
