MySQL SELECT 仅非空值

2021-11-20 00:00:00 sql select mysql notnull

是否可以执行只接受 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*/

相关文章