如何检查 MySQL 中的列是空的还是空的？

How do I check if a column is empty or null in MySQL?

提问by priya

I have a column in a table which might contain null or empty values. How do I check if a column is empty or null in the rows present in a table?

我在表中有一列可能包含空值或空值。如何检查表中存在的行中的列是空的还是空的？

(e.g. null or '' or '  ' or '      ' and ...)

回答by ma?ek

This will select all rows where some_colis NULLor ''(empty string)

这将选择some_col是NULL或''（空字符串）的所有行

SELECT * FROM table WHERE some_col IS NULL OR some_col = '';

回答by onedaywhen

As defined by the SQL-92 Standard, when comparing two strings of differing widths, the narrower value is right-padded with spaces to make it is same width as the wider value. Therefore, all string values that consist entirely of spaces (including zero spaces) will be deemed to be equal e.g.

根据 SQL-92 标准的定义，当比较两个不同宽度的字符串时，较窄的值用空格右填充，使其与较宽的值具有相同的宽度。因此，所有完全由空格（包括零空格）组成的字符串值将被视为相等，例如

'' = ' ' IS TRUE
'' = '  ' IS TRUE
' ' = '  ' IS TRUE
'  ' = '      ' IS TRUE
etc

Therefore, this should work regardless of how many spaces make up the some_colvalue:

因此，无论有多少空格组成该some_col值，这都应该有效：

SELECT * 
  FROM T
 WHERE some_col IS NULL 
       OR some_col = ' ';

or more succinctly:

或更简洁地说：

SELECT * 
  FROM T
 WHERE NULLIF(some_col, ' ') IS NULL;

回答by Andomar

A shorter way to write the condition:

写条件的一种更简短的方法：

WHERE some_col > ''

Since null > ''produces unknown, this has the effect of filtering out both nulland empty strings.

由于null > ''产生unknown，这具有过滤掉null空字符串和空字符串的效果。

回答by Code Magician

You can test whether a column is null or is not null using WHERE col IS NULLor WHERE col IS NOT NULLe.g.

您可以使用WHERE col IS NULL或WHERE col IS NOT NULL例如测试列是否为空或不为空

SELECT myCol 
FROM MyTable 
WHERE MyCol IS NULL 

In your example you have various permutations of white space. You can strip white space using TRIMand you can use COALESCEto default a NULL value (COALESCE will return the first non-null value from the values you suppy.

在您的示例中，您有各种空白排列。您可以使用删除空格，TRIM并且可以使用COALESCE默认的 NULL 值（COALESCE 将从您提供的值中返回第一个非空值。

e.g.

例如

SELECT myCol
FROM MyTable
WHERE TRIM(COALESCE(MyCol, '') = '' 

This final query will return rows where MyColis null or is any length of whitespace.

这个最终查询将返回MyCol空行或任意长度的空白行。

If you can avoid it, it's better not to have a function on a column in the WHERE clause as it makes it difficult to use an index. If you simply want to check if a column is null or empty, you may be better off doing this:

如果可以避免它，最好不要在 WHERE 子句中的列上使用函数，因为它会使使用索引变得困难。如果您只是想检查列是否为空或为空，最好这样做：

SELECT myCol
FROM MyTable
WHERE MyCol IS NULL OR MyCol =  '' 

See TRIMCOALESCEand IS NULLfor more info.

有关详细信息，请参阅TRIM COALESCE和IS NULL。

Also Working with null valuesfrom the MySQL docs

还使用MySQL 文档中的空值

回答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 Reynante Daitol

Either

任何一个

SELECT IF(field1 IS NULL or field1 = '', 'empty', field1) as field1 from tablename

or

或者

SELECT case when field1 IS NULL or field1 = ''
        then 'empty'
        else field1
   end as field1 from tablename

回答by Keith Johnson

I hate messy fields in my databases. If the column might be a blank string or null, I'd rather fix this before doing the select each time, like this:

我讨厌数据库中凌乱的字段。如果该列可能是空字符串或空值，我宁愿在每次进行选择之前修复此问题，如下所示：

UPDATE MyTable SET MyColumn=NULL WHERE MyColumn='';
SELECT * FROM MyTable WHERE MyColumn IS NULL

This keeps the data tidy, as long as you don't specifically need to differentiate between NULL and empty for some reason.

这可以保持数据整洁，只要您出于某种原因不需要特别区分 NULL 和空。

回答by diEcho

try

尝试

SELECT 0 IS NULL ,  '' IS NULL , NULL IS NULL

-> 0, 0, 1

or

或者

SELECT ISNULL('  ') , ISNULL( NULL )
 -> 0 ,1

Reference

参考

回答by Amaynut

This statement is much cleaner and more readable for me:

这条语句对我来说更清晰、更易读：

select * from my_table where ISNULL(NULLIF(some_col, ''));

回答by Vikash Pandey

While checking null or Emptyvalue for a column in my project, I noticed that there are some support concern in various Databases.

在检查null or Empty项目中某列的值时，我注意到各种数据库中存在一些支持问题。

Every Database doesn't support TRIMmethod.

每个数据库都不支持 TRIM方法。

Below is the matrix just to understand the supported methods by different databases.

下面的矩阵只是为了了解不同数据库支持的方法。

The TRIM function in SQL is used to remove specified prefix or suffix from a string. The most common pattern being removed is white spaces. This function is called differently in different databases:

SQL 中的 TRIM 函数用于从字符串中删除指定的前缀或后缀。被删除的最常见模式是空格。该函数在不同数据库中的调用方式不同：

• MySQL:TRIM(), RTRIM(), LTRIM()
• Oracle:RTRIM(), LTRIM()
• SQL Server:RTRIM(), LTRIM()

• MySQL：TRIM(), RTRIM(), LTRIM()
• 甲骨文：RTRIM(), LTRIM()
• SQL 服务器：RTRIM(), LTRIM()

How to Check Empty/Null :-

如何检查空/空：-

Below are two different ways according to different Databases-

以下是根据不同数据库的两种不同方式-

The syntax for these trim functions are:

这些修剪函数的语法是：

1. Use of Trim to check-

   SELECT FirstName FROM UserDetails WHERE TRIM(LastName) IS NULL

2. Use of LTRIM & RTRIM to check-

   SELECT FirstName FROM UserDetails WHERE LTRIM(RTRIM(LastName)) IS NULL

1. 使用 Trim 检查-

   SELECT FirstName FROM UserDetails WHERE TRIM(LastName) IS NULL

2. 使用 LTRIM 和 RTRIM 检查-

   SELECT FirstName FROM UserDetails WHERE LTRIM(RTRIM(LastName)) IS NULL

Above both ways provide same result just use based on your DataBase support. It Just returns the FirstNamefrom UserDetailstable if it has an empty LastName

以上两种方式提供相同的结果，只是根据您的数据库支持使用。如果它有空，它只返回FirstNamefromUserDetails表LastName

Hoping this will help you :)

希望这会帮助你:)