SQL 检查“空值或空值”的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23766084/
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
Best way to check for "empty or null value"
提问by Andrus
What is best way to check if value is null or empty string in Postgres sql statements?
在 Postgres sql 语句中检查值是 null 还是空字符串的最佳方法是什么？
Value can be long expression so it is preferable that it is written only once in check.
值可以是长表达式，因此最好只在检查中写入一次。
Currently I'm using:
目前我正在使用：
coalesce( trim(stringexpression),'')=''
But it looks a bit ugly.
但是看起来有点难看。
stringexpressionmay be char(n)column or expression containing char(n)columns with trailing spaces.
stringexpression可能是char(n)包含char(n)带有尾随空格的列的列或表达式。
What is best way?
什么是最好的方法？
回答by Erwin Brandstetter
The expression stringexpression = ''yields:
该表达式stringexpression = ''产生：
TRUE? .. for ''(or for anystring consisting of only spaces with the data type char(n))NULL? .. for NULLFALSE.. for anythingelse
TRUE? .. for ''（或任何仅由空格组成的数据类型的字符串char(n)）NULL？.. 对于NULLFALSE.. 对于其他任何事情
So to check for: "stringexpressionis either NULL or empty":
所以要检查：“stringexpression是 NULL 还是空”：
(stringexpression = '') IS NOT FALSE
Or the reverse approach (may be easier to read):
或者相反的方法（可能更容易阅读）：
(stringexpression <> '') IS NOT TRUE
Works for any character typeincluding the obsolescent char(n)which is hardly ever useful.
The manual about comparison operators.
适用于任何字符类型，包括char(n)几乎没有用的过时字符。
关于比较运算符的手册。
Oruse the expression you already had, just without the trim()which would be useless for char(n)(see below), or it would include strings consisting of only spaces in the test for other character types:
或者使用您已经拥有的表达式，只是没有trim()which 将无用char(n)（见下文），或者它会包含仅由空格组成的字符串，用于其他字符类型的测试：
coalesce(stringexpression, '') = ''
But the expressions at the top are faster.
但是顶部的表达式更快。
Asserting the opposite: "stringexpressionis neither NULL nor empty"is even simpler:
断言相反：“stringexpression既不是 NULL 也不是空”甚至更简单：
stringexpression <> ''
About char(n)
关于 char(n)
Do not confuse this data type with other character types like varchar(n), varchar, textor "char"(with quotes), which are all useful data types. This is about the outdated data type with very limited usefulness: char(n), short for: character(n). Also, charand characterare short for char(1)/ character(1)(same thing).
不要将此数据类型与其他字符类型混淆，例如varchar(n), varchar,text或"char"（带引号），它们都是有用的数据类型。这是关于用处非常有限的过时数据类型：char(n)，缩写：character(n)。此外，char和character是char(1)/ character(1)（相同的东西）的缩写。
In char(n)(unlike other string types!) an empty stringis not different from any other string consisting of only spaces. All of these are folded to nspaces in char(n)per definition of the type. It follows logically that this works for char(n)as well:
在char(n)（与其他字符串类型不同！）中，空字符串与任何其他仅由空格组成的字符串没有区别。所有这些都在每个类型的定义中折叠为n 个空格char(n)。从逻辑上讲，这也适用于char(n)：
coalesce(stringexpression, '') = ''
Just as much as these (which wouldn't work for other character types):
与这些一样多（不适用于其他字符类型）：
coalesce(stringexpression, ' ') = ' '
coalesce(stringexpression, '') = ' '
Demo
演示
Empty string equals any string of spaces when cast to char(n):
当转换为 时，空字符串等于任何空格字符串char(n)：
SELECT ''::char(5) = ''::char(5) AS eq1
,''::char(5) = ' '::char(5) AS eq2
,''::char(5) = ' '::char(5) AS eq3;
eq1 | eq2 | eq3 ----+-----+---- t | t | t
eq1 | eq2 | eq3 ----+-----+---- t | t | t
Test for "null or empty string" with char(n):
测试“空或空字符串” char(n)：
SELECT stringexpression
,stringexpression = '' AS simple_test
,(stringexpression = '') IS NOT FALSE AS test1
,(stringexpression <> '') IS NOT TRUE AS test2
,coalesce(stringexpression, '') = '' AS test_coalesce1
,coalesce(stringexpression, ' ') = ' ' AS test_coalesce2
,coalesce(stringexpression, '') = ' ' AS test_coalesce3
FROM (
VALUES
('foo'::char(5))
, ('')
, (NULL)
, (' ') -- not different from '' in char(n)
) sub(stringexpression);
stringexpression | simple_test | test1 | test2 | test_coalesce1 | test_coalesce2 | test_coalesce3 ------------------+-------------+-------+-------+----------------+----------------+---------------- foo | f | f | f | f | f | f | t | t | t | t | t | t | | t | t | t | t | t | t | t | t | t | t | t
stringexpression | simple_test | test1 | test2 | test_coalesce1 | test_coalesce2 | test_coalesce3 ------------------+-------------+-------+-------+----------------+----------------+---------------- foo | f | f | f | f | f | f | t | t | t | t | t | t | | t | t | t | t | t | t | t | t | t | t | t
Test for "null or empty string" with text
测试“空或空字符串” text
SELECT stringexpression
,stringexpression = '' AS simple_test
,(stringexpression = '') IS NOT FALSE AS test1
,(stringexpression <> '') IS NOT TRUE AS test2
,coalesce(stringexpression, '') = '' AS test_coalesce1
,coalesce(stringexpression, ' ') = ' ' AS test_coalesce2
,coalesce(stringexpression, '') = ' ' AS test_coalesce3
FROM (
VALUES
('foo'::text)
, ('')
, (NULL)
, (' ') -- different from '' in a sane character type like text
) sub(stringexpression);
stringexpression | simple_test | test1 | test2 | test_coalesce1 | test_coalesce2 | test_coalesce3 ------------------+-------------+-------+-------+----------------+----------------+---------------- foo | f | f | f | f | f | f | t | t | t | t | f | f | | t | t | t | t | f | f | f | f | f | f | f
stringexpression | simple_test | test1 | test2 | test_coalesce1 | test_coalesce2 | test_coalesce3 ------------------+-------------+-------+-------+----------------+----------------+---------------- foo | f | f | f | f | f | f | t | t | t | t | f | f | | t | t | t | t | f | f | f | f | f | f | f
dbfiddle here
Old SQL Fiddle
Related:
有关的：
回答by sam
To check for null and empty:
要检查 null 和空：
coalesce(string, '') = ''
To check for null, empty and spaces (trim the string)
检查空、空和空格（修剪字符串）
coalesce(TRIM(string), '') = ''
回答by yglodt
Checking for the length of the string also works and is compact:
检查字符串的长度也有效并且很紧凑：
where length(stringexpression) > 0;
回答by ?wistak35
If there may be empty trailing spaces, probably there isn't better solution. COALESCEis just for problems like yours.
如果可能有空的尾随空格，可能没有更好的解决方案。COALESCE只针对像你这样的问题。
回答by Mowazzem Hosen
another way is
另一种方式是
nullif(trim(stringExpression),'') is not null
回答by TarasB
Something that I saw people using is stringexpression > ''. This may be not the fastest one, but happens to be one of the shortest.
我看到人们使用的东西是stringexpression > ''. 这可能不是最快的，但恰好是最短的。
Tried it on MS SQL as well as on PostgreSQL.
在 MS SQL 和 PostgreSQL 上都试过了。
回答by John VE
A lot of the answers are the shortest way, not the necessarily the best way if the column has lots of nulls. Breaking the checks up allows the optimizer to evaluate the check faster as it doesn't have to do work on the other condition.
许多答案是最短的方法，如果列有很多空值，则不一定是最好的方法。打破检查允许优化器更快地评估检查，因为它不必在其他条件下工作。
(stringexpression IS NOT NULL AND trim(stringexpression) != '')
The string comparison doesn't need to be evaluated since the first condition is false.
由于第一个条件为假，因此不需要评估字符串比较。
回答by Ambrish Rajput
If database having large number of records then null checkcan take more time
you can use null check in different ways like :
1) where columnname is null2) where not exists()3) WHERE (case when columnname is null then true end)
如果数据库具有大量记录，则null check可能需要更多时间，您可以以不同的方式使用空检查，例如：1) where columnname is null2) where not exists()3)WHERE (case when columnname is null then true end)
回答by Danilo da Silva
My preffered way to compare nullable fields is: NULLIF(nullablefield, :ParameterValue) IS NULL AND NULLIF(:ParameterValue, nullablefield) IS NULL . This is cumbersome but is of universal use while Coalesce is impossible in some cases.
我比较可空字段的首选方法是： NULLIF(nullablefield, :ParameterValue) IS NULL AND NULLIF(:ParameterValue, nullablefield) IS NULL 。这很麻烦，但通用，而 Coalesce 在某些情况下是不可能的。
The second and inverse use of NULLIF is because "NULLIF(nullablefield, :ParameterValue) IS NULL" will always return "true" if the first parameter is null.
NULLIF 的第二个反向使用是因为如果第一个参数为空，“NULLIF(nullablefield, :ParameterValue) IS NULL”将始终返回“true”。
