如何检查mysql中的列是否为空

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 ...)

15 个解决方案

#1

310

This will select all rows where some_col is NULL or '' (empty string)

这将选择some_col为空或“(空字符串)”的所有行

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

#2

114

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_col value:

因此，无论有多少空间构成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;

#3

A shorter way to write the condition:

一种更简短的描述条件的方式:

WHERE some_col > ''

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

因为空> "产生未知，这就产生了过滤空字符串和空字符串的效果。

#4

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

您可以使用col为null或col为not null来测试列是否为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 TRIM and you can use COALESCE to default a NULL value (COALESCE will return the first non-null value from the values you suppy.

在您的示例中，有各种各样的空白排列。您可以使用TRIM剥离空白，并可以使用COALESCE来默认一个空值(COALESCE将从您所提供的值返回第一个非空值。

e.g.

如。

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

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

这个最后的查询将返回MyCol为null或任何长度的空格。

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 TRIM COALESCE and IS NULL for more info.

有关更多信息，请参见微调合并，并为NULL。

Also Working with null values from the MySQL docs

也可以使用MySQL文档中的空值

#5

Another method without WHERE, try this..

另一种方法没有地方，试试这个。

Will select both Empty and NULL values

是否同时选择空值和空值

SELECT ISNULL(NULLIF(fieldname,''))  FROM tablename

#6

try

试一试

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

-> 0, 0, 1

或

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

Reference

参考

#7

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:

我讨厌数据库中的杂乱字段。如果列可能是空字符串或null，我宁愿在每次执行select之前修改它:

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和empty。

#8

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

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

Every Database doesn't support TRIM method.

每个数据库都不支持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()
MySQL:修剪(),空白(),LTRIM()
Oracle: RTRIM(), LTRIM()
Oracle:空白(),LTRIM()
SQL Server: RTRIM(), LTRIM()
LTRIM SQL Server:空白()()

How to Check Empty/Null :-

如何检查空/空:-

Below are two different ways according to different Databases-

下面是根据不同的数据库的两种不同的方法

The syntax for these trim functions are:

这些修剪功能的语法如下:

Use of Trim to check-

使用装饰检查-

SELECT FirstName FROM UserDetails WHERE TRIM(LastName) IS NULL

在TRIM(LastName)为空的地方，从UserDetails中选择FirstName
Use of LTRIM & RTRIM to check-

使用LTRIM & RTRIM检查-

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

在LTRIM(RTRIM(LastName)为空时，从UserDetails中选择FirstName

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

以上两种方法都提供相同的结果，只是基于您的数据库支持。如果UserDetails表中有一个空的LastName，它就会返回FirstName

Hoping this will help you :)

希望这能帮助你:

#9

If you want to have NULL values presented last when doing an ORDER BY, try this:

如果您希望在执行ORDER BY时最后显示空值，请尝试以下操作:

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

#10

This statement is much cleaner and more readable for me:

这句话对我来说更清晰，更有可读性:

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

#11

Either

要么

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

或

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

#12

Check for null

检查空

$column is null
isnull($column)

Check for empty

检查空

$column != ""

However, you should always set NOT NULL for column,
mysql optimization can handle only one IS NULL level

但是，应该始终为列设置NOT NULL, mysql优化只能处理一个为NULL级别

#13

You can also do

你也可以做

SELECT * FROM table WHERE column_name LIKE ''

The inverse being

的逆

SELECT * FROM table WHERE column_name NOT LIKE ''

#14

-1

try this if the datatype are string and row is null

如果数据类型为字符串且行为空，请尝试此操作

SELECT * FROM table WHERE column_name IS NULL OR column_name = ''

if the the datatype are int or column are 0 then try this

如果数据类型为int或列为0，那么尝试此操作

SELECT * FROM table WHERE column_name > = 0

#15

-2

SELECT column_name FROM table_name WHERE column_name IN (NULL, '')

#1

310