blocks|key|4519713|text|如果您有权限，可以从information_schema.tables中选择count(*)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4519714|select+count(*)+from+information_schema.tables
where+table_schema+=+<My+Schema>
and+table_type+=+'BASE+TABLE';|code-block|syntax|javascript|4519715|并根据table_rows列筛选空的yables：|4519716|+select+count(*)+from+information_schema.tables
+where+table_schema+=+<My+Schema>
+and+table_type+=+'BASE+TABLE'
+and+table_rows+=+0;|4519717|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can select count(*) from information_schema.tables if you have priviliges on it.

<pre><code>select count(*) from information_schema.tables
where table_schema = &lt;My Schema&gt;
and table_type = 'BASE TABLE';
</code></pre>

and filter on table_rows column for empty yables:

<pre><code> select count(*) from information_schema.tables
 where table_schema = &lt;My Schema&gt;
 and table_type = 'BASE TABLE'
 and table_rows = 0;
</code></pre>

blocks|key|1516804|text|SHOW+DATABASES;
SHOW+TABLES+FROM+--your_db_here--;
SELECT+COUNT(*)+=+0+FROM+--your_table_here--;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1516805|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SHOW DATABASES;
SHOW TABLES FROM --your_db_here--;
SELECT COUNT(*) = 0 FROM --your_table_here--;
</code></pre>

blocks|key|1303144|text|有一个特殊的数据库，information_schema，它包含关于mysql服务器上所有数据库的元数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1303145|SELECT+`TABLE_NAME`,+`TABLE_ROWS`+
FROM+`information_schema`.`TABLES`+
WHERE+`TABLE_SCHEMA`+=+'NameOfDatabaseYouAreInterestedIn'
AND+`TABLE_TYPE`+=+'BASE+TABLE'|code-block|syntax|javascript|1303146|TABLE_ROWS并不总是完全准确的，您可能希望遍历这些表并获取计数|1303147|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

there is a special database, information_schema which contains meta data about all databases on the mysql server.

<pre><code>SELECT `TABLE_NAME`, `TABLE_ROWS` 
FROM `information_schema`.`TABLES` 
WHERE `TABLE_SCHEMA` = 'NameOfDatabaseYouAreInterestedIn'
AND `TABLE_TYPE` = 'BASE TABLE'
</code></pre>

TABLE_ROWS is not always completely accurate, you may want to loop through the tables and get a count

blocks|key|215149|text|您可以使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|215150|SHOW+TABLES;|code-block|syntax|javascript|215151|获取数据库中的表，然后计算返回的行数。|215152|您也可以这样做(尽管它可能非常慢)：|215153|+SELECT+COUNT(*)+FROM+information_schema.tables+WHERE+table_schema+=+'my_database';|215154|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You can use

<pre><code>SHOW TABLES;
</code></pre>

To get the tables in a database and then count the rows returned.

You can also do this (though it can be very slow):

<pre><code> SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'my_database';
</code></pre>

