MySQL 哪里都不是空的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3108262/
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
WHERE all IS NOT NULL
提问by Sam
How can I run a MySQL query that selects everything that is not null? It would be something like
如何运行选择所有非空的 MySQL 查询？它会是这样的
SELECT * FROM schedule WHERE ((all)) IS NOT NULL
Do I just remove the all and go..?
我只是删除所有然后去..？
SELECT * FROM schedule WHERE IS NOT NULL
回答by nos
You'll have to explicitly state the condition on each column, so e.g.
您必须明确说明每一列的条件，例如
SELECT *
FROM schedule
WHERE id IS NOT NULL
AND foo IS NOT NULL
AND bar IS NOT NULL; -- and so on..
回答by UltraCommit
You can concatenate the fields in order to write only a where-condition:
您可以连接字段以仅编写 where-condition：
SELECT *
FROM SCHEDULE
WHERE ID || FOO || BAR IS NOT NULL;
回答by Hans Ke?ing
It depend on what you mean exactly by "everything that is not null":
这取决于“不为空的所有内容”的确切含义：
all columns must not be
nullselect * from schedule where col1 is not null AND col2 is not null AND ..at least one column should be
non-nullselect * from schedule where col1 is not null OR col 2 is not null OR ..
所有列不得为
nullselect * from schedule where col1 is not null AND col2 is not null AND ..至少一列应该是
non-nullselect * from schedule where col1 is not null OR col 2 is not null OR ..
At least this is how you would do it in "general sql". I don't know if MySql has special syntax for this.
至少这是您在“通用 sql”中的做法。我不知道 MySql 是否有特殊的语法。
回答by Anax
You need to get a list of the columns of your table, by looking at the information_schema database.
您需要通过查看 information_schema 数据库来获取表列的列表。
Let's suppose that your database is called mydataand your table in question is named mytable. You can get the list of the table's nullable columns by issuing the following statement:
假设您的数据库被调用mydata并且您的相关表被命名为mytable。您可以通过发出以下语句来获取表的可为空列的列表：
SELECT `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'mydata'
AND `TABLE_NAME` = 'mytable'
AND `IS_NULLABLE` = 'YES'
Our final query will look like this:
我们的最终查询将如下所示：
SELECT * FROM `mydata`.`mytable`
WHERE CONCAT(<list of columns>) IS NOT NULL
All we are missing now is the list of nullable columns, comma-separated. We're going to use the GROUP_CONCATfunction to produce the final statement, which we will execute like this:
我们现在缺少的只是可空列的列表，以逗号分隔。我们将使用该GROUP_CONCAT函数来生成最终语句，我们将像这样执行：
SET @query = CONCAT(
'SELECT * FROM `mydata`.`mytable` WHERE CONCAT(',
(SELECT GROUP_CONCAT(COLUMN_NAME)
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'mydata' AND
`TABLE_NAME` = 'mytable'
AND `IS_NULLABLE` = 'YES'),
') IS NOT NULL');
PREPARE stmt_name FROM @query;
EXECUTE stmt_name;
References:
参考：
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
回答by Martin Smith
I've just looked at your linked questionand agree with Guffa's answer that you should normalise your database.
我刚刚查看了您链接的问题，并同意 Guffa 的回答，即您应该规范化您的数据库。
The above looks more like a spreadsheet then something that belongs in an RDBMS.
上面看起来更像是一个电子表格，而不是属于 RDBMS 的东西。
To answer your concerns about this being the way you want it displayed. You could write a pivot query and put it in a view to mimic your current table structure and use this for your display query.
回答您对这是您希望它显示的方式的担忧。您可以编写一个数据透视查询并将其放在视图中以模拟您当前的表结构并将其用于显示查询。
This will avoid the need for some horrific 24 column WHERE clause whenever you want to search for data or find out if it is NULL as in this question.
每当您想搜索数据或找出它是否为 NULL 时，这将避免需要一些可怕的 24 列 WHERE 子句，如本问题所示。
回答by Dipu
this works only with char and varchar type. If you are having other datatypes it throws error.
这仅适用于 char 和 varchar 类型。如果您有其他数据类型，则会引发错误。
SELECT *
FROM SCHEDULE
WHERE coalesce(ID, FOO, BAR) IS NOT NULL;
inconsistent datatypes: expected CHARgot DATENote: here FOOis of date type.
不一致的数据类型：预期CHAR得到DATE注意：这里FOO是日期类型。
回答by Evan Carroll
I would notdo this, but to replace someone else's horrible idea., especially do not do this -- this is what they suggested:
我不会这样做，而是为了取代别人的可怕想法。，尤其是不要这样做——这是他们的建议：
SELECT *
FROM SCHEDULE
WHERE ID || FOO || BAR IS NOT NULL;
Don't do this either, but at least it isn't as bad...
也不要这样做，但至少它没有那么糟糕......
SELECT *
FROM SCHEDULE
WHERE coalesce(ID, FOO, BAR) IS NOT NULL;
This at least works on other versions of SQL, and some compilers will expand it to a series of IS NOT NULL.
这至少适用于其他版本的 SQL，并且一些编译器会将其扩展为一系列IS NOT NULL.
回答by Sam
Use XML so you don't have to iterate through all your columns:
使用 XML，因此您不必遍历所有列：
--Your Source Table
CREATE TABLE [dbo].[schedule](
[id] [nchar](10) NULL,
[col1] [nchar](10) NULL,
[col2] [nchar](10) NULL,
[col3] [nchar](10) NULL
) ON [PRIMARY]
--I know my result should show items NOT NULL, like id:2,col1,val:'a'
INSERT INTO [schedule](id,col1,col2,col3)
values
(1,null,null,null),
(2,'a',null,null),
(3,null,'b',null),
(4,null,null,null),
(5,null,null,'c')
--temp placeholder table
CREATE TABLE #tempsch (id VARCHAR(max) not null, schColumns XML)
--Load temp table with XML version of your table
INSERT INTO #tempsch (id, schColumns)
SELECT s.id,
( SELECT *
FROM [schedule] AS x
WHERE x.id = s.id
FOR XML PATH('bar'), TYPE, ROOT('foo')
) AS xml_format_string
FROM [schedule] AS s
--This select should return all values Where all is NOT NULL
SELECT id,
sd.c.value('local-name(.)', 'VARCHAR(MAX)') AS elementName,
sd.c.value('.', 'VARCHAR(MAX)') AS elementValue
FROM #tempsch s
CROSS APPLY schColumns.nodes('/foo/bar/*') sd(c)
WHERE
sd.c.value('local-name(.)', 'VARCHAR(MAX)') <> 'id'
回答by David Swindells
If you are using another programming language combined with sql, you could speed up this process by looping through a list of column names and using a parameter to do the if not null check rather than have to type them all in individually e.g
如果您使用另一种与 sql 结合的编程语言，您可以通过循环列名列表并使用参数进行 if not null 检查来加快此过程，而不必单独键入它们，例如
**code SQL**
Select *
FROM Schedule
WHERE @ColumnName IS NOT NULL
**code c#**
for(int i =0; i<parameterArray[].length; i++)
{
command.Parameters.Add(new SqlParameter("ColumnName", parameterArray[i]));
command.Execute();
}
I would suggest using transactions so you can do the above in one batch after the for loop has run through.
我建议使用事务，这样您就可以在 for 循环运行后一批执行上述操作。
