Mysql 的元数据 information_schema

2021-02-16 | 129 浏览

Mysql的元数据information_schema

1.information_schema介绍

1.虚拟库

2.开启数据库时产生的

3.不能被删除和修改

4.库中存储的是视图

5.此库中的视图也是不能被删除和修改的,智能select查询

作用

此库中的视图是用来,间接的查询数据库的"元数据"("基表"数据)

基表:数据是通过自动统计收集而来的.

基表:是不允许人为直接增\删\改\查的

必须通过:专用的DDL,DCL实现增\删\改,show语句和information_schema可以做查询

元数据

库

表

状态

2.information_schema重要操作

information_schema.tables 记录了:整个MySQL数据库中,所有的表的详细属性信息

db01 [information_schema]>desc tables;
 TABLE_SCHEMA      ----->  表所在的库
 TABLE_NAME        ----->  表名字
 ENGINE            ----->  表的存储引擎
 TABLE_ROWS        ----->  表的行数
 AVG_ROW_LENGTH    ----->  表的平均行长度(字节)
 INDEX_LENGTH      ----->  表的索引长度(字节)

1.统计各个库下表的个数

SELECT table_schema,COUNT(table_name) 
FROM information_schema.tables 
GROUP BY table_schema;

2.统计各个引擎表的个数

SELECT ENGINE,COUNT(table_name) FROM information_schema.tables 
GROUP BY ENGINE;

3.列出所有innodb引擎的表

SELECT  table_schema,table_name ,ENGINE 
FROM information_schema.tables 
WHERE ENGINE='innodb';

4.world的数据库下每个表的行数

SELECT table_name ,table_rows FROM information_schema.tables
WHERE table_schema='world';

5.统计每个库占用的总空间大小 ,一张表占用空间大小: AVG_ROW_LENGTH * TABLE_ROWS+ INDEX_LENGTH

SELECT table_schema,SUM(AVG_ROW_LENGTH * TABLE_ROWS+ INDEX_LENGTH)/1024 AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;

6.扩展107张表 (一个库,无规则表名)

ALTER TABLE t1 DISCARD TABLESPACE;
ALTER TABLE t1 IMPORT TABLESPACE;

7.CONCAT()函数使用

SELECT CONCAT("alter table ",table_name," discard tablespace;") FROM information_schema.tables 
WHERE table_schema='world';
SELECT table_name ROM information_schema.tables 
WHERE table_schema='world';

SELECT CONCAT("alter table ",table_name," discard tablespace;") 
FROM information_schema.tables  WHERE table_schema='world' INTO OUTFILE '/tmp/discard.sql';

8.使用导出语句受限制

vim /etc/my.cnf
secure-file-priv=/tmp

9.分表备份(练习)_全库所有的表都单独备份

mysqldump -uroot -p123 world city  > /bak/world_city.sql 

select concat("mysqldump -uroot -p123 ",table_schema," ",table_name,"  > /bak/",table_schema,"_",table_name,".sql") 
from information_schema.tables;