mysql查看表中的空值_在MySQL表中查找只有空值的所有列

您可以通过动态创建(从

INFORMATION_SCHEMA.COLUMNS表)包含要执行的SQL的字符串，然后从该字符串中执行

preparing a statement来避免使用该过程。

我们希望构建的SQL将如下所示：

SELECT * FROM (

SELECT 'tableA' AS `table`,

IF(COUNT(`column_a`), NULL, 'column_a') AS `column`

FROM tableA

UNION ALL

SELECT 'tableB' AS `table`,

IF(COUNT(`column_b`), NULL, 'column_b') AS `column`

FROM tableB

UNION ALL

-- etc.

) t WHERE `column` IS NOT NULL

这可以使用以下方式完成：

SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation

SELECT CONCAT(

'SELECT * FROM ('

, GROUP_CONCAT(

'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'

, 'IF('

, 'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'

, 'NULL,'

, QUOTE(COLUMN_NAME)

, ') AS `column` '

, 'FROM `', REPLACE(TABLE_NAME, '`', '``'), '`'

SEPARATOR ' UNION ALL '

)

, ') t WHERE `column` IS NOT NULL'

)

INTO @sql

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = DATABASE();

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;