当前位置：Gxlcms > 数据库问题 > MySQL INFORMATION_SCHEMA 使用

MySQL INFORMATION_SCHEMA 使用

时间：2021-07-01 10:21:17 帮助过：2人阅读

@table_schema=‘employees‘; SELECT table_name, table_type, engine, table_rows, avg_row_length, data_length, index_length, table_collation, create_time FROM information_schema.tables WHERE table_schema = @table_schema ORDER BY table_name;

-- 查看会话连接信息

<span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> 
    THREAD_ID,
    name,
    type,
    PROCESSLIST_ID,
    PROCESSLIST_USER </span><span style="color: #0000ff;">AS</span> <span style="color: #ff00ff;">user</span><span style="color: #000000;">,
    PROCESSLIST_HOST </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> host,
    PROCESSLIST_DB </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> db,
    PROCESSLIST_COMMAND </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> cmd,
    PROCESSLIST_TIME </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> time,
    PROCESSLIST_STATE </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> state,
    PROCESSLIST_INFO </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> info,
    CONNECTION_TYPE </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> type,
    THREAD_OS_ID </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> os_id
</span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
    performance_schema.threads
</span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
    type </span><span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">FOREGROUND</span><span style="color: #ff0000;">‘</span>
<span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> THREAD_ID;

-- CHARACTER_SETS 查看数据库支持的字符集

<span style="color: #0000ff;">SELECT </span><span style="color: #808080;">* </span><span style="color: #0000ff;">FROM </span><span style="color: #000000;">INFORMATION_SCHEMA.CHARACTER_SETS
</span><span style="color: #0000ff;">WHERE </span><span style="color: #000000;">CHARACTER_SET_NAME </span><span style="color: #808080;">LIKE</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">utf%</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
SHOW </span><span style="color: #0000ff;">CHARACTER</span> <span style="color: #0000ff;">SET</span> <span style="color: #808080;">LIKE</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">utf%</span><span style="color: #ff0000;">‘</span>;

-- COLLATIONS 字符序

<span style="color: #008080;">--</span><span style="color: #008080;"> 用于指定数据集如何排序，以及字符串的比对规则</span>
<span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> INFORMATION_SCHEMA.COLLATIONS
</span><span style="color: #0000ff;">WHERE</span> COLLATION_NAME <span style="color: #808080;">LIKE</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">utf%</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
SHOW COLLATION </span><span style="color: #808080;">LIKE</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">utf%</span><span style="color: #ff0000;">‘</span>;

-- 查看表结构定义信息

<span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> 
    table_name,
    COLUMN_NAME,
    ordinal_position,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    column_type,
    column_key,
    character_set_name,
    collation_name
</span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
    INFORMATION_SCHEMA.COLUMNS
</span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
    table_name </span><span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">employees</span><span style="color: #ff0000;">‘</span>
        <span style="color: #808080;">AND</span> table_schema <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">employees</span><span style="color: #ff0000;">‘</span>;

show columns from employees from employees;

desc employeees.employees;

-- 查看支持的引擎

<span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>  <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> INFORMATION_SCHEMA.ENGINES;
show ENGINES;</span>

-- 查看数据库的数据文件信息

<span style="color: #0000ff;">SELECT</span> 
    <span style="color: #ff00ff;">FILE_ID</span><span style="color: #000000;">,
    </span><span style="color: #ff00ff;">FILE_NAME</span><span style="color: #000000;">,
    FILE_TYPE,
    TABLESPACE_NAME,
    FREE_EXTENTS,
    TOTAL_EXTENTS,
    ((TOTAL_EXTENTS </span><span style="color: #808080;">-</span> FREE_EXTENTS) <span style="color: #808080;">*</span> EXTENT_SIZE) <span style="color: #808080;">/</span> <span style="color: #800000; font-weight: bold;">1024</span> <span style="color: #808080;">/</span> <span style="color: #800000; font-weight: bold;">1024</span> <span style="color: #0000ff;">AS</span><span style="color: #000000;"> MB_used,
    EXTENT_SIZE,
    INITIAL_SIZE,
    MAXIMUM_SIZE,
    AUTOEXTEND_SIZE,
    DATA_FREE,
    STATUS,
    ENGINE
</span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
    INFORMATION_SCHEMA.FILES;</span>

-- 查看指定表的约束

<span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> 
    constraint_schema,
    table_name,
    constraint_name,
    column_name,
    ordinal_position,
    CONCAT(table_name,
            </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">.</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
            column_name,
            </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> -> </span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
            referenced_table_name,
            </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">.</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
            referenced_column_name) </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> list_of_fks
</span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
    information_schema.KEY_COLUMN_USAGE
</span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
    REFERENCED_TABLE_SCHEMA </span><span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">employees</span><span style="color: #ff0000;">‘</span>
        <span style="color: #808080;">AND</span> REFERENCED_TABLE_NAME <span style="color: #0000ff;">IS</span> <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span>
<span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> TABLE_NAME , COLUMN_NAME;

