MySQL - 选择具有空列的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14112211/
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 - Selecting rows with null columns
提问by Zalaboza
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 or”的查询中。
can it be done with mysql?
可以用mysql完成吗？
采纳答案by FLICKER
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)
回答by Ravi
select * from tablename where col1 is NULL or col2 is NULL
Result
结果
ID | col1 | col2
------------------
2 | NULL | Val2
3 | VAL1 | NULL
回答by fthiella
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。
回答by Muhammad Raheel
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 来设置默认值
