获取MySQL数据库中所有表的记录计数

有没有办法在不运行SELECT count()每个表的情况下获取MySQL数据库中所有表中的行数？

1> Hates_..：

---

SELECT SUM(TABLE_ROWS) 
     FROM INFORMATION_SCHEMA.TABLES 
     WHERE TABLE_SCHEMA = '{your_db}';

但是请注意文档:对于InnoDB表,行计数只是 SQL优化中使用的粗略估计.您需要使用COUNT(*)来获得准确的计数(这更昂贵).

---

或者,如果你想要每个表:SELECT table_name,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='{your_db}';

---

有没有其他方法来获取table_row和table_name？因为我想要精确的结果而不是粗略估计.谢谢.

---

@krunalshah,这是InnoDB的限制之一.有关详细信息,请参阅http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html,InnoDB表的限制部分.你总是可以使用SELECT COUNT(*)FROM t,然而,它要慢得多

---

Jaitsu,不,不是.count(*)(或者更实际的是count(id))是mysql用它来计算它的行​​不是吗？在任何情况下,我只是测试它并为count()调用获得更大的数字,无论值得多少.

---

SELECT TABLE_NAME,SUM(TABLE_ROWS)N FROM INFORMATION_SCHEMA.TABLES TABLE_NAME的TABLE_SCHEMA ='{your_db}'组;

2> gpojd..：

---

你可以把一些东西和Tables表放在一起.我从来没有这样做过,但看起来它有一个TABLE_ROWS列和一个TABLE NAME列.

要获取每个表的行,您可以使用如下查询:

SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '**YOUR SCHEMA**';

---

有没有其他方法来获取table_row和table_name？因为我想要精确的结果而不是粗略估计.谢谢.

3> Nathan..：

---

像@Venkatramanan和其他人一样,我发现INFORMATION_SCHEMA.TABLES不可靠(使用InnoDB,MySQL 5.1.44),每次运行它时都会提供不同的行数,即使在静默表上也是如此.这是一种相对hacky(但灵活/适应性)的生成大型SQL语句的方法,您可以将其粘贴到新查询中,而无需安装Ruby gems和东西.

SELECT CONCAT(
    'SELECT "', 
    table_name, 
    '" AS table_name, COUNT(*) AS exact_row_count FROM `', 
    table_schema,
    '`.`',
    table_name, 
    '` UNION '
) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_schema = '**my_schema**';

它产生如下输出:

SELECT "func" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.func UNION                         
SELECT "general_log" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.general_log UNION           
SELECT "help_category" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_category UNION       
SELECT "help_keyword" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_keyword UNION         
SELECT "help_relation" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_relation UNION       
SELECT "help_topic" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_topic UNION             
SELECT "host" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.host UNION                         
SELECT "ndb_binlog_index" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.ndb_binlog_index UNION 

复制并粘贴除了最后一个UNION以获得良好的输出,如,

+------------------+-----------------+
| table_name       | exact_row_count |
+------------------+-----------------+
| func             |               0 |
| general_log      |               0 |
| help_category    |              37 |
| help_keyword     |             450 |
| help_relation    |             990 |
| help_topic       |             504 |
| host             |               0 |
| ndb_binlog_index |               0 |
+------------------+-----------------+
8 rows in set (0.01 sec)

---

谢谢,我希望我不必安装任何插件/宝石来获得准确的计数.

4> djburdick..：

---

我跑了:

show table status;

这将为您提供每个表的行数以及一堆其他信息.我曾经使用上面选择的答案,但这更容易.

我不确定这是否适用于所有版本,但我使用的是InnoDB引擎.

---

不幸的是,如果你使用InnoDB,这种方法会遇到与上述其他方法相同的不准确性.例如,我有一个大约65,000行的InnoDB表,但这里的这些方法报告它有350,000到780,000以上的任何值.

5> 小智..：

---

 SELECT TABLE_NAME,SUM(TABLE_ROWS) 
 FROM INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_SCHEMA = 'your_db' 
 GROUP BY TABLE_NAME;

这就是你所需要的.

6> 小智..：

---

此存储过程列出表,计算记录,并在最后生成记录总数.

要在添加此过程后运行它:

CALL `COUNT_ALL_RECORDS_BY_TABLE` ();

-

程序,流程:

DELIMITER $$

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `COUNT_ALL_RECORDS_BY_TABLE`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE TNAME CHAR(255);

DECLARE table_names CURSOR for 
    SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN table_names;   

DROP TABLE IF EXISTS TCOUNTS;
CREATE TEMPORARY TABLE TCOUNTS 
  (
    TABLE_NAME CHAR(255),
    RECORD_COUNT INT
  ) ENGINE = MEMORY; 


WHILE done = 0 DO

  FETCH NEXT FROM table_names INTO TNAME;

   IF done = 0 THEN
    SET @SQL_TXT = CONCAT("INSERT INTO TCOUNTS(SELECT '" , TNAME  , "' AS TABLE_NAME, COUNT(*) AS RECORD_COUNT FROM ", TNAME, ")");

    PREPARE stmt_name FROM @SQL_TXT;
    EXECUTE stmt_name;
    DEALLOCATE PREPARE stmt_name;  
  END IF;

END WHILE;

CLOSE table_names;

SELECT * FROM TCOUNTS;

SELECT SUM(RECORD_COUNT) AS TOTAL_DATABASE_RECORD_CT FROM TCOUNTS;

END