MySQL SELECT 仅非空值-数据库问题

MySQL SELECT only not null values(MySQL SELECT 仅非空值)

本文介绍了MySQL SELECT 仅非空值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

是否可以执行只接受 NOT NULL 值的 select 语句?

Is it possible to do a select statement that takes only NOT NULL values?

现在我正在使用这个:

SELECT * FROM table

然后我必须用 php 循环过滤掉空值.

And then I have to filter out the null values with a php loop.

有没有办法:

SELECT * (that are NOT NULL) FROM table

现在当我选择 * 我得到 val1,val2,val3,null,val4,val5,null,null 等...但我只想得到结果中不为空的值.不用循环过滤可以吗?

Right now when I select * I get val1,val2,val3,null,val4,val5,null,null etc.... but I just want to get the values that are not null in my result. Is this possible without filtering with a loop?

推荐答案

你应该使用 IS NOT NULL.(比较运算符 = 和 <> 都给出 UNKNOWN 和 NULL 在表达式的两侧.)

You should use IS NOT NULL. (The comparison operators = and <> both give UNKNOWN with NULL on either side of the expression.)

SELECT * 
FROM table 
WHERE YourColumn IS NOT NULL;

为了完整起见，我会提到在 MySQL 中你也可以否定 null 安全相等运算符但这不是标准 SQL.

Just for completeness I'll mention that in MySQL you can also negate the null safe equality operator but this is not standard SQL.

SELECT *
FROM table 
WHERE NOT (YourColumn <=> NULL);

已编辑以反映评论.听起来您的表格可能不是第一范式，在这种情况下，更改结构可能会使您的任务更容易.不过，还有其他几种方法...

Edited to reflect comments. It sounds like your table may not be in first normal form in which case changing the structure may make your task easier. A couple of other ways of doing it though...

SELECT val1 AS val
FROM  your_table
WHERE val1 IS NOT NULL
UNION ALL
SELECT val2 
FROM  your_table
WHERE val2 IS NOT NULL
/*And so on for all your columns*/

上面的缺点是它对每一列多次扫描表一次.下面可能可以避免这种情况，但我还没有在 MySQL 中测试过.

The disadvantage of the above is that it scans the table multiple times once for each column. That may possibly be avoided by the below but I haven't tested this in MySQL.

SELECT CASE idx
         WHEN 1 THEN val1
         WHEN 2 THEN val2
       END AS val
FROM   your_table
        /*CROSS JOIN*/
       JOIN (SELECT 1 AS idx
                   UNION ALL
                   SELECT 2) t
HAVING val IS NOT NULL  /*Can reference alias in Having in MySQL*/

这篇关于MySQL SELECT 仅非空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！