blocks|key|492089|text|这将选择some_col为NULL或''+(空字符串)的所有行|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|492090|SELECT+*+FROM+table+WHERE+some_col+IS+NULL+OR+some_col+=+'';|code-block|syntax|javascript|492091|entityMap^0|4|8|D|4|I|2|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

This will select all rows where <code>some_col</code> is <code>NULL</code> or <code>''</code> (empty string)

<pre><code>SELECT * FROM table WHERE some_col IS NULL OR some_col = '';
</code></pre>

blocks|key|707808|text|根据SQL-92标准的定义，当比较两个不同宽度的字符串时，较窄的值将用空格右填充，以使其与较宽的值具有相同的宽度。因此，所有完全由空格(包括零空格)组成的字符串值将被视为相等，例如|type|unstyled|depth|inlineStyleRanges|entityRanges|data|707809|''+=+'+'+IS+TRUE
''+=+'++'+IS+TRUE
'+'+=+'++'+IS+TRUE
'++'+=+'++++++'+IS+TRUE
etc|code-block|syntax|javascript|707810|因此，无论some_col值由多少个空格组成，这都应该有效：|offset|length|style|CODE|707811|SELECT+*+
++FROM+T
+WHERE+some_col+IS+NULL+
+++++++OR+some_col+=+'+';|707812|或者更简洁地说：|707813|SELECT+*+
++FROM+T
+WHERE+NULLIF(some_col,+'+')+IS+NULL;|707814|entityMap^0|0|0|5|8|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

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. 

<pre><code>'' = ' ' IS TRUE
'' = ' ' IS TRUE
' ' = ' ' IS TRUE
' ' = ' ' IS TRUE
etc
</code></pre>

Therefore, this should work regardless of how many spaces make up the <code>some_col</code> value:

<pre><code>SELECT * 
 FROM T
 WHERE some_col IS NULL 
 OR some_col = ' ';
</code></pre>

or more succinctly:

<pre><code>SELECT * 
 FROM T
 WHERE NULLIF(some_col, ' ') IS NULL;
</code></pre>

blocks|key|492165|text|编写条件的一种更短的方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|492166|WHERE+some_col+>+''|code-block|syntax|javascript|492167|因为null+>+''会生成unknown，所以这会过滤掉null和空字符串。|offset|length|style|CODE|492168|entityMap^0|0|0|2|9|E|7|T|4|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|N|$]]

A shorter way to write the condition:

<pre><code>WHERE some_col &gt; ''
</code></pre>

Since <code>null &gt; ''</code> produces <code>unknown</code>, this has the effect of filtering out both <code>null</code> and empty strings.

blocks|key|707553|text|您可以使用WHERE+col+IS+NULL或WHERE+col+IS+NOT+NULL来测试列是null还是not+null，例如|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|707554|SELECT+myCol+
FROM+MyTable+
WHERE+MyCol+IS+NULL+|code-block|syntax|javascript|707555|在您的示例中，有各种空格的排列。您可以使用TRIM去掉空格，也可以使用COALESCE默认一个空值(COALESCE将从您支持的值中返回第一个非空值。|707556|例如：|707557|SELECT+myCol
FROM+MyTable
WHERE+TRIM(COALESCE(MyCol,+''))+=+''+|707558|最后的查询将返回MyCol为null或为任意长度空格的行。|707559|如果可以避免这种情况，最好不要在WHERE子句中的列上使用函数，因为这会使使用索引变得困难。如果您只是想检查列是否为null或为空，最好这样做：|707560|SELECT+myCol
FROM+MyTable
WHERE+MyCol+IS+NULL+OR+MyCol+=++''+|707561|有关更多信息，请参阅TRIM+COALESCE和IS+NULL。|707562|另请参阅MySQL文档中的Working+with+null+values|707563|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim|1|http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce|2|http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_is-null|3|http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html^0|5|H|N|L|0|0|L|4|Z|8|0|0|0|8|5|0|0|0|A|4|0|F|8|1|O|7|2|0|D|O|3|0^^$0|@$1|2|3|4|5|6|7|1E|8|@$9|1F|A|1G|B|C]|$9|1H|A|1I|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|1J|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1K|8|@$9|1L|A|1M|B|C]|$9|1N|A|1O|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|1P|8|@]|D|@]|E|$]]|$1|O|3|P|5|H|7|1Q|8|@]|D|@]|E|$I|J]]|$1|Q|3|R|5|6|7|1R|8|@$9|1S|A|1T|B|C]]|D|@]|E|$]]|$1|S|3|T|5|6|7|1U|8|@]|D|@]|E|$]]|$1|U|3|V|5|H|7|1V|8|@]|D|@]|E|$I|J]]|$1|W|3|X|5|6|7|1W|8|@]|D|@$9|1X|A|1Y|1|1Z]|$9|20|A|21|1|22]|$9|23|A|24|1|25]]|E|$]]|$1|Y|3|Z|5|6|7|26|8|@]|D|@$9|27|A|28|1|29]]|E|$]]|$1|10|3|-4|5|6|7|2A|8|@]|D|@]|E|$]]]|11|$12|$5|13|14|15|E|$16|17]]|18|$5|13|14|15|E|$16|19]]|1A|$5|13|14|15|E|$16|1B]]|1C|$5|13|14|15|E|$16|1D]]]]

