MySQL只选择非空值

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

是否可以做一个只取非空值的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?

现在当我select * val1,val2,val3,null,val4 val5,空,空等....但我只想得到结果中不为空的值。不使用循环过滤，这是可能的吗?

7 个解决方案

#1

377

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

您应该使用的是NOT NULL。(比较运算符=和<>在表达式的两边都用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 operator but this is not standard SQL.

为了完整起见，我将提到，在MySQL中，您还可以否定null safe equality操作符，但这不是标准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*/

#2

You can filter out rows that contain a NULL value in a specific column:

您可以过滤掉特定列中包含空值的行:

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:

如果要过滤任何列中包含null的行，请尝试以下操作:

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说，如果你需要这样做，你可能会改变你的数据库设计。

#3

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的字段。

#4

I found this solution:

我发现这个解决方案:

This query select last not null value for each column.

此查询为每个列选择last not null值。

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.

我希望帮助你。

#5

I use the \! command within MySQL to grep out NULL values from the shell:

我使用\ !命令MySQL中的grep从shell中输出空值:

\! mysql -e "SELECT * FROM table WHERE column = 123456\G" | grep -v NULL

It works best with a proper .my.cnf where your database/username/password are specified. That way you just have to surround your select with \! mysql e and | grep -v NULL.

它在指定数据库/用户名/密码的.my.cnf中工作得最好。这样你只需要用\!mysql e和| grep -v NULL。

#6

Yes use NOT NULL in your query like this below.

Yes在您的查询中使用NOT NULL，如下所示。

SELECT * 
FROM table
WHERE col IS NOT NULL;

#7

-3

SELECT * FROM TABLE_NAME
where COLUMN_NAME <> '';

#1

377