MySQL INFORMATION_SCHEMA 使用

標籤：

--  查看指定資料庫的表資訊

SET @table_schema=‘employees‘;SELECT     table_name,    table_type,    engine,    table_rows,    avg_row_length,    data_length,    index_length,    table_collation,    create_timeFROM    information_schema.tablesWHERE    table_schema = @table_schemaORDER BY table_name;

 

-- 查看會話串連資訊

SELECT     THREAD_ID,    name,    type,    PROCESSLIST_ID,    PROCESSLIST_USER AS user,    PROCESSLIST_HOST AS host,    PROCESSLIST_DB AS db,    PROCESSLIST_COMMAND AS cmd,    PROCESSLIST_TIME AS time,    PROCESSLIST_STATE AS state,    PROCESSLIST_INFO AS info,    CONNECTION_TYPE AS type,    THREAD_OS_ID AS os_idFROM    performance_schema.threadsWHERE    type = ‘FOREGROUND‘ORDER BY THREAD_ID;

 

-- CHARACTER_SETS 查看資料庫支援的字元集

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETSWHERE CHARACTER_SET_NAME LIKE ‘utf%‘;SHOW CHARACTER SET LIKE ‘utf%‘;

 

-- COLLATIONS  字元序

-- 用於指定資料集如何排序，以及字串的比對規則SELECT * FROM INFORMATION_SCHEMA.COLLATIONSWHERE COLLATION_NAME LIKE ‘utf%‘;SHOW COLLATION LIKE ‘utf%‘;

-- 查看錶結構定義資訊

SELECT     table_name,    COLUMN_NAME,    ordinal_position,    DATA_TYPE,    IS_NULLABLE,    COLUMN_DEFAULT,    column_type,    column_key,    character_set_name,    collation_nameFROM    INFORMATION_SCHEMA.COLUMNSWHERE    table_name = ‘employees‘        AND table_schema = ‘employees‘;

show columns from employees from employees;

desc employeees.employees;

 

-- 查看支援的引擎

SELECT *  FROM INFORMATION_SCHEMA.ENGINES;show ENGINES;

 

-- 查看資料庫的資料檔案資訊

SELECT     FILE_ID,    FILE_NAME,    FILE_TYPE,    TABLESPACE_NAME,    FREE_EXTENTS,    TOTAL_EXTENTS,    ((TOTAL_EXTENTS - FREE_EXTENTS) * EXTENT_SIZE) / 1024 / 1024 AS MB_used,    EXTENT_SIZE,    INITIAL_SIZE,    MAXIMUM_SIZE,    AUTOEXTEND_SIZE,    DATA_FREE,    STATUS,    ENGINEFROM    INFORMATION_SCHEMA.FILES;

 

-- 查看指定表的約束

SELECT     constraint_schema,    table_name,    constraint_name,    column_name,    ordinal_position,    CONCAT(table_name,            ‘.‘,            column_name,            ‘ -> ‘,            referenced_table_name,            ‘.‘,            referenced_column_name) AS list_of_fksFROM    information_schema.KEY_COLUMN_USAGEWHERE    REFERENCED_TABLE_SCHEMA = ‘employees‘        AND REFERENCED_TABLE_NAME IS NOT NULLORDER BY TABLE_NAME , COLUMN_NAME;

 

-- 查看指定分區表資訊

SELECT     TABLE_SCHEMA,    table_name,    partition_name,    subpartition_name sub_par,    partition_ordinal_position par_position,    partition_method method,    partition_expression expression,    partition_description description,    table_rowsFROM    information_schema.PARTITIONSWHERE    table_schema = ‘test‘        AND table_name = ‘t‘;

 

-- 查看支援的外掛程式

SELECT  PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE,  PLUGIN_LIBRARY, PLUGIN_LICENSEFROM INFORMATION_SCHEMA.PLUGINS;SHOW PLUGINS;

 

-- 查看資料庫連接資訊

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;SHOW FULL PROCESSLIST;

 

-- 查看資料庫中的預存程序、函數等

SELECT     ROUTINE_SCHEMA,    routine_name,    ROUTINE_TYPE,    data_type,    routine_body,    routine_definition,    routine_commentFROM    INFORMATION_SCHEMA.ROUTINESWHERE    ROUTINE_TYPE = ‘PROCEDURE‘AND ROUTINE_SCHEMA="employees";

 

-- 查看存在的資料庫及字元集資訊

SELECT     SCHEMA_NAME,    DEFAULT_CHARACTER_SET_NAME,    DEFAULT_COLLATION_NAMEFROM    INFORMATION_SCHEMA.SCHEMATA;SHOW DATABASES;

 

-- 查看索引資訊

SELECT     table_schema,    table_name,    index_name,    COLUMN_NAME,    COLLATION,    CARDINALITY,    index_typeFROM    INFORMATION_SCHEMA.STATISTICSWHERE    table_name = ‘employees‘        AND table_schema = ‘employees‘;

SHOW INDEX FROM employees FROM employees;

 

-- 查看資料庫大小

SELECT     table_schema ‘database‘,    CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024),                    2),            ‘M‘) sizeFROM    information_schema.TABLESWHERE    ENGINE = (‘MyISAM‘ || ‘InnoDB‘)GROUP BY table_schema;

 

-- 查看錶大小　　

SELECT     CONCAT(table_schema, ‘.‘, table_name) table_name,    CONCAT(ROUND(data_length / (1024 * 1024), 2),            ‘M‘) data_length,    CONCAT(ROUND(index_length / (1024 * 1024), 2),            ‘M‘) index_length,    CONCAT(ROUND(ROUND(data_length + index_length) / (1024 * 1024),                    2),            ‘M‘) total_sizeFROM    information_schema.TABLESORDER BY data_length DESC;

 

MySQL INFORMATION_SCHEMA 使用