mysql中information_schema数据库

迷南。 2023-10-04 13:08 64阅读 0赞

**目录**

1、information\_schema说明

2、information\_schema下的表

2.1 schemata表

2.2 tables表

2.3 columns表

2.4 statistics表

2.5、user\_privileges表（用户权限表）

2.7 table\_privileges表（表权限表）

2.8 column\_privileges表（列权限表）

2.9 character\_sets 字符集表

2.10 collations表

2.11 collation\_character\_set\_applicability表

2.12 table\_constraints表（存在约束的表的约束信息）

2.13 key\_column\_usage表（描述了具有约束的键列）

2.14 routines表（mysql服务器中存储过程和函数信息）

2.15 views表（视图表）

2.16 triggers表（触发器表）

--------------------

## **1、information\_schema说明** ##

information\_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

information\_schema是信息数据库，其中保存着关于mysql服务器所维护的所有其他数据库的信息。在information\_schema中，有数个只读表。它们**实际上是视图**，而不是基本表，因此，你将无法看到与之相关的任何文件，也就说information\_schema是一个虚拟数据库，物理上并不存在。

![714f29551ffc386b40758c7b1293fbbd.png][]

事实上，尽管不需要生成名为information\_schema的文件，仍提供了名为information\_schema的数据库。可以使用USE语句将information\_schema选择为默认数据库，但访问该数据库中所含表的唯一方式是使用SELECT语句。不能在其中插入内容，不能更新它们，也不能删除其中的内容。

每位MySQL用户均有权访问这些表，但仅限于表中的特定行，在这类行中含有用户具有恰当访问权限的对象。

**与SHOW相比，SELECT****的优点**

SELECT ... FROM information\_schema语句的目的在于提供一种更为一致的方式，以访问MySQL所支持的各种SHOW语句（SHOW DATABASES、SHOW TABLES等等）提供的信息。与SHOW相比，使用SELECT有多项优点“

· 符合Code规则。也就是说，所有访问均是在表上进行的。

· 不需要了解新语句的语法。由于他们已知道SELECT的工作方式，仅需了解对象名即可。

· 实现人无需操心增加关键词方面的事宜。

· 有数百万种可能的输出变化，而不是一种。这样，就为对元数据有不同需求的应用程序提供了更高的灵活性。

· 由于其他DBMS也采用了这类方式，移植更为容易。

然而，由于SHOW在MySQL的雇员和用户中十分流行，如果SHOW消失，可能会导致混乱，因此传统的语法方式无法给出消除SHOW的足够理由。事实上，在MySQL 5.1中，还对SHOW进行了多项增强。关于这方面的介绍，请参见[“SHOW语句的扩展”][SHOW]。

## **2、information\_schema下的表** ##

### **2.1 schemata表** ###

schemata表提供了当前mysql实例中所有数据库的信息。show databases的结果取之此表。该表对应的列说明如下：

<table> 
 <tbody> 
 <tr> 
 <td>列名</td> 
 <td>中文解释 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</td> 
 <td>例子 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</td> 
 </tr> 
 <tr> 
 <td>catalog_name</td> 
 <td></td> 
 <td></td> 
 </tr> 
 <tr> 
 <td>schema_name</td> 
 <td>数据库名</td> 
 <td></td> 
 </tr> 
 <tr> 
 <td>default_character_set_name</td> 
 <td>数据库默认编码</td> 
 <td>utf8</td> 
 </tr> 
 <tr> 
 <td>default_collation_name</td> 
 <td></td> 
 <td>utf8_general_ci</td> 
 </tr> 
 <tr> 
 <td>sql_path</td> 
 <td></td> 
 <td>null</td> 
 </tr> 
 </tbody> 
</table>

**注释：**SQL\_P

select schema_name as `database` from information_schema.SCHEMATA where schema_name like 'naxx%';
    
    show databases like 'naxx%';

### **2.2 tables表** ###

tables表提供了关于数据库中的**表的信息**（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。

