MySQL - 选择具有空列的行

How can I select any row that contains empty or null column?

如何选择包含空列或空列的任何行?

I'm trying to run a check on my table, in which I want to see if any of my rows contain a column that doesn't hold a value..

我正试图在我的桌子上运行检查,我想看看我的任何行是否包含一个没有值的列。

example table: demo

示例表:演示

+----+------+------+
| ID | col1 | col2 |
+----+------+------+
| 1  | VAL1 | Val2 |
| 2  | NULL | Val2 |
| 3  | VAL1 | NULL |
+----+------+------+

I want the query to return rows 2-3 , noting that I have many columns in actual table so I don't want to include it in the query with 'where or'.

我希望查询返回2-3行,注意我在实际表中有很多列,所以我不想将它包含在'where或'的查询中。

can it be done with mysql?

可以用mysql完成吗?

4 个解决方案

#1

the best answer that does not need to hard-code the column names is:

不需要对列名进行硬编码的最佳答案是:

DECLARE @sqlStr VARCHAR(max) = (
        SELECT stuff((
                    SELECT 'and ' + c.NAME + ' is null '
                    FROM sys.columns c
                    WHERE object_name(object_id) = 'yourtablename'
                    ORDER BY c.NAME
                    FOR XML PATH('')
                    ), 1, 3, '')
        )

SET @sqlStr = 'select * from ' + yourtablename + ' where ' + @sqlStr

PRINT @sqlStr

EXEC (@sqlStr)

#2

select * from tablename where col1 is NULL or col2 is NULL

Result

 ID | col1  | col2
 ------------------     
 2  | NULL  | Val2     
 3  | VAL1  | NULL

#3

If you really want not to use ORs, you could use this:

如果你真的不想使用OR,你可以使用这个:

SELECT *
FROM demo
WHERE concat(col1, col2, ...) is Null

or this:

SELECT *
FROM demo
WHERE col1+col2+... is null

but while it's makes the query easier to write, it won't make it faster. I would suggest you just to use ORs.

虽然它使查询更容易编写,但它不会使它更快。我建议你只使用OR。

#4

-2

SELECT 
    IFNULL(col1,0),
    IFNULL(col2,0)
FROM mytable
WHERE col1 IS NULL or col2 IS NULL

You can use IFNULL to set default value

您可以使用IFNULL来设置默认值

#1