Mysql

選擇條目不為空的列名

  • September 12, 2017

我想要一個表中至少有一個非NULL數據條目的列的列表。

換句話說，我想獲取以下至少返回一個條目的列名：

SELECT DISTINCT column_name FROM table WHERE column_name IS NOT NULL

我嘗試了以下方法：

SELECT column_name
FROM information_schema.columns
WHERE table_name = "table_name"
AND EXISTS (
   SELECT DISTINCT column_name FROM table_name WHERE column_name IS NOT NULL
)

但這也會返回所有條目所在的列名NULL。

那麼如何只獲取那些沒有NULL條目的列呢？

讓我們在我的機器上選擇一個範例表：

mysql> show create table weisci_jaws_staging2.users\G
*************************** 1. row ***************************
      Table: users
Create Table: CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(32) NOT NULL DEFAULT '',
 `passwd` varchar(32) NOT NULL DEFAULT '',
 `user_type` tinyint(4) DEFAULT '2',
 `recovery_key` varchar(48) DEFAULT NULL,
 `name` varchar(255) DEFAULT NULL,
 `email` varchar(255) DEFAULT NULL,
 `url` varchar(255) DEFAULT NULL,
 `timezone` varchar(5) DEFAULT NULL,
 `language` varchar(5) DEFAULT NULL,
 `theme` varchar(24) DEFAULT NULL,
 `editor` varchar(24) DEFAULT NULL,
 `last_login` datetime DEFAULT NULL,
 `createtime` datetime DEFAULT NULL,
 `updatetime` datetime DEFAULT NULL,
 `change_passwd` tinyint(1) NOT NULL DEFAULT '1',
 `never_expire` tinyint(1) NOT NULL DEFAULT '1',
 `bad_passwd_count` smallint(6) DEFAULT '0',
 `last_access` bigint(20) DEFAULT '0',
 `enabled` tinyint(1) NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`),
 UNIQUE KEY `users_username_idx` (`username`)
) ENGINE=MyISAM AUTO_INCREMENT=160 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

mysql> select count(1) from weisci_jaws_staging2.users;
+----------+
| count(1) |
+----------+
|      117 |
+----------+
1 row in set (0.00 sec)

mysql>

有了這張表，這裡有兩個問題：

  • 哪些列可以為空？
  • 哪些列不可為空？

此查詢將為您找到：

select is_nullable,GROUP_CONCAT(column_name) column_list
from information_schema.columns
where table_schema = 'weisci_jaws_staging2'
and   table_name = 'users'
group by is_nullable;

查看該表的查詢結果：

mysql> select is_nullable,GROUP_CONCAT(column_name) column_list
   -> from information_schema.columns
   -> where table_schema = 'weisci_jaws_staging2'
   -> and   table_name = 'users'
   -> group by is_nullable;
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| is_nullable | column_list                                                                                                                        |
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| NO          | id,never_expire,change_passwd,enabled,username,passwd                                                                              |
| YES         | recovery_key,last_access,bad_passwd_count,updatetime,createtime,last_login,editor,user_type,language,timezone,url,email,name,theme |
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql>

好的，我們有兩個列表。我們從中學到什麼？

  • 如果您獲得兩個列表，則無需檢查實際表，因為根據定義該表具有非 NULL 列。

  • 如果你得到一份清單，那麼

    • 如果您只得到 is_nullable=‘NO’，則無需檢查實際表，因為根據定義該表具有非 NULL 列。
    • 如果你只得到 is_nullable=‘YES’，實際的表會有點脆弱。沒有主鍵，你這個可憐的，受折磨的靈魂！現在，您必須求助於從實際表中讀取每一行。

如果您只查找非空列，那麼這將是您想要的查詢：

select GROUP_CONCAT(column_name) nonnull_columns
from information_schema.columns
where table_schema = 'weisci_jaws_staging2'
and   table_name = 'users'
and   is_nullable = 'NO';

這是此查詢的輸出：

mysql> select GROUP_CONCAT(column_name) nonnull_columns
   -> from information_schema.columns
   -> where table_schema = 'weisci_jaws_staging2'
   -> and   table_name = 'users'
   -> and   is_nullable = 'NO';
+-------------------------------------------------------+
| nonnull_columns                                       |
+-------------------------------------------------------+
| id,username,passwd,change_passwd,never_expire,enabled |
+-------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

刪除 GROUP_CONCAT，你會得到這個：

mysql> select column_name nonnull_column
   -> from information_schema.columns
   -> where table_schema = 'weisci_jaws_staging2'
   -> and   table_name = 'users'
   -> and   is_nullable = 'NO';
+----------------+
| nonnull_column |
+----------------+
| id             |
| username       |
| passwd         |
| change_passwd  |
| never_expire   |
| enabled        |
+----------------+
6 rows in set (0.01 sec)

mysql>

試一試 ！！！

注意：請注意，我不需要閱讀實際表格的數據內容。這比閱讀整個表格要高效得多。

更新 2012-11-15 13:40 EDT

@sensware 的答案中的程式碼給出了NULL列。最初的問題要求non-NULL列。我增加了程式碼來測試我的表：

SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation
SELECT CONCAT(
        'SELECT * FROM ('
      ,  GROUP_CONCAT(
           CONCAT(
             'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
           , 'IF('
           ,   'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
           ,   'NULL,'
           ,    QUOTE(COLUMN_NAME)
           , ') AS `column` '
           , 'FROM `',
           REPLACE(TABLE_SCHEMA, '`', '``'), '`.`',
           REPLACE(TABLE_NAME, '`', '``'), '`'
           )
           SEPARATOR ' UNION ALL '
        )
      , ') t WHERE `column` IS NOT NULL'
      )
