是否有一种方法可以获得MySQL数据库中所有表的行计数,而无需在每个表上运行SELECT count() ?
当前回答
像许多其他人一样,我很难用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
其他回答
如果你使用数据库information_schema,你可以使用下面的mysql代码(where部分使查询不显示行为空值的表):
SELECT TABLE_NAME, TABLE_ROWS
FROM `TABLES`
WHERE `TABLE_ROWS` >=0
SELECT TABLE_NAME,SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db'
GROUP BY TABLE_NAME;
这就是你所需要的。
下面的代码为所有故事生成选择查询。只需删除最后的“UNION ALL”选择所有结果,并粘贴一个新的查询窗口运行。
SELECT
concat('select ''', table_name ,''' as TableName, COUNT(*) as RowCount from ' , table_name , ' UNION ALL ') as TR FROM
information_schema.tables where
table_schema = 'Database Name'
这个存储过程列出表,统计记录,并在最后生成记录的总数。
添加此过程后运行:
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
基于上面@Nathan的回答,但不需要“删除最终的联合”,并带有对输出进行排序的选项,我使用以下SQL。它生成另一个SQL语句,然后运行:
select CONCAT( 'select * from (\n', group_concat( single_select SEPARATOR ' UNION\n'), '\n ) Q order by Q.exact_row_count desc') as sql_query
from (
SELECT CONCAT(
'SELECT "',
table_name,
'" AS table_name, COUNT(1) AS exact_row_count
FROM `',
table_schema,
'`.`',
table_name,
'`'
) as single_select
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'YOUR_SCHEMA_NAME'
and table_type = 'BASE TABLE'
) Q
您确实需要一个足够大的group_concat_max_len服务器变量的值,但是从MariaDb 10.2.4开始,它应该默认为1M。
推荐文章
- 如何关闭mysql密码验证?
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- MySQL区分大小写查询
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- MySQL数据库表中的最大记录数
- 原则-如何打印出真正的sql,而不仅仅是准备好的语句?
- PHP/MySQL插入一行然后获取id
- 我如何循环通过一组记录在SQL Server?
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N