> **show tables from db\_ca\_ods**;【注db\_ca\_ods为数据库名】的结果取之此表。

<table> 
 <tbody> 
 <tr> 
 <td>列名</td> 
 <td>中文解释</td> 
 <td>例子/说明</td> 
 </tr> 
 <tr> 
 <td>table_catalog&nbsp;&nbsp;</td> 
 <td></td> 
 <td>NULL</td> 
 </tr> 
 <tr> 
 <td>table_schema</td> 
 <td>数据库名称</td> 
 <td>information_schema</td> 
 </tr> 
 <tr> 
 <td>table_name</td> 
 <td>表名</td> 
 <td>&nbsp;tables表，属于information_schema数据库</td> 
 </tr> 
 <tr> 
 <td>table_type</td> 
 <td>&nbsp;表类型</td> 
 <td>&nbsp;应是BASE TABLE（基本表）或VIEW（视图）。如果表是临时性的，TABLE_TYPE&nbsp;=&nbsp;TEMPORARY。（没有临时视图，因此，因此不存在歧义）</td> 
 </tr> 
 <tr> 
 <td>engine</td> 
 <td>表所用引擎</td> 
 <td>InnoDB</td> 
 </tr> 
 <tr> 
 <td>version</td> 
 <td> Version </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> row_format </td> 
 <td> Row_format </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> table_rows </td> 
 <td> 表中总行数 </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> avg_row_length </td> 
 <td> Avg_row_length </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> data_length </td> 
 <td> Data_length </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> max_data_length </td> 
 <td> Max_data_length </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> index_length </td> 
 <td> Index_length </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> data_free </td> 
 <td> Data_free </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> auto_increment </td> 
 <td> Auto_increment </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> create_time </td> 
 <td> Create_time </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> update_time </td> 
 <td> Update_time </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> check_time </td> 
 <td> Check_time </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> table_collation </td> 
 <td> Collation </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> checksum </td> 
 <td> Checksum </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> create_options </td> 
 <td> Create_options </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> table_comment </td> 
 <td> Comment </td> 
 <td> 表的说明，及create语句中comment的值 </td> 
 </tr> 
 </tbody> 
</table>

下述语句是等效的：

select table_name from information_schema.tables where table_schema = 'db_name' and  table_name like 'iap%';
    
    show tables from `db_name` like 'iap%';

*  **查看某个数据库表的记录数。**

-- 查看某个数据库表的记录数。
    select table_schema,table_name,table_rows from tables where TABLE_SCHEMA = '数据库名称' order by table_rows desc;

*  **查看数据库所占空间**

-- 查看数据库所占空间
    SELECT
    	concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
    	concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB 
    FROM
    	information_schema.TABLES 
    WHERE
    	table_schema = 'naxx-iap';

*  **查看某个表所占空间**

-- 查看某个表所占空间
    SELECT
        concat( TRUNCATE ( sum( data_length ) / 1024 / 1024, 2 ), 'MB' ) AS data_size,
        concat( TRUNCATE ( sum( max_data_length ) / 1024 / 1024, 2 ), 'MB' ) AS max_data_size,
        concat( TRUNCATE ( sum( data_free ) / 1024 / 1024, 2 ), 'MB' ) AS data_free,
        concat( TRUNCATE ( sum( index_length ) / 1024 / 1024, 2 ), 'MB' ) AS index_size 
    FROM
        information_schema.TABLES 
    WHERE
        TABLE_NAME = '表名';

### 2.3 columns表 ###

columns表提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。

>  **show columns from schemaname(数据库名).tablename(表名)**的结果取之此表。

### ***2.4* *statistics**表*** ###

statistics表：提供了关于表索引的信息。

>  **show index from schemaname.tablename** 的结果取之此表

`下述语句是等效的：`

-- 查询表中索引
    select * from information_schema.statistics where table_name = 'db_name' and table_schema = 'tb_name';
    
    show index from `naxxdb_nameiap`.tbl_name;

### 2.5、**user\_privileges表（用户权限表）** ###