INTO   @sql
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'weisci_jaws_staging2'
AND    TABLE_NAME = 'users';
SELECT @sql\G
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

這是輸出：

mysql> SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(
   ->          'SELECT * FROM ('
   ->        ,  GROUP_CONCAT(
   ->             CONCAT(
   ->               'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
   ->             , 'IF('
   ->             ,   'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
   ->             ,   'NULL,'
   ->             ,    QUOTE(COLUMN_NAME)
   ->             , ') AS `column` '
   ->             , 'FROM `',
   ->             REPLACE(TABLE_SCHEMA, '`', '``'), '`.`',
   ->             REPLACE(TABLE_NAME, '`', '``'), '`'
   ->             )
   ->             SEPARATOR ' UNION ALL '
   ->          )
   ->        , ') t WHERE `column` IS NOT NULL'
   ->        )
   -> INTO   @sql
   -> FROM   INFORMATION_SCHEMA.COLUMNS
   -> WHERE  TABLE_SCHEMA = 'weisci_jaws_staging2'
   -> AND    TABLE_NAME = 'users';
Query OK, 1 row affected (0.02 sec)

mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT * FROM (SELECT 'users' AS `table`,IF(COUNT(`id`),NULL,'id') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`username`),NULL,'username') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`passwd`),NULL,'passwd') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`user_type`),NULL,'user_type') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`recovery_key`),NULL,'recovery_key') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`name`),NULL,'name') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`email`),NULL,'email') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`url`),NULL,'url') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`timezone`),NULL,'timezone') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`language`),NULL,'language') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`theme`),NULL,'theme') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`editor`),NULL,'editor') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`last_login`),NULL,'last_login') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`createtime`),NULL,'createtime') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`updatetime`),NULL,'updatetime') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`change_passwd`),NULL,'change_passwd') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`never_expire`),NULL,'never_expire') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`bad_passwd_count`),NULL,'bad_passwd_count') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`last_access`),NULL,'last_access') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`enabled`),NULL,'enabled') AS `column` FROM `weisci_jaws_staging2`.`users`) t WHERE `column` IS NOT NULL
1 row in set (0.00 sec)

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

mysql> EXECUTE stmt;
+-------+--------+
| table | column |
+-------+--------+
| users | theme  |
+-------+--------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql>

這給出了 NULL 列。最初的問題要求非 NULL 列。我將程式碼更改為生成的非 NULL。我將通過翻轉以下順序來做到這一點IF..COUNT：

SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation
SELECT CONCAT(
        'SELECT * FROM ('
      ,  GROUP_CONCAT(
           CONCAT(
             'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
           , 'IF('
           ,   'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
           ,    QUOTE(COLUMN_NAME)
           ,   ',NULL'
           , ') AS `column` '
           , 'FROM `',
           REPLACE(TABLE_SCHEMA, '`', '``'), '`.`',
           REPLACE(TABLE_NAME, '`', '``'), '`'
           )
           SEPARATOR ' UNION ALL '
        )
      , ') t WHERE `column` IS NOT NULL'
      )
