MySQL如何无需指定表名列名，统计全库各表列非空值数量？

Got it, here's a pure SQL approach that automatically generates the non-null count for every column in every table in your target MySQL database—no manual table/column listing required:

-- Optional: Increase if you have many tables/columns to avoid GROUP_CONCAT length limits
SET SESSION group_concat_max_len = 1000000;

-- Target the current database; replace DATABASE() with 'your_db_name' for a specific DB
SET @target_db = DATABASE();

-- Build the dynamic SQL query
SET @dynamic_sql = (
  SELECT GROUP_CONCAT(
    CONCAT(
      "SELECT '", table_name, "' AS `Table`, '", column_name, "' AS `Column`, COUNT(`", column_name, "`) AS `Count` FROM `", table_schema, "`.`", table_name, "`"
    ) SEPARATOR " UNION ALL "
  )
  FROM information_schema.columns
  WHERE table_schema = @target_db
);

-- Execute the generated query
PREPARE stmt FROM @dynamic_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

How this works: #

1. Metadata Pull: We use information_schema.columns—MySQL's built-in table that stores all schema details—to get every table and column in your target database.
2. Dynamic Query Construction: Using GROUP_CONCAT, we stitch together individual SELECT statements for each column. Each statement returns the table name, column name, and count of non-null values (since COUNT(column) automatically ignores NULL entries).
3. Execute: A prepared statement runs the generated SQL, outputting exactly the format you requested.

Example Output: #

Table   Column  Count
----    ----    ----
users   id      500
users   name    498
users   email   500
users   bio     120
orders  order_id 2000
orders  user_id 1995
orders  notes   300

Quick Notes: #

• If you hit a "group_concat exceeds limit" error, bump up the group_concat_max_len value (the example uses 1MB, adjust based on your database size).
• To exclude system tables, add a filter like AND table_name NOT LIKE 'mysql_%' to the information_schema.columns query.
• Replace DATABASE() with a quoted database name (e.g., 'sales_db') if you want to target a different database than your current session.

内容的提问来源于stack exchange，提问作者Vlad