mysql 查看某数据库中所有表的行数

本文最后更新于(2023-6-16 08:59:12),链接可能失效,内容可能难以复现。请注意甄别。

模糊计数

select table_name,table_rows from information_schema.tables 
where TABLE_SCHEMA = '<datebase_name>' 
order by table_rows desc;

精确计数

1.执行以下语句
select concat(
'select "',
table_name,
'", count(*) from ',
TABLE_SCHEMA,
'.',
table_name,
' union all'
) from information_schema.tables
where TABLE_SCHEMA='<datebase_name>';
2.复制第1步语句的查询结果,粘贴之后将最后一个union all替换为;
select "<table1_name>", count(*) from <datebase_name>.<table1_name> union all
select "<table2_name>", count(*) from <datebase_name>.<table2_name> union all
...
select "<tablen_name>", count(*) from <datebase_name>.<tablen_name>;
3. 复制第2步语句的查询结果,然后运行,查询结果如下:
+-------------+-------------+
| <table1_name>   | count(*)                 |
+-------------+-------------+
| <table1_name>   |      <table1_rows> |
| <table2_name>   |      <table2_rows> |
| ...                   | ...                      |
| <tablen_name>   |      <tablen_rows> |
+--------------------------+----------+
* 拓展:直接统计总数
select sum(sum1) from (
select count(*) as sum1 from <datebase_name>.<table1_name> union all
select count(*) as sum1 from <datebase_name>.<table2_name> union all
...
select count(*) as sum1 from <datebase_name>.<tablen_name>) as total;
查询结果如下:
+--------------------------+
| sum(sum1)        |
+--------------------------+
|<total_rows>|
+--------------------------+