是否有一种方法可以获得MySQL数据库中所有表的行计数,而无需在每个表上运行SELECT count() ?
当前回答
大多数其他答案建议使用INFORMATION_SCHEMA。但是在MySQL 8中它已经不存在了。行数已移动到INFORMATION_SCHEMA.INNODB_TABLESTATS。
你可以用以下方法查询:
SELECT *
FROM information_schema.INNODB_TABLESTATS
WHERE NAME LIKE "YOUR_DB_NAME/%"
ORDER BY NUM_ROWS DESC
请注意,这仍然是一个近似值,像以前一样,不是一个确切的数字。
其他回答
SELECT TABLE_NAME,SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db'
GROUP BY TABLE_NAME;
这就是你所需要的。
像许多其他人一样,我很难用InnoDB在INFORMATION_SCHEMA表上获得准确的值,并且能够通过count()进行查询将无限受益,并且希望在一次查询中完成它。
首先,确保启用大规模group_concats:
SET SESSION group_concat_max_len = 1000000;
然后运行此查询以获得将为数据库运行的结果查询。
SELECT CONCAT('SELECT ', GROUP_CONCAT(table1.count SEPARATOR ',\n')) FROM (
SELECT concat('(SELECT count(id) AS \'',table_name,' Count\' ','FROM ',table_name,') AS ',table_name,'_Count') AS 'count'
FROM information_schema.tables
WHERE table_schema = '**YOUR_DATABASE_HERE**'
) AS table1
这将生成诸如…
SELECT (SELECT count(id) AS 'table1 Count' FROM table1) AS table1_Count,
(SELECT count(id) AS 'table2 Count' FROM table2) AS table2_Count,
(SELECT count(id) AS 'table3 Count' FROM table3) AS table3_Count;
这反过来又产生了以下结果:
*************************** 1. row ***************************
table1_Count: 1
table2_Count: 1
table3_Count: 0
还有一个选择:对于非InnoDB,它使用information_schema中的数据。TABLES(因为它更快),对于InnoDB -选择count(*)来获得准确的计数。它还会忽略视图。
SET @table_schema = DATABASE();
-- or SET @table_schema = 'my_db_name';
SET GROUP_CONCAT_MAX_LEN=131072;
SET @selects = NULL;
SELECT GROUP_CONCAT(
'SELECT "', table_name,'" as TABLE_NAME, COUNT(*) as TABLE_ROWS FROM `', table_name, '`'
SEPARATOR '\nUNION\n') INTO @selects
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = @table_schema
AND ENGINE = 'InnoDB'
AND TABLE_TYPE = "BASE TABLE";
SELECT CONCAT_WS('\nUNION\n',
CONCAT('SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND ENGINE <> "InnoDB" AND TABLE_TYPE = "BASE TABLE"'),
@selects) INTO @selects;
PREPARE stmt FROM @selects;
EXECUTE stmt USING @table_schema;
DEALLOCATE PREPARE stmt;
如果你的数据库有很多大的InnoDB表,计算所有行会花费更多的时间。
简单的方法:
SELECT
TABLE_NAME, SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{Your_DB}'
GROUP BY TABLE_NAME;
结果示例:
+----------------+-----------------+
| TABLE_NAME | SUM(TABLE_ROWS) |
+----------------+-----------------+
| calls | 7533 |
| courses | 179 |
| course_modules | 298 |
| departments | 58 |
| faculties | 236 |
| modules | 169 |
| searches | 25423 |
| sections | 532 |
| universities | 57 |
| users | 10293 |
+----------------+-----------------+
这个存储过程列出表,统计记录,并在最后生成记录的总数。
添加此过程后运行:
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
推荐文章
- 将值从同一表中的一列复制到另一列
- GROUP BY with MAX(DATE)
- 删除id与其他表不匹配的sql行
- 等价的限制和偏移SQL Server?
- MySQL CPU使用率高
- INT和VARCHAR主键之间有真正的性能差异吗?
- 拒绝访问;您需要(至少一个)SUPER特权来执行此操作
- 为什么我不能在DELETE语句中使用别名?
- 在SQL Server Management Studio中保存带有标题的结果
- 从存储引擎得到错误28
- "where 1=1"语句
- 如何选择一个记录和更新它,与一个单一的查询集在Django?
- 多语句表值函数vs内联表值函数
- 如何从Oracle的表中获取列名?
- 可能做MySQL外键的两个可能的表之一?