user\_privileges（用户权限）表给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表

<table> 
 <tbody> 
 <tr> 
 <td>列名</td> 
 <td>中文解释</td> 
 <td>例子/说明</td> 
 </tr> 
 <tr> 
 <td>grantee</td> 
 <td></td> 
 <td>例如“user'@'host”</td> 
 </tr> 
 <tr> 
 <td>table_catalog</td> 
 <td></td> 
 <td></td> 
 </tr> 
 <tr> 
 <td>privilege_type</td> 
 <td>权限类型</td> 
 <td>例如：select、drop等</td> 
 </tr> 
 <tr> 
 <td>is_grantable</td> 
 <td>是否授权，即是否拥有“将此权限赋予其他用户”的权限，对应mysql.user表中的grant_priv字段</td> 
 <td>例如：YES或NO</td> 
 </tr> 
 </tbody> 
</table>

实例：

![7ab4865e262ebd6111eb8d23d40c5b59.png][]

**2.6 schema\_privileges表（方案/数据库权限表）**

schema\_privileges（方案权限）表给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表

<table> 
 <tbody> 
 <tr> 
 <td>列名</td> 
 <td>中文解释</td> 
 <td>例子/说明</td> 
 </tr> 
 <tr> 
 <td>grantee</td> 
 <td></td> 
 <td>例“user'@'host”</td> 
 </tr> 
 <tr> 
 <td>table_catalog</td> 
 <td></td> 
 <td></td> 
 </tr> 
 <tr> 
 <td>table_schema</td> 
 <td>数据库名</td> 
 <td></td> 
 </tr> 
 <tr> 
 <td>privilege_type</td> 
 <td>权限类型</td> 
 <td></td> 
 </tr> 
 <tr> 
 <td>is_grantable</td> 
 <td>是否授权</td> 
 <td>例如：YES或NO</td> 
 </tr> 
 </tbody> 
</table>

### **2.7 table\_privileges表（表权限表）** ###

table\_privileges（表权限）表给出了关于表权限的信息。该信息源自**mysql.tables\_priv**授权表。是非标准表

### ***2.8 column\_privileges表（列权限表）***  ###

*column\_privileges（列权限）表给出了关于列权限的信息。该信息源自**mysql.columns\_priv**授权表。是非标准表。*

### ***2.9 character\_sets 字符集表*** ###

character\_sets（字符集）表提供了mysql实例可用字符集的信息。

> **show character set;** 结果集取之此表。

### **2.10 collations表** ###

collations表提供了关于各字符集的对照信息。

### **2.11 collation\_character\_set\_applicability表** ###

collation\_character\_set\_applicability表指明了可用于校对的字符集。

> 这些列等效于**show collation**的前两个显示字段。

### **2.12 table\_constraints表（存在约束的表的约束信息）** ###

table\_constraints表描述了存在约束的表。以及表的约束类型。

### **2.13 key\_column\_usage表（描述了具有约束的键列）** ###

key\_column\_usage表描述了具有约束的键列。

### **2.14 routines表（mysql服务器中存储过程和函数信息）** ###

routines表提供了关于存储子程序（存储程序和函数）的信息。此时，routines表不包含自定义函数（udf）。名为“mysql.proc name”的列指明了对应于information\_schema.routines表的**mysql.proc**表列。

### **2.15 views表（视图表）** ###

views表给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。

### **2.16 triggers表（触发器表）** ###

triggers表提供了关于触发程序的信息。必须有super权限才能查看该表

[714f29551ffc386b40758c7b1293fbbd.png]: https://img-blog.csdnimg.cn/img_convert/714f29551ffc386b40758c7b1293fbbd.png
[SHOW]: http://tool.oschina.net/uploads/apidocs/mysql-5.1-zh/information-schema.html#extended-show
[7ab4865e262ebd6111eb8d23d40c5b59.png]: https://img-blog.csdnimg.cn/img_convert/7ab4865e262ebd6111eb8d23d40c5b59.png