MySQL 检查字段是否为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5520500/
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
Check field if empty
提问by Efox
Is the query correct if I wanted to check if the field has other characters other than null and empty?
如果我想检查该字段是否有除 null 和空以外的其他字符，查询是否正确？
select CASE WHEN description IS NULL THEN 'null'
WHEN description IS NOT NULL THEN 'not null' ELSE 'something else' END
AS 'description'from milestone where name like '%Test%' or name like '%test%';
+-------------+
| description |
+-------------+
| not null |
+-------------+
1 row in set (0.00 sec)
回答by RichardTheKiwi
Null and empty means NULL + '' (empty string)?
Null 和 empty 表示 NULL + '' （空字符串）？
select CASE WHEN description IS NULL or description = '' THEN 'null or empty'
ELSE 'not null' END
AS 'description'
In your original query, there is no possibility of a third case because IS NULL and IS NOT NULL are complementary, between them they have covered all possibilities.
在您的原始查询中，不可能出现第三种情况，因为 IS NULL 和 IS NOT NULL 是互补的，它们之间涵盖了所有可能性。
Also, unless you are using case-sensitive collation (very rare, and never by default unless you specifically nominate one), MySQL is not Oracle - these two queries will work the same:
此外，除非您使用区分大小写的排序规则（非常罕见，并且除非您特别指定，否则默认情况下永远不会），MySQL 不是 Oracle - 这两个查询的工作方式相同：
where name like '%Test%' or name like '%test%'
where name like '%test%'
Because MySQL will match strings case-insensitively
因为 MySQL 会不区分大小写地匹配字符串
回答by Sliq
Simple IF solution:
简单的 IF 解决方案：
IF (my_field = '', "not null", "null")
By the way, I personally like to use it like that (shorthand syntax):
顺便说一句，我个人喜欢这样使用它（速记语法）：
IF (my_field = '', 1, 0)
回答by Ivan Marjanovic
Maybe you can try something like this:
也许你可以尝试这样的事情：
select IF(LENGTH(description) > 0,'not null', 'null or empty') from milestone
回答by Dojo
I would create a "stored function" (what in MSSQL is called a user-defined function):
我会创建一个“存储函数”（在 MSSQL 中称为用户定义函数）：
CREATE FUNCTION isNullOrSpaces(s TEXT)
RETURNS BOOLEAN DETERMINISTIC
RETURN (IFNULL(LENGTH(TRIM(s)), 0) = 0);
select
isNullOrSpaces(null) 'null',
isNullOrSpaces('') 'empty string',
isNullOrSpaces(' ') 'spaces',
isNullOrSpaces('
') 'spaces, tab and newline';
Note that the last case - where the value contains tabs and newlines - returns 0 (FALSE). This is thanks to a bad implementation (IMHO) of the built-in TRIM function, which doesn't remove all whitespace.
请注意，最后一种情况 - 值包含制表符和换行符 - 返回 0 (FALSE)。这要归功于内置 TRIM 函数的错误实现（恕我直言），它不会删除所有空格。
I would have preferred to make the function isNullOrWhiteSpace, but since this is good enough for many cases and a pure-SQL implementation of a proper TRIM function will be slow, I figured this will do. If you need to handle all whitespace, consider making what MySQL calls a user-defined function (native function is a better name IMO) - see for instance https://www.codeproject.com/articles/15643/mysql-user-defined-functions.
我更愿意让函数 isNullOrWhiteSpace ，但由于这对于许多情况来说已经足够了，并且正确的 TRIM 函数的纯 SQL 实现会很慢，我认为这可以做到。如果您需要处理所有空格，请考虑将 MySQL 称为用户定义函数（本机函数是 IMO 的更好名称）-例如参见https://www.codeproject.com/articles/15643/mysql-user-defined -功能。
You may also wish to make a version that returns empty string if the argument is NULL, empty or spaces. This version is mainly useful for the WHERE or HAVING clauses of a query, but the only difficult part of making one that works the same except returning empty string or the original string is to name the function appropriately..! Something like this:
如果参数为 NULL、空或空格，您可能还希望创建一个返回空字符串的版本。此版本主要用于查询的 WHERE 或 HAVING 子句，但除了返回空字符串或原始字符串之外，唯一困难的部分是正确命名函数..！像这样的东西：
CREATE FUNCTION trimEx(s TEXT)
RETURNS TEXT DETERMINISTIC
RETURN IF(IFNULL(LENGTH(TRIM(s)), 0) = 0, '', TRIM(s));
select
trimEx(null) 'null',
trimEx('') 'empty string',
trimEx(' ') 'spaces',
trimEx(' not empty ') 'contains text';
回答by Phantasma
You can also try to use REGEXP:
您也可以尝试使用REGEXP：
// 0 is considered empty
WHERE `field` [NOT] REGEXP '[^0]'
// 0 is not considered empty
WHERE `field` [NOT] REGEXP '[^.]'
回答by Pavan G Jakati
// checks if the field not null
where field_name !=' '
