MySQL information_schema.tables

 

 

 

table_stype 为 enum类型取值

1.  BASE TABLE
2. VIEW
3. SYSTEM VIEW

 

用法:

1. 某个schema是否存在某个table
   

   select 
       table_schema,
       table_name,
       table_type,
       table_rows
   from
       information_schema.tables
   where
       table_schema='information_schema'
       and table_type='SYSTEM VIEW'
       and table_name='INNODB_TABLESPACES';

    

2. 某个schema是否存在某view
   

   SELECT
       table_schema,
       table_name,
       table_type,
       table_rows 
   FROM
       information_schema.TABLES 
   WHERE
       table_schema = 'courier' AND table_type = 'VIEW' 
   ORDER BY
       TABLE_TYPE

    

3. 统计某个库中所有表所有记录数
   

    

    

   show table status from information_schema

    

4. 统计某库中BASE TABLE & VIEW 数量
   

   SELECT
       table_schema,
       table_type,
       count( 1 ) 
   FROM
       information_schema.TABLES 
   WHERE
       table_schema = 'courier' 
   GROUP BY
       table_schema,
       table_type

    

5. 统计某个库中表数