在mysql中查找数据库中具有最大行数的表 [英] Find Table with maximum number of rows in a database in mysql

问题描述

正如问题标题所暗示的，我想在特定数据库中找到具有最大行数(条目)的表.我已经能够使用下面的查询提取特定数据库中所有表的名称.

As the question title suggests, I want to find the table having maximum number of rows (entries) in a particular database. I have been able to extract the names of all the tables in a particular database using the query below.

SELECT TABLE_NAME
FROM information_schema.tables
WHERE TABLE_SCHEMA="Some_Database";

我该如何处理?我一直试图为上述目的制定一个嵌套查询，但无法想出一些东西(我对它们不太满意).请帮忙.

How do I proceed beyond this?? I have been trying to formulate a nested query for the above purpose but couldn't come up with something (I am not very comfortable with them). Please Help.

正如在这个 link 中给出的，table_rows 字段没有给出准确的结果.这就是为什么我需要为每个表做一个 MAX (Count(*)) 之类的事情.

As given in this link the table_rows field does not give an accurate result. That is why I need to do something like a MAX (Count(*)) for each table.

推荐答案

试试这个......

SELECT TABLE_NAME,MAX(TABLE_ROWS)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "DB_Name";

或

请尝试以下两个查询以获得实际结果.

please try the following two queries for actual result.

查询 1:

SELECT CONCAT('SELECT COUNT(*) as cnt FROM ', table_name, ' union all') 
      FROM information_schema.tables WHERE table_schema = 'your_db_name';

查询 2:

select max(cnt) from (paste the result of first query and remove 
last union all keyword) as tmptable;

这篇关于在mysql中查找数据库中具有最大行数的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！