You can test whether a column is null or is not null using <code>WHERE col IS NULL</code> or <code>WHERE col IS NOT NULL</code> e.g.
<pre><code>SELECT myCol 
FROM MyTable 
WHERE MyCol IS NULL 
</code></pre>
In your example you have various permutations of white space. You can strip white space using <code>TRIM</code> and you can use <code>COALESCE</code> to default a NULL value (COALESCE will return the first non-null value from the values you suppy.
e.g.
<pre><code>SELECT myCol
FROM MyTable
WHERE TRIM(COALESCE(MyCol, '')) = '' 
</code></pre>
This final query will return rows where <code>MyCol</code> is null or is any length of whitespace.
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:
<pre><code>SELECT myCol
FROM MyTable
WHERE MyCol IS NULL OR MyCol = '' 
</code></pre>
See <a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim" rel="noreferrer">TRIM</a> <a href="http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce" rel="noreferrer">COALESCE</a> and <a href="http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_is-null" rel="noreferrer">IS NULL</a> for more info.
Also <a href="http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html" rel="noreferrer">Working with null values</a> from the MySQL docs

blocks|key|707880|text|另一个没有WHERE的方法，试试这个..|type|unstyled|depth|inlineStyleRanges|entityRanges|data|707881|将同时选择空值和null值|707882|SELECT+ISNULL(NULLIF(fieldname,''))++FROM+tablename|code-block|syntax|javascript|707883|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Another method without WHERE, try this.. 

Will select both Empty and NULL values 

<pre><code>SELECT ISNULL(NULLIF(fieldname,'')) FROM tablename
</code></pre>

blocks|key|707971|text|任一|type|unstyled|depth|inlineStyleRanges|entityRanges|data|707972|SELECT+IF(field1+IS+NULL+or+field1+=+'',+'empty',+field1)+as+field1+from+tablename|code-block|syntax|javascript|707973|或|707974|SELECT+case+when+field1+IS+NULL+or+field1+=+''
++++++++then+'empty'
++++++++else+field1
+++end+as+field1+from+tablename|707975|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Either 

<pre><code>SELECT IF(field1 IS NULL or field1 = '', 'empty', field1) as field1 from tablename
</code></pre>

or

<pre><code>SELECT case when field1 IS NULL or field1 = ''
 then 'empty'
 else field1
 end as field1 from tablename
</code></pre>

blocks|key|491937|text|试一试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|491938|SELECT+0+IS+NULL+,++''+IS+NULL+,+NULL+IS+NULL

->+0,+0,+1|code-block|syntax|javascript|491939|或|491940|SELECT+ISNULL('++')+,+ISNULL(+NULL+)
+->+0+,1|491941|Reference|offset|length|491942|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_is-null^0|0|0|0|0|0|9|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

try

<pre><code>SELECT 0 IS NULL , '' IS NULL , NULL IS NULL

-&gt; 0, 0, 1
</code></pre>

or

<pre><code>SELECT ISNULL(' ') , ISNULL( NULL )
 -&gt; 0 ,1
</code></pre>

<a href="http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_is-null" rel="noreferrer">Reference</a>

blocks|key|492189|text|我讨厌我的数据库中杂乱的字段。如果列可能是空字符串或null，我宁愿在每次执行select之前解决这个问题，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|492190|UPDATE+MyTable+SET+MyColumn=NULL+WHERE+MyColumn='';
SELECT+*+FROM+MyTable+WHERE+MyColumn+IS+NULL|code-block|syntax|javascript|492191|这保持了数据的整洁，只要您不会因为某些原因而特别需要区分NULL和NULL。|492192|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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:

<pre><code>UPDATE MyTable SET MyColumn=NULL WHERE MyColumn='';
SELECT * FROM MyTable WHERE MyColumn IS NULL
</code></pre>

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

blocks|key|492218|text|这句话对我来说更清晰、更易读：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|492219|select+*+from+my_table+where+ISNULL(NULLIF(some_col,+''));|code-block|syntax|javascript|492220|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This statement is much cleaner and more readable for me:

<pre><code>select * from my_table where ISNULL(NULLIF(some_col, ''));
</code></pre>

blocks|key|491915|text|如果您希望在执行ORDER+BY时最后显示空值，请尝试执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|491916|SELECT+*+FROM+my_table+WHERE+NULLIF(some_col,+'')+IS+NULL;|code-block|syntax|javascript|491917|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>SELECT * FROM my_table WHERE NULLIF(some_col, '') IS NULL;
</code></pre>

blocks|key|492314|text|你也可以这样做|type|unstyled|depth|inlineStyleRanges|entityRanges|data|492315|SELECT+*+FROM+table+WHERE+column_name+LIKE+''|code-block|syntax|javascript|492316|相反的存在|492317|SELECT+*+FROM+table+WHERE+column_name+NOT+LIKE+''|492318|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can also do 

<pre><code>SELECT * FROM table WHERE column_name LIKE ''
</code></pre>

The inverse being

<pre><code>SELECT * FROM table WHERE column_name NOT LIKE ''
</code></pre>

blocks|key|492336|text|SELECT+*+FROM+tbl+WHERE+trim(IFNULL(col,''))+<>+'';|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|492337|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT * FROM tbl WHERE trim(IFNULL(col,'')) &lt;&gt; '';
</code></pre>

blocks|key|708028|text|select+*+from+table+where+length(RTRIM(LTRIM(column_name)))+>+0|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|708029|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select * from table where length(RTRIM(LTRIM(column_name))) &gt; 0
</code></pre>

blocks|key|708069|text|获取NULL、0、'‘的行|type|unstyled|depth|inlineStyleRanges|entityRanges|data|708070|++++SELECT+*+FROM+table+WHERE+some_col+IS+NOT+TRUE;|code-block|syntax|javascript|708071|获取不带NULL、0、'‘的行|708072|++++SELECT+*+FROM+table+WHERE+some_col+IS+TRUE;|708073|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Get rows with NULL, 0, '', ' ', ' '
<pre><code> SELECT * FROM table WHERE some_col IS NOT TRUE;
</code></pre>
Get rows without NULL, 0, '', ' ', ' '
<pre><code> SELECT * FROM table WHERE some_col IS TRUE;
</code></pre>

blocks|key|707640|text|检查是否为空|type|unstyled|depth|inlineStyleRanges|entityRanges|data|707641|$column+is+null
isnull($column)|code-block|syntax|javascript|707642|707643|$column+!=+""|707644|但是，您应该始终为列设置NOT+NULL，|707645|mysql+optimization+can+handle+only+one+IS+NULL+level|offset|length|707646|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html^0|0|0|0|0|0|0|1G|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|G|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|H|3|I|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|J|3|K|5|6|7|11|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|12|8|@]|9|@$N|13|O|14|1|15]]|A|$]]|$1|P|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|Q|$R|$5|S|T|U|A|$V|W]]]]