-- 查看指定分区表信息

<span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> 
    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_rows
</span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
    information_schema.PARTITIONS
</span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
    table_schema </span><span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">test</span><span style="color: #ff0000;">‘</span>
        <span style="color: #808080;">AND</span> table_name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">t</span><span style="color: #ff0000;">‘</span>;

-- 查看支持的插件

<span style="color: #0000ff;">SELECT</span><span style="color: #000000;">
  PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE,
  PLUGIN_LIBRARY, PLUGIN_LICENSE
</span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> INFORMATION_SCHEMA.PLUGINS;
SHOW PLUGINS;</span>

-- 查看数据库连接信息

<span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> INFORMATION_SCHEMA.PROCESSLIST;
SHOW </span><span style="color: #0000ff;">FULL</span> PROCESSLIST;

-- 查看数据库中的存储过程、函数等

<span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> 
    ROUTINE_SCHEMA,
    routine_name,
    ROUTINE_TYPE,
    data_type,
    routine_body,
    routine_definition,
    routine_comment
</span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
    INFORMATION_SCHEMA.ROUTINES
</span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
    ROUTINE_TYPE </span><span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">PROCEDURE</span><span style="color: #ff0000;">‘</span>
<span style="color: #808080;">AND</span> ROUTINE_SCHEMA<span style="color: #808080;">=</span>"employees";

-- 查看存在的数据库及字符集信息

<span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> 
    SCHEMA_NAME,
    DEFAULT_CHARACTER_SET_NAME,
    DEFAULT_COLLATION_NAME
</span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
    INFORMATION_SCHEMA.SCHEMATA;
SHOW DATABASES;</span>

-- 查看索引信息

<span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> 
    table_schema,
    table_name,
    index_name,
    COLUMN_NAME,
    COLLATION,
    CARDINALITY,
    index_type
</span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
    INFORMATION_SCHEMA.</span><span style="color: #0000ff;">STATISTICS</span>
<span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
    table_name </span><span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">employees</span><span style="color: #ff0000;">‘</span>
        <span style="color: #808080;">AND</span> table_schema <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">employees</span><span style="color: #ff0000;">‘</span>;<br><br>SHOW INDEX FROM employees FROM employees;

-- 查看数据库大小

<span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> 
    table_schema </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">database</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
    CONCAT(</span><span style="color: #ff00ff;">ROUND</span>(<span style="color: #ff00ff;">SUM</span>(data_length <span style="color: #808080;">+</span> index_length) <span style="color: #808080;">/</span> (<span style="color: #800000; font-weight: bold;">1024</span> <span style="color: #808080;">*</span> <span style="color: #800000; font-weight: bold;">1024</span><span style="color: #000000;">),
                    </span><span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">),
            </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">M</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">) size
</span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
    information_schema.TABLES
</span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
    ENGINE </span><span style="color: #808080;">=</span> (<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">MyISAM</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">||</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">InnoDB</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span> table_schema;

-- 查看表大小　　

<span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> 
    CONCAT(table_schema, </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">.</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">, table_name) table_name,
    CONCAT(</span><span style="color: #ff00ff;">ROUND</span>(data_length <span style="color: #808080;">/</span> (<span style="color: #800000; font-weight: bold;">1024</span> <span style="color: #808080;">*</span> <span style="color: #800000; font-weight: bold;">1024</span>), <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">),
            </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">M</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">) data_length,
    CONCAT(</span><span style="color: #ff00ff;">ROUND</span>(index_length <span style="color: #808080;">/</span> (<span style="color: #800000; font-weight: bold;">1024</span> <span style="color: #808080;">*</span> <span style="color: #800000; font-weight: bold;">1024</span>), <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">),
            </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">M</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">) index_length,
    CONCAT(</span><span style="color: #ff00ff;">ROUND</span>(<span style="color: #ff00ff;">ROUND</span>(data_length <span style="color: #808080;">+</span> index_length) <span style="color: #808080;">/</span> (<span style="color: #800000; font-weight: bold;">1024</span> <span style="color: #808080;">*</span> <span style="color: #800000; font-weight: bold;">1024</span><span style="color: #000000;">),
                    </span><span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">),
            </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">M</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">) total_size
</span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
    information_schema.TABLES
</span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> data_length <span style="color: #0000ff;">DESC</span>;

MySQL INFORMATION_SCHEMA 使用

标签：

MySQL INFORMATION_SCHEMA 使用

-- 查看会话连接信息

-- CHARACTER_SETS 查看数据库支持的字符集

-- COLLATIONS 字符序

-- 查看表结构定义信息

-- 查看支持的引擎

-- 查看数据库的数据文件信息

-- 查看指定表的约束

-- 查看指定分区表信息

-- 查看支持的插件

-- 查看数据库连接信息

-- 查看数据库中的存储过程、函数等

-- 查看存在的数据库及字符集信息

-- 查看索引信息

-- 查看数据库大小

-- 查看表大小

人气教程排行

-- 查看表大小　　