MySQL批量统计数据库中的数据量

一、查询一个数据库中每张表的数据量：

1、执行下面的SQL语句，是为了拼接一个查询语句

select concat(
    'select "',
    TABLE_name,
    '", count(*) from ',
    TABLE_SCHEMA,
    '.',
    TABLE_name,
    ' union all'
) from information_schema.tables
where TABLE_SCHEMA='数据库名称';

返回这样的结果：

2、将返回的结果复制出来，去掉最后一个 union all ，如下图所示，再运行该SQL语句

select "order", count(*) from ms.order union all
select "person", count(*) from ms.person union all
select "table4", count(*) from ms.table4 union all
select "test01", count(*) from ms.test01 union all
select "zb", count(*) from ms.zb union all
select "zh_b", count(*) from ms.zh_b union all
select "zh_c", count(*) from ms.zh_c

就可以得到下面的结果，这个数据库里每张表的数据量

二、查询一个数据库数据的总量：

1、执行下面的SQL语句，是为了拼接一个查询语句

select 'select sum(count) as total_rows from (' union all
select concat(
    'select "',
    TABLE_name,
    '", count(*) as count from ',
    TABLE_SCHEMA,
    '.',
    TABLE_name,
    ' union all'
) from information_schema.tables
where TABLE_SCHEMA='数据库名称' union all
select ') as total'

返回这样的结果：

2、将返回的结果复制出来，去掉最后一个 union all ，如下图所示，再运行该SQL语句

select sum(count) as total_rows from (
select "order", count(*) as count from ms.order union all
select "person", count(*) as count from ms.person union all
select "table4", count(*) as count from ms.table4 union all
select "test01", count(*) as count from ms.test01 union all
select "zb", count(*) as count from ms.zb union all
select "zh_b", count(*) as count from ms.zh_b union all
select "zh_c", count(*) as count from ms.zh_c
) as total

就可以得到下面的结果，这个数据库里所有表的数据量