WithCoderWithCoderWithCoder

MySQL自带的information_schema简介

    在MySQL中,information_schema是MySQL自带的,我们可以把 information_schema 看作是一个数据库,它是有关数据库信息的信息数据库。它提供了访问数据库“元数据”的方式,其中保存着关于MySQL服务器所维护的所有其他数据库的信息,如数据库名、数据库中的数据表,数据表的列的数据类型,或访问权限等信息。在information_schema中,有数个只读表,它们实际上是视图,而不是基本表,因此无法看到与之相关的任何文件。

    一、information_schema数据库(主要)表说明:

    SCHEMATA表:提供了当前mysql实例中所有数据库的信息,show databases 从此表获取结果。

    TABLES表:提供了关于数据库中表的信息(包括视图),此表详细记录了某个表属于哪个schema,表类型,表引擎,创建时间等信息,show tables from schemaname的结果来自于这个表。

    COLUMNS表:提供了表中的列信息,记录了某个表的所有列以及每个列的信息,show columns from schemaname.tablename结果来自于这个表。

    STATISTICS表:提供了关于表的索引信息,show index from schemaname.tablename的结果自于这个表。

    USER_PRIVILEGES(用户权限)表:给出了关于用户权限的信息,该信息源自mysql.user授权表,是非标准表。

    SCHEMA_PRIVILEGES(结构权限)表:给出了关于结构(数据库)权限的信息,该信息来自mysql.db授权表,是非标准表。

    TABLE_PRIVILEGES(表权限)表:给出了关于表权限的信息,该信息源自mysql.tables_priv授权表,是非标准表。

    COLUMN_PRIVILEGES(列权限)表:给出了关于列权限的信息,该信息源自mysql.columns_priv授权表,是非标准表。

    CHARACTER_SETS(字符集)表:提供了mysql实例可用字符集的信息,SHOW CHARACTER SET结果集来自于这个表。

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

    COLLATION_CHARACTER_SET_APPLICABILITY表:指明了可用于校对的字符集,这些列等效于SHOW COLLATION的前两个显示字段。

    TABLE_CONSTRAINTS表:描述了存在约束的表。以及表的约束类型。

    KEY_COLUMN_USAGE表:描述了具有约束的键列。

    ROUTINES表:提供了关于存储子程序(存储程序和函数)的信息。此时,ROUTINES表不包含自定义函数(UDF)。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

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

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

    二、使用举例

    2.1 查看数据库和数据表容量大小 

SELECT
	table_schema AS '数据库',
	SUM(table_rows) AS '记录数',
	SUM(TRUNCATE(data_length/1024/1024, 2)) AS '数据容量(MB)',
	SUM(TRUNCATE(index_length/1024/1024, 2)) AS '索引容量(MB)'
FROM information_schema.tables
	GROUP BY table_schema
	ORDER BY SUM(data_length) DESC, SUM(index_length) DESC;

    结果如下图:

    1-200F915194G54.png    

    更多细节,可以参考“查看数据库和数据表容量大小

    2.2 想要查询指定表的自增ID

SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name='db_data';

    结果如下图:

    1-200F9152225X6.png

    另外,我们也可以修改指定数据表的自增ID,采用以下语句:   

alter table 表名 auto_increment = 数字;

    更多细节,可以参考“解决mysql数据库表自增主键id删除记录后主键值不连续的问题

    2.3 批量删除表

    批量删除表的sql语句比较容易,如下:

SELECT
   CONCAT('drop table ',table_name,';')
FROM
   information_schema. TABLES
WHERE
   TABLE_SCHEMA='test' AND table_name LIKE 'db_%';

    执行查询,会自动生成出 drop table table_name 这样的SQL语句,同样执行后,即可批量删除表。


欢迎分享交流,转载请注明出处:WithCoder » MySQL自带的information_schema简介