blocks|key|215223|text|我不知道这是否有帮助，但是mysqlshow有一个选项可以显示每个表的行数(--count)：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|215224|$mysqlshow+--count++*p*
Wildcard:+%25p%25
%2B-------------%2B--------%2B--------------%2B
%7C++Databases++%7C+Tables+%7C++Total+Rows++%7C
%2B-------------%2B--------%2B--------------%2B
%7C+implantacao+%7C+++++25+%7C++++++++++134+%7C
%7C+pmsp++++++++%7C+++++80+%7C++++++8561947+%7C
%7C+tmp+++++++++%7C++++++7+%7C++++++++++++5+%7C
%2B-------------%2B--------%2B--------------%2B
3+rows+in+set.|code-block|syntax|javascript|215225|如果您传递数据库名称：|215226|$mysqlshow+--count+tmp
Database:+tmp
%2B------------%2B----------%2B------------%2B
%7C+++Tables+++%7C+Columns++%7C+Total+Rows+%7C
%2B------------%2B----------%2B------------%2B
%7C+builds+++++%7C++++++++2+%7C++++++++++0+%7C
%7C+gtable+++++%7C++++++++2+%7C++++++++++5+%7C
%7C+patterns+++%7C++++++++9+%7C++++++++++0+%7C
%7C+products+++%7C++++++++2+%7C++++++++++0+%7C
%7C+sig_types++%7C++++++++2+%7C++++++++++0+%7C
%7C+signatures+%7C++++++++2+%7C++++++++++0+%7C
%7C+versions+++%7C++++++++2+%7C++++++++++0+%7C
%2B------------%2B----------%2B------------%2B
7+rows+in+set.|215227|您可以添加表名并获取其相关信息：|215228|$mysqlshow+tmp+gtable
Database:+tmp++Table:+gtable
%2B--------%2B---------%2B-------------------%2B------%2B-----%2B---------%2B-------%2B---------------------------------%2B---------%2B
%7C+Field++%7C+Type++++%7C+Collation+++++++++%7C+Null+%7C+Key+%7C+Default+%7C+Extra+%7C+Privileges++++++++++++++++++++++%7C+Comment+%7C
%2B--------%2B---------%2B-------------------%2B------%2B-----%2B---------%2B-------%2B---------------------------------%2B---------%2B
%7C+symbol+%7C+text++++%7C+latin1_swedish_ci+%7C+YES++%7C+++++%7C+++++++++%7C+++++++%7C+select,insert,update,references+%7C+++++++++%7C
%7C+size+++%7C+int(11)+%7C+++++++++++++++++++%7C+YES++%7C+++++%7C+++++++++%7C+++++++%7C+select,insert,update,references+%7C+++++++++%7C
%2B--------%2B---------%2B-------------------%2B------%2B-----%2B---------%2B-------%2B---------------------------------%2B---------%2B|215229|有关更多选项/信息，请查看mysqlshow+--help。|215230|entityMap^0|D|9|12|7|0|0|0|0|0|0|D|G|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|13|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|14|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|15|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|16|8|@$9|17|A|18|B|C]]|D|@]|E|$]]|$1|U|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|V|$]]

I dont know if this helps but there is a option to <code>mysqlshow</code> to show number of rows per table (<code>--count</code>):

<pre><code>$mysqlshow --count *p*
Wildcard: %p%
+-------------+--------+--------------+
| Databases | Tables | Total Rows |
+-------------+--------+--------------+
| implantacao | 25 | 134 |
| pmsp | 80 | 8561947 |
| tmp | 7 | 5 |
+-------------+--------+--------------+
3 rows in set.
</code></pre>

And if you pass the database name:

<pre><code>$mysqlshow --count tmp
Database: tmp
+------------+----------+------------+
| Tables | Columns | Total Rows |
+------------+----------+------------+
| builds | 2 | 0 |
| gtable | 2 | 5 |
| patterns | 9 | 0 |
| products | 2 | 0 |
| sig_types | 2 | 0 |
| signatures | 2 | 0 |
| versions | 2 | 0 |
+------------+----------+------------+
7 rows in set.
</code></pre>

You can add the tablename and get info about it:

<pre><code>$mysqlshow tmp gtable
Database: tmp Table: gtable
+--------+---------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------+---------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| symbol | text | latin1_swedish_ci | YES | | | | select,insert,update,references | |
| size | int(11) | | YES | | | | select,insert,update,references | |
+--------+---------+-------------------+------+-----+---------+-------+---------------------------------+---------+
</code></pre>

Look at <code>mysqlshow --help</code> for more options/information.

Following command will let me know the names of databases.

$ mysqlshow 

But how do I know the number of tables in each database and also the number of empty tables?
for e.g.

db count empty

test 10 5

mydb 122 0

client 34 34

mysqlshow with the number of tables

33 天实现自己的 AI 进化论？发文瓜分机械键盘、耳机等万元奖池！点击参加活动：<a href="https://cloud.tencent.com/developer/article/2503022" target="_blank">https://cloud.tencent.com/developer/article/2503022</a> 
 <img src="https://qcloudimg.tencent-cloud.cn/raw/01543e0407db10898a7ee9f420794432.jpg"/>

技术创作特训营有奖征文

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云AI代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

文章&问答评论现已支持表情

下面的命令会让我知道数据库的名称。$ mysqlshow但是我如何知道每个数据库中的表数以及空表的数目呢？例如数据库计数为空测试10 5mydb 122 %0客户端34 34

问mysqlshow包含表的数量
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysqlshow包含表的数量EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问mysqlshow包含表的数量
EN