Check for null

<pre><code>$column is null
isnull($column)
</code></pre>

Check for empty

<pre><code>$column != ""
</code></pre>

However, you should always set NOT NULL for column, 
<a href="http://dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html" rel="nofollow">mysql optimization can handle only one IS NULL level</a>

blocks|key|707951|text|如果数据类型为字符串且行为空，请尝试此操作|type|unstyled|depth|inlineStyleRanges|entityRanges|data|707952|SELECT+*+FROM+table+WHERE+column_name+IS+NULL+OR+column_name+=+''|code-block|syntax|javascript|707953|如果数据类型为int或column为0，请尝试执行以下操作|707954|SELECT+*+FROM+table+WHERE+column_name+>+=+0|707955|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

try this if the datatype are string and row is null
<pre><code>SELECT * FROM table WHERE column_name IS NULL OR column_name = ''
</code></pre>
if the datatype are int or column are 0 then try this
<pre><code>SELECT * FROM table WHERE column_name &gt; = 0
</code></pre>

blocks|key|492268|text|SELECT+column_name+FROM+table_name+WHERE+column_name+IN+(NULL,+'')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|492269|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT column_name FROM table_name WHERE column_name IN (NULL, '')
</code></pre>

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?

<pre><code>(e.g. null or '' or ' ' or ' ' and ...)
</code></pre>

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

数据库

MySQL

我的表中有一列可能包含null或空值。如何检查表中存在的行中的列是否为空或null？(e.g. null or '' or '  ' or '      ' and ...)

问如何在MySQL中检查列是否为空或null？
EN

回答 17

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中检查列是否为空或null？EN

回答 17

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中检查列是否为空或null？
EN