mysql元数据_MySQL操作之元数据获取

大家好，我是anyux。本文介绍MySQL数据库获取元数据。

元数据(information-schema)

什么是元数据

元数据是存储在"基表"中。通过专用的DDL语句或DCL语句进行修改，通过专用视图进行元数据的查询

information_schema中保存了大量元数据查询的视图。show命令是封装好的元数据查询的命令

使用school数据库

use school;

创建视图

create view tmp_view as select STU.sname as 姓名, group_concat(SC.score) as 成绩 from student as STU join sc as SC on STU.sno=SC.sno group by STU.sname;

查询视图

select * from tmp_view;

删除视图

drop view tmp_view;information_schema中保存的就是一个临时表，临时表的查询方法和表的查询方法是一样的。

进入到information_schema中

use information_schema;

查看视图表

show tables;

其中的TABLES是非常重要的，通过这个视图可以查看到数据库系统中所有的表信息

查看TABLES表结构

desc TABLES;

查看建表语句

Create Table: CREATE TEMPORARY TABLE `TABLES` (`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',`TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',`ENGINE` varchar(64) DEFAULT NULL,`VERSION` bigint(21) unsigned DEFAULT NULL,`ROW_FORMAT` varchar(10) DEFAULT NULL,`TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,`AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,`DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,`MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,`INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,`DATA_FREE` bigint(21) unsigned DEFAULT NULL,`AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,`CREATE_TIME` datetime DEFAULT NULL,`UPDATE_TIME` datetime DEFAULT NULL,`CHECK_TIME` datetime DEFAULT NULL,`TABLE_COLLATION` varchar(32) DEFAULT NULL,`CHECKSUM` bigint(21) unsigned DEFAULT NULL,`CREATE_OPTIONS` varchar(255) DEFAULT NULL,`TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT '') ENGINE=MEMORY DEFAULT CHARSET=utf8

其中重要的下面会标识出来

TABLE_SCHEMA 表所在的库名TABLE_NAME 表名ENGINE 引擎名称TABLE_ROWS 表的行数AVG_ROW_LENGTH 表中行的平均行(字节)INDEX_LENGTH 索引的占用空间大小(字节)

使用information_schema获取数据库信息

显示所有库和表的信息

显示所有库下的所有表信息

select table_schema,table_name from `information_schema`.`tables`;

所有库对就一串表显示

select table_schema as 数据库名,group_concat(table_name) as 数据表名 from `information_schema`.`tables` group by table_schema;

统计所有innodb引擎的表

查询所有引擎为innodb的表

select table_schema as 数据库名称, engine as 引擎 from `information_schema`.`tables` where `information_schema`.`tables`.engine='innodb' group by table_schema;

统计world库下的city表占用的空间大小

统计公式：占用空间大小=平均行长度*行数+索引长度

select table_name as 表名称, concat(avg_row_length*table_rows+index_length,'字节') as 占用空间大小 from `information_schema`.`tables` where table_schema='world' and table_name='city';

统计world数据库占用空间大小

select table_schema as 数据库名称, concat(sum(avg_row_length*table_rows+index_length),'字节') as 占用空间大小 from `information_schema`.`tables` where table_schema='world';

统计每个库的占用空间大小，并从大到小排序

提示，information_schema是内存表，不占用内存空间。数据库系统关闭时会清空

select table_schema as 数据库名, concat(sum(avg_row_length*table_rows+index_length),'字节') as '占用空间大小' from `information_schema`.`tables` group by `table_schema` order by 占用空间大小 DESC;

配合concat拼接语句或命令

模仿以下语句，进行数据库分库分表备份

备份world库下的city表到/opt/目录下

mysqldump -uroot -p123 world city >/opt/world_city.sql;

拼接sql语句，保存到系统目录下

select concat("mysqldump -uroot -proot ",table_schema," ",table_name," >/opt/",table_schema,"_",table_name,".sql") as SQL语句 from `information_schema`.`tables`;

模仿以下语句，进行批量生成world数据库所有表操作

语句作用是将未备份且数据丢失的数据恢复

alter table world.city discard tablespace;

此处是将指定的数据库中所有表丢失的数据备份

select concat("alter table ",table_schema,".",table_name," discard tablespace;") from `information_schema`.`tables` where table_schema='world';