一、查询一个数据库中每张表的数据量:
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 "paylog", count(*) from test.paylog union all select "stat_alliance", count(*) from test.stat_alliance
二、查询一个数据库数据的总量:
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
"paylog" ,
count(*) AS count
FROM
test.paylog
UNION ALL
SELECT
"stat_alliance" ,
count(*) AS count
FROM
test.stat_alliance
) AS total