@yuezc:在写这篇笔记之前,一直以为数据库就那样,不就一个查询总行数么,难吗?应该很简单。但是在深入了解之后,让我脑门大开。对,就这感觉。
1、获取单个表的MySQL行计数
SELECT
COUNT(*)
FROM
t_sdrs_xsjbxx;
2、获取MySQL两个或多个表的行计数
SELECT
'xsjbxx' tableName,
COUNT(*)
FROM
t_sdrs_xsjbxx
UNION
SELECT
'xsxj' tableName,
COUNT(*)
FROM t_sdrs_xsxj;
3、获取特定数据库中所有表的MySQL行计数
SELECT
table_name
FROM
information_schema.tables
WHERE
table_schema = 'sdrs_xxxx'
AND table_type = 'BASE TABLE';
或者
SELECT
-- CONCAT(
GROUP_CONCAT(
CONCAT(
'SELECT \'',table_name, '\' table_name,COUNT(*) rows FROM ',
table_name
) SEPARATOR ' UNION '
) 'ORDER BY table_name'
-- )
INTO @SQL
FROM
(
SELECT
table_name
FROM
information_schema. TABLES
WHERE
table_schema = 'sdrs' //库名
AND table_type = 'BASE TABLE'
) table_list;
USE sdrs; //库名
PREPARE st
FROM @SQL;
EXECUTE st;
DEALLOCATE PREPARE st;