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

模糊计数

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>|
+--------------------------+