MYSQL 获取所有列中只有 NULL 的行

Question

我有一个表，其中一些行在除描述列之外的所有列中仅包含 NULL。

    description | colA | colB
    -------------------------
    Peter       | bla  | NULL
    Frank       | NULL | NULL
    George      | NULL | blub

如何在不显式命名的情况下选择所有列中具有 NULL 的所有行？

伪代码:SELECT ``decription`` WHERE all other columns are NULL 应该返回 Frank。我如何做到这一点？

Answer 1

引用INFORMATION_SCHEMA并使用PREPARE语句，这里给出了一种解决方案，并提供了完整的demo。

解决方法引用:Select all columns except one in MySQL?

SQL:

-- data
create table t1(description char(20), colA char(20), colB char(20));
insert into t1 values
(    'Peter'       , 'bla', NULL),
(    'Frank'       , NULL , NULL),
(    'George'      , NULL , 'blub');
SELECT * FROM t1;

-- Query wanted
SET @sql = CONCAT(
    'SELECT description FROM t1 WHERE COALESCE(',
    (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'description,', '') 
     FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test'), 
    ') IS NULL');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

输出:

mysql> SELECT * FROM t1;
+-------------+------+------+
| description | colA | colB |
+-------------+------+------+
| Peter       | bla  | NULL |
| Frank       | NULL | NULL |
| George      | NULL | blub |
+-------------+------+------+
3 rows in set (0.00 sec)

mysql>
mysql> SET @sql = CONCAT(
    -> 'SELECT description FROM t1 WHERE COALESCE(',
    -> (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'description,', '')
    ->  FROM INFORMATION_SCHEMA.COLUMNS
    ->  WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test'),
    -> ') IS NULL');
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt1 FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt1;
+-------------+
| description |
+-------------+
| Frank       |
+-------------+
1 row in set (0.00 sec)

在 PREPARE 之前详述 SET 语句:

1. SET是生成如下字符串。

   SELECT description FROM t1 WHERE COALESCE(<所有列的列表，description 除外>) IS NULL

2. 使用引用链接中的方法从 INFORMATION_SCHEMA.COLUMNS 查询。

To use in your own environment, you need to

1. Change table name 't1' to your own table name;

2. Change TABLE_SCHEMA 'test' to your own database name.