MySQL SELECT 仅非空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5285448/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL SELECT only not null values
提问by bryan sammon
Is it possible to do a select statement that takes only NOT NULL values?
是否可以执行仅采用 NOT NULL 值的 select 语句？
Right now I am using this:
现在我正在使用这个：
SELECT * FROM table
And then I have to filter out the null values with a php loop.
然后我必须用 php 循环过滤掉空值。
Is there a way to do:
有没有办法做到：
SELECT * (that are NOT NULL) FROM table
?
?
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?
现在当我选择 * 我得到 val1,val2,val3,null,val4,val5,null,null 等...但我只想在我的结果中得到不为空的值。如果不使用循环过滤，这可能吗？
回答by Martin Smith
You should use IS NOT NULL. (The comparison operators =and <>both give UNKNOWNwith NULLon either side of the expression.)
你应该使用IS NOT NULL. （比较操作符=和<>两个给定UNKNOWN与NULL上表达的任一侧）。
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 operatorbut this is not standard SQL.
为了完整起见，我会提到在 MySQL 中，您还可以否定null 安全相等运算符，但这不是标准 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*/
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.
上面的缺点是它对每列多次扫描表一次。下面可能可以避免这种情况，但我还没有在 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*/
回答by Mark Byers
You can filter out rows that contain a NULL value in a specific column:
您可以过滤掉特定列中包含 NULL 值的行：
SELECT col1, col2, ..., coln
FROM yourtable
WHERE somecolumn IS NOT NULL
If you want to filter out rows that contain a null in any column then try this:
如果要过滤掉任何列中包含空值的行，请尝试以下操作：
SELECT col1, col2, ..., coln
FROM yourtable
WHERE col1 IS NOT NULL
AND col2 IS NOT NULL
-- ...
AND coln IS NOT NULL
Update: Based on your comments, perhaps you want this?
更新：根据您的评论，也许您想要这个？
SELECT * FROM
(
SELECT col1 AS col FROM yourtable
UNION
SELECT col2 AS col FROM yourtable
UNION
-- ...
UNION
SELECT coln AS col FROM yourtable
) T1
WHERE col IS NOT NULL
And I agre with Martin that if you need to do this then you should probably change your database design.
我同意 Martin 的观点，如果您需要这样做，那么您可能应该更改您的数据库设计。
回答by Alan Chavez
Select * from your_table
WHERE col1 and col2 and col3 and col4 and col5 IS NOT NULL;
The only disadvantage of this approach is that you can only compare 5 columns, after that the result will always be false, so I do compare only the fields that can be NULL.
这种方法的唯一缺点是您只能比较 5 列，之后结果将始终为 false，因此我只比较可以是NULL.
回答by porquero
I found this solution:
我找到了这个解决方案：
This query select last not null value for each column.
此查询为每列选择最后一个非空值。
Example
例子
If you have a table:
如果你有一张桌子：
id|title|body
1 |t1 |b1
2 |NULL |b2
3 |t3 |NULL
you get:
你得到：
title|body
t3 |b2
Query
询问
SELECT DISTINCT (
SELECT title
FROM test
WHERE title IS NOT NULL
ORDER BY id DESC
LIMIT 1
) title, (
SELECT body
FROM test
WHERE body IS NOT NULL
ORDER BY id DESC
LIMIT 1
) body
FROM test
I hope help you.
我希望能帮到你。
回答by soulshake
I use the \!command within MySQL to grep out NULL values from the shell:
我使用\!MySQL 中的命令从 shell 中 grep 出 NULL 值：
\! mysql -e "SELECT * FROM table WHERE column = 123456\G" | grep -v NULL
It works best with a proper .my.cnfwhere your database/username/password are specified. That way you just have to surround your selectwith \! mysql eand | grep -v NULL.
它最适合.my.cnf在指定数据库/用户名/密码的地方使用。这样你只需select要用\! mysql e和包围你| grep -v NULL。
回答by Basant
Following query working for me
以下查询对我有用
when i have set default value of column 'NULL' then
当我设置了列 'NULL' 的默认值时
select * from table where column IS NOT NULL
and when i have set default value nothing then
当我设置默认值时什么都没有
select * from table where column <>''
回答by Developer
MYSQL IS NOT NULL WITH JOINS AND SELECT, INSERT INTO, DELETE & LOGICAL OPERATOR LIKE OR , NOT
MYSQL IS NOT NULL WITH JOINS AND SELECT, INSERT INTO, DELETE & LOGICAL OPERATOR LIKE OR , NOT
Using IS NOT NULL On Join Conditions
SELECT * FROM users
LEFT JOIN posts ON post.user_id = users.id
WHERE user_id IS NOT NULL;
Using IS NOT NULL With AND Logical Operator
SELECT * FROM users
WHERE email_address IS NOT NULL
AND mobile_number IS NOT NULL;
Using IS NOT NULL With OR Logical Operator
SELECT * FROM users
WHERE email_address IS NOT NULL
OR mobile_number IS NOT NULL;
回答by Jonathan Vasiliou
Yes use NOT NULLin your query like this below.
是的，NOT NULL在您的查询中使用，如下所示。
SELECT *
FROM table
WHERE col IS NOT NULL;
回答by Venkat Kallem
SELECT * FROM TABLE_NAME
where COLUMN_NAME <> '';
