MYSQL - 计算每个表中的行数

I would like to know how many rows are in each table in my database. I've come so far as to having

我想知道我的数据库中每个表中有多少行。我到目前为止已经到了

select count(*) _tablename_;

However i would need to do that on each and every table - and there are a lot. What would me the best way to get a print-out with the table name and it's row count?

但是我需要在每张桌子上都这样做 - 而且还有很多。使用表名和行数计算打印输出的最佳方法是什么？

2 个解决方案

#1

SELECT table_name, table_rows
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = '<your db>';

I also hope you realise there's an error in your query: it's missing a FROM.

我也希望你意识到你的查询中有一个错误：它缺少一个FROM。

#2

This following query will return number of rows in each table but it doesn't seem to return exact value all the time

以下查询将返回每个表中的行数，但它似乎不会始终返回精确值

SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '<your db>';

TABLE_ROWS: The number of table rows in the partition. For partitioned InnoDB tables, the row count given in the TABLE_ROWS column is only an estimated value used in SQL optimization, and may not always be exact... for more https://dev.mysql.com/doc/mysql-infoschema-excerpt/5.5/en/partitions-table.html

TABLE_ROWS：分区中的表行数。对于分区的InnoDB表，TABLE_ROWS列中给出的行计数只是SQL优化中使用的估计值，并且可能并不总是准确...更多https://dev.mysql.com/doc/mysql-infoschema-摘录/ 5.5 / EN /分区，table.html

#1

SELECT table_name, table_rows
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = '<your db>';