MySQL SELECT仅非空值 [英] MySQL SELECT only not null values
问题描述
是否可以执行仅包含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等.但是我只想获取结果中不为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;
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仅非空值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!