The MySQL metabase uses Information_schema.tables to query the database and data table information

Overview for databases such as MySQL and Infobright, the tables in the INFORMATION_SCHEMA database are read-only and cannot be updated, deleted, inserted, or triggered because they are actually just a view, not a base table, and have no associated files. Information_schema.tablesThe metadata information for the data table is stored, and the fields that are commonly used are described below:

• Table_schema: Record database name ;
• TABLE_NAME: Record data table name ;
• Engine: Storage engines ;
• Table_rows: A rough line estimate of the table;
• Data_length: the size of the record table (in bytes);
• Index_length: The size of the index of the record table;
• Row_format: You can see if the data table has been compressed ;

Here are a few common uses; Information_schema.tables information;

 
   
  

  
    
   
use information_schema;
  
    
   
show create table tables;
 
   
  

 
   
  

  
    
   
desc tables;
 
   
  

Querying all of the database information

 
   
  

  
    
   
select distinct TABLE_SCHEMA from tables ;
 
   
  

Querying the database and data table information displays all table information below the MySQL database: (in common use)

 
   
  

  
    
   
use mysql;
  
    
   
show tables;
 
   
  

Get database and data table information through Information_schema.table:

 
   
  

  
    
   
use information_schema;
  
    
   
select TABLE_SCHEMA ,table_name from tables where table_schema like ‘mysql‘;
 
   
  

Data table size and index sizeExample 1:Mysql.time_zone Related tables
Gets the size of the Time_zone related table:

 
   
  

  
    
   
select (sum(DATA_LENGTH) + sum(INDEX_LENGTH)) as size from tables where table_schema=‘mysql‘ and table_name like ‘time_%‘;
 
   
  

Example 2: Get the size of the specified database;

 
   
  

  
    
   
select (sum(DATA_LENGTH) + sum(INDEX_LENGTH)) as size from tables where table_schema=‘mysql‘;
 
   
  

Determine if the MyISAM data table is compressed

 
   
  

  
    
   
select distinct row_format,engine from information_schema.tables where engine=‘myisam‘;
 
   
  

• Fixed: Indicates compressed;
  
• Dynamic: Indicates uncompressed;

 
   
  

  
    
   
select row_format,engine,table_name from information_schema.tables where engine=‘myisam‘;
 
   
  

get database and data table information directly from Linux instructions:

 
   
  

  
    
   
mysql -uroot -pxxxx -D information_schema -e "select TABLE_SCHEMA ,table_name from tables where table_schema like ‘hsm_syslog_%‘"
 
   
  

Parameter description:

• 
  
• -D: Indicates the name of the database;
  
• -E: Indicates the command to be executed:;
• 
  

From for notes (Wiz)

The MySQL metabase uses Information_schema.tables to query the database and data table information