View the number of records of all tables in the database in mysql

This article will give you a brief introduction to the implementation method of viewing the number of records of all tables in the database in mysql. For more information, see.

This article will give you a brief introduction to the implementation method of viewing the number of records of all tables in the database in mysql. For more information, see.

If you use mysql 5.0 or later, you can query the tables Table in the information_schema database. This table uses table_rows to record the number of rows of the table. For example, to view the number of records of all tables in the database testdb:

The Code is as follows:
Use information_schema; Table_name, table_rows from tables Where TABLE_SCHEMA = 'testdb' Order by table_rows desc;

However, for InnoDB tables, table_rows row counts are only approximate estimates.

Another method is to concatenate an SQL statement using the tables Table of the information_schema database, for example:

The Code is as follows:
Use information_schema; Select concat ( 'Select "', TABLE_name, '", Count (*) from ', TABLE_SCHEMA, '.', TABLE_name, 'Union all' ) From tables Where TABLE_SCHEMA = 'testdb ';

Record the number of records in all tables in mysql:

TABLE_SCHEMA: Name
TABLE_NAME: Table Name

ENGINE: The storage ENGINE used
TABLES_ROWS: number of records

DATA_LENGTH: The data size is measured in bytes, except 1024 for K and 1048576 (= 1024*1024) for M.
INDEX_LENGTH: Index size

The Code is as follows:
Use information_schema; Select table_schema, table_name, table_rows from tables order by table_rows desc;

View the specified size:

The Code is as follows:
SELECT sum (DATA_LENGTH) + sum (INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA = 'database name ';

The result is in bytes. Except 1024 for K, except 1048576 (= 1024*1024) for M.