INTO   @sql
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'weisci_jaws_staging2'
AND    TABLE_NAME = 'users';
SELECT @sql\G
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

讓我們現在執行它…

mysql> SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(
   ->          'SELECT * FROM ('
   ->        ,  GROUP_CONCAT(
   ->             CONCAT(
   ->               'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
   ->             , 'IF('
   ->             ,   'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
   ->             ,    QUOTE(COLUMN_NAME)
   ->             ,   ',NULL'
   ->             , ') AS `column` '
   ->             , 'FROM `',
   ->             REPLACE(TABLE_SCHEMA, '`', '``'), '`.`',
   ->             REPLACE(TABLE_NAME, '`', '``'), '`'
   ->             )
   ->             SEPARATOR ' UNION ALL '
   ->          )
   ->        , ') t WHERE `column` IS NOT NULL'
   ->        )
   -> INTO   @sql
   -> FROM   INFORMATION_SCHEMA.COLUMNS
   -> WHERE  TABLE_SCHEMA = 'weisci_jaws_staging2'
   -> AND    TABLE_NAME = 'users';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT * FROM (SELECT 'users' AS `table`,IF(COUNT(`id`),'id',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`username`),'username',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`passwd`),'passwd',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`user_type`),'user_type',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`recovery_key`),'recovery_key',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`name`),'name',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`email`),'email',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`url`),'url',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`timezone`),'timezone',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`language`),'language',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`theme`),'theme',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`editor`),'editor',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`last_login`),'last_login',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`createtime`),'createtime',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`updatetime`),'updatetime',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`change_passwd`),'change_passwd',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`never_expire`),'never_expire',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`bad_passwd_count`),'bad_passwd_count',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`last_access`),'last_access',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`enabled`),'enabled',NULL) AS `column` FROM `weisci_jaws_staging2`.`users`) t WHERE `column` IS NOT NULL
1 row in set (0.00 sec)

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

mysql> EXECUTE stmt;
+-------+------------------+
| table | column           |
+-------+------------------+
| users | id               |
| users | username         |
| users | passwd           |
| users | user_type        |
| users | recovery_key     |
| users | name             |
| users | email            |
| users | url              |
| users | timezone         |
| users | language         |
| users | editor           |
| users | last_login       |
| users | createtime       |
| users | updatetime       |
| users | change_passwd    |
| users | never_expire     |
| users | bad_passwd_count |
| users | last_access      |
| users | enabled          |
+-------+------------------+
19 rows in set (0.01 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql>

好的，現在可以了。仍然存在問題。該查詢需要讀取整個表。我的測試表只有 117 行和 20 列。具有數百萬行或數十列的更大表呢？我不會去推測，因為我知道程式碼會差幾個數量級。

這就是為什麼我推薦我的答案

select GROUP_CONCAT(column_name) nonnull_columns
from information_schema.columns
where table_schema = 'weisci_jaws_staging2'
and   table_name = 'users'
and   is_nullable = 'NO';

要麼

select column_name nonnull_column
from information_schema.columns
where table_schema = 'weisci_jaws_staging2'
and   table_name = 'users'
and   is_nullable = 'NO';

因為不必檢查實際的數據內容。

我製作的增強程式碼應該只用在所有列都允許NULL值的表上，這非常罕見。

引用自：https://dba.stackexchange.com/questions/28726

相關問答

Mysql

如果沒有行返回，如何顯示返回零？

  • April 18, 2022
檢視問答
Mysql

為什麼我的簡單搜尋查詢太慢了？

  • April 15, 2022
檢視問答
Mysql

我可以根據 Mysql 中的名稱選擇列嗎？

  • January 27, 2021
檢視問答
Mysql

可能為 NULL 單元格的兩列的差異

  • August 19, 2018
檢視問答
Mysql

MySQL 插入 NULL 插入 0

  • June 30, 2017
檢視問答
Mysql

一般數據庫優化 - 正確的數據類型 - 為什麼要避免 NULL？

  • July 17, 2016
檢視問答