The number of records in MySQL that view all the tables in the database

If you are using a MySQL version of 5.0 or more, you can get it by querying the tables in the INFORMATION_SCHEMA library, which uses table_rows to record the number of rows in the table. For example, to view the number of records for all tables in the library TestDB:

The code is as follows	Copy Code
Use INFORMATION_SCHEMA; Select Table_name,table_rows from tables where Table_schema = ' TestDB ' ORDER BY table_rows Desc;

It should be noted, however, that for InnoDB tables, the Table_rows row count is only a ballpark estimate.

Another way is to use the tables of the INFORMATION_SCHEMA library to stitch up a single SQL statement, such as:

code is as follows	copy code
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 MySQL that view all tables:

Table_schema: Database name
TABLE_NAME: Table name

ENGINE: the storage engine used
Tables_rows: Number of records

Data_length: The result of data size is in bytes, except 1024 is K, except 1048576 (=1024*1024) is M
Index_length: Index size

The code is as follows	Copy Code
Use INFORMATION_SCHEMA; Select Table_schema,table_name,table_rows from the tables order by table_rows Desc;

To view the specified database size:

The code is as follows	Copy Code
SELECT sum (data_length) +sum (index_length) from INFORMATION_SCHEMA. TABLES where Table_schema= ' database name ';

The resulting result is in bytes, except that 1024 is K, except 1048576 (=1024*1024) is M