mysql – 计算每个表的行数(从子查询返回表名)

我试过了

select count(*) from (select tablename from main_table) as t;

但它只返回main_table中的条目数,但不返回每个表中条目的数量.
我可以使用系统表来获取rowcount,但我不想要所有表但只需要特定的表,并且可能需要特定查询的行数.

Algo就是这样的

for tablenames in main_table where id>3:
    select count(*) from tablename where constraints    

SELECT  Table_Name, table_rows
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = 'BASE TABLE'
AND     TABLE_SCHEMA = 'YourDatabase';

或者您需要使用UNION ALL

SELECT  'T1' AS TableName, COUNT(*) AS Rows
FROM    T1
UNION ALL
SELECT  'T2' AS TableName, COUNT(*) AS Rows
FROM    T2
UNION ALL
SELECT  'T3' AS TableName, COUNT(*) AS Rows
FROM    T3;

如果需要动态完成,那么您可以使用动态SQL：

SET @SQL = (SELECT GROUP_CONCAT('SELECT ''', 
                                TableName, 
                                ''' AS TableName, COUNT(*) AS Rows FROM ', 
                                TableName SEPARATOR ' UNION ALL ')
            FROM MainTable
            --WHERE Some condition to limit tables
            );

PREPARE stmt FROM @SQL;
EXECUTE stmt;

Example on SQL Fiddle

这实际上产生与UNION ALL解决方案相同的SQL,但是根据主表的内容创建SQL.