MySQL操作之元数据获取

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

元数据(information-schema)

什么是元数据

 使用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中
 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';

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

 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';