查询以计算我在 MySQL 中的表数

Query to count the number of tables I have in MySQL

提问by Genadinik

I am growing the number of tables I have and I am sometimes curious just to do a quick command line query to count the number of tables in my database. Is that possible? If so, what is the query?

我正在增加我拥有的表的数量，有时我很好奇只是为了执行快速命令行查询来计算我的数据库中的表数量。那可能吗？如果是这样，查询是什么？

回答by Joseadrian

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'dbName';

Source

来源

This is mine:

这是我的：

USE databasename; 
SHOW TABLES; 
SELECT FOUND_ROWS();

回答by RolandoMySQLDBA

In case you would like a count all the databases plus a summary, please try this:

如果您想计算所有数据库和摘要，请尝试以下操作：

SELECT IFNULL(table_schema,'Total') "Database",TableCount 
FROM (SELECT COUNT(1) TableCount,table_schema 
      FROM information_schema.tables 
      WHERE table_schema NOT IN ('information_schema','mysql') 
      GROUP BY table_schema WITH ROLLUP) A;

Here is a sample run:

这是一个示例运行：

mysql> SELECT IFNULL(table_schema,'Total') "Database",TableCount
    -> FROM (SELECT COUNT(1) TableCount,table_schema
    ->       FROM information_schema.tables
    ->       WHERE table_schema NOT IN ('information_schema','mysql')
    ->       GROUP BY table_schema WITH ROLLUP) A;
+--------------------+------------+
| Database           | TableCount |
+--------------------+------------+
| performance_schema |         17 |
| Total              |         17 |
+--------------------+------------+
2 rows in set (0.29 sec)

Give it a Try !!!

试一试 ！！！

回答by Mukund

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'dbo' and TABLE_TYPE='BASE TABLE'

回答by Rohit Dubey

This will give you names and table count of all the databases in you mysql

这将为您提供 mysql 中所有数据库的名称和表数

SELECT TABLE_SCHEMA,COUNT(*) FROM information_schema.tables group by TABLE_SCHEMA;

回答by Arun Kasyakar

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'database_name';

回答by jmm

To count number of tables just do this:

要计算表的数量，只需执行以下操作：

USE your_db_name;    -- set database
SHOW TABLES;         -- tables lists
SELECT FOUND_ROWS(); -- number of tables

Sometimes easy things will do the work.

有时，简单的事情就可以解决问题。

回答by automatix

There may be multiple ways to count the tables of a database. My favorite is this on:

可能有多种方法来计算数据库的表。我最喜欢的是这个：

SELECT
    COUNT(*)
FROM
    `information_schema`.`tables`
WHERE
    `table_schema` = 'my_database_name'
;

回答by Swamy

select name, count(*) from DBS, TBLS 
where DBS.DB_ID = TBLS.DB_ID 
group by NAME into outfile '/tmp/QueryOut1.csv' 
fields terminated by ',' lines terminated by '\n';

回答by Rajiv Singh

from command line :

从命令行：

mysql -uroot -proot  -e "select count(*) from 
information_schema.tables where table_schema = 'database_name';"

in above example root is username and password , hosted on localhost.

在上面的例子中 root 是 username 和 password ，托管在本地主机上。

回答by Mohammed Nosirat

mysql> show tables;

mysql> 显示表；

it will show the names of the tables, then the count on tables.

它将显示表的名称，然后是表的计数。

source

来源