是否有一种方法可以获得MySQL数据库中所有表的行计数,而无需在每个表上运行SELECT count() ?
当前回答
SELECT SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{your_db}';
从文档中注意到:对于InnoDB表,行数只是用于SQL优化的粗略估计。您需要使用COUNT(*)来获得精确的计数(成本更高)。
其他回答
这个存储过程列出表,统计记录,并在最后生成记录的总数。
添加此过程后运行:
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
像@Venkatramanan和其他人一样,我找到了INFORMATION_SCHEMA。TABLES不可靠(使用InnoDB, MySQL 5.1.44),每次运行时给出不同的行数,即使是在静态表上。这里有一种生成大型SQL语句的相对hack(但是灵活/适应性强)的方法,您可以将其粘贴到新的查询中,而不需要安装Ruby宝石之类的东西。
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)
大多数其他答案建议使用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
请注意,这仍然是一个近似值,像以前一样,不是一个确切的数字。
下面的代码为所有故事生成选择查询。只需删除最后的“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'
海报想要行计数,但没有指定哪个表引擎。对于InnoDB,我只知道一种方法,那就是计数。
我是这样摘土豆的:
# Put this function in your bash and call with:
# rowpicker DBUSER DBPASS DBNAME [TABLEPATTERN]
function rowpicker() {
UN=$1
PW=$2
DB=$3
if [ ! -z "$4" ]; then
PAT="LIKE '$4'"
tot=-2
else
PAT=""
tot=-1
fi
for t in `mysql -u "$UN" -p"$PW" "$DB" -e "SHOW TABLES $PAT"`;do
if [ $tot -lt 0 ]; then
echo "Skipping $t";
let "tot += 1";
else
c=`mysql -u "$UN" -p"$PW" "$DB" -e "SELECT count(*) FROM $t"`;
c=`echo $c | cut -d " " -f 2`;
echo "$t: $c";
let "tot += c";
fi;
done;
echo "total rows: $tot"
}
我对此没有任何断言,只是说这是一种非常丑陋但有效的方法,可以获得数据库中每个表中存在多少行,而不需要使用表引擎,也不需要拥有安装存储过程的权限,也不需要安装ruby或php。是的,生锈了。是的,这很重要。Count(*)是准确的。
推荐文章
- 如何关闭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