如何在MySQL中统计指定数据库表的行数、列数及大小？

你的查询有几个关键问题导致结果不准确，咱们一步步拆解并修正：

1. 核心问题分析 #

• 缺少GROUP BY子句：你的JOIN会把每个表的每一列都对应成一行，如果不按表名分组，COUNT(t2.table_name)会计算所有表的总列数，而非单表列数；同时table_rows和data_length也会重复显示，无法对应到单个表。
• InnoDB的table_rows是估算值：对于InnoDB引擎的表，INFORMATION_SCHEMA.TABLES中的table_rows是通过统计采样得到的估算值，误差可能很大（尤其是大表），无法得到精确行数。
• 表大小统计不完整：data_length仅包含表的数据长度，未包含索引占用的空间，要获取表的总大小需要加上index_length。

2. 修正后的估算版查询（性能优异） #

如果能接受行数的估算值，这个查询可以快速得到表的基本信息，同时修复了分组和大小统计的问题：

SELECT
  t1.table_name,
  t1.table_rows AS estimated_rows, -- 注：InnoDB下为估算值，MyISAM下为精确值
  COUNT(t2.column_name) AS column_count,
  ROUND((t1.data_length + t1.index_length) / 1024 / 1024, 2) AS total_size_mb -- 转换为MB并保留两位小数
FROM INFORMATION_SCHEMA.TABLES t1
JOIN INFORMATION_SCHEMA.COLUMNS t2 
  ON t1.table_name = t2.table_name AND t1.table_schema = t2.table_schema
WHERE t1.table_schema = 'my_db_name'
GROUP BY t1.table_name, t1.table_rows, t1.data_length, t1.index_length
ORDER BY total_size_mb DESC;

3. 精确行数的统计方案（适合小表或需精准数据场景） #

如果需要精确的行数，必须对每个表执行COUNT(*)，这里提供两种实现方式：

方法1：生成批量查询语句 #

先运行以下SQL生成所有表的COUNT语句，复制后批量执行即可得到精确行数，再和列数、大小的统计结果合并：

SELECT CONCAT('SELECT ''', table_name, ''' AS table_name, COUNT(*) AS exact_rows FROM ', table_name, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'my_db_name';

方法2：存储过程自动统计（适合频繁使用） #

创建一个存储过程自动完成全量统计，并将结果存入临时表方便查看：

DELIMITER //
CREATE PROCEDURE GetTableStats(IN db_name VARCHAR(255))
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tbl_name VARCHAR(255);
  DECLARE cur CURSOR FOR SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = db_name;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  -- 创建临时表存储统计结果
  DROP TEMPORARY TABLE IF EXISTS table_stats;
  CREATE TEMPORARY TABLE table_stats (
    table_name VARCHAR(255) PRIMARY KEY,
    exact_rows BIGINT,
    column_count INT,
    total_size_mb DECIMAL(10,2)
  );

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO tbl_name;
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- 插入精确行数
    SET @sql = CONCAT('INSERT INTO table_stats (table_name, exact_rows) VALUES (''', tbl_name, ''', (SELECT COUNT(*) FROM ', db_name, '.', tbl_name, '));');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 更新列数和总大小
    UPDATE table_stats ts
    JOIN (
      SELECT COUNT(column_name) AS col_count FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = db_name AND table_name = tbl_name
    ) c ON 1=1
    JOIN (
      SELECT ROUND((data_length + index_length)/1024/1024, 2) AS size_mb FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = db_name AND table_name = tbl_name
    ) s ON 1=1
    SET ts.column_count = c.col_count, ts.total_size_mb = s.size_mb
    WHERE ts.table_name = tbl_name;
  END LOOP;
  CLOSE cur;

  -- 输出最终统计结果
  SELECT * FROM table_stats ORDER BY total_size_mb DESC;
END //
DELIMITER ;

-- 调用存储过程，替换为你的数据库名
CALL GetTableStats('my_db_name');

注意事项 #

• 精确统计COUNT(*)对大表来说会很慢，因为InnoDB需要扫描全表（除非有覆盖索引），建议仅在必要时使用。
• 如果是MyISAM引擎，table_rows是精确值，因为MyISAM会主动维护行数计数器，此时使用估算版查询即可获得准确结果。

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