MySQL元数据

元数据获取

什么是元数据 : 除了表之外的都是元数据也称之为基表
比如库表属性状态信息权限等

视图模式:
自定义 :将一大串命令指定成一个视图类似别名
create view v_xxxx as 一下子命令 # 定义视图只在当前库中使用
select * from v_xxx; # 调用视图
drop view v_xxx # 删除视图库

元数据获取方法

方法一: show语句系统封装好的基础查询功能可实现大部分的元数据查询需求

方法二: information_schema视图库

系统自定义的元数据查询方法

select 可看大部分系统自带的

应用场景数据库资产统计

例如有多少库表数据库数据增长量结构信息变化等各种资产统计

用于做数据库资产统计的视图

tables 常用列为

desc information_schema.tables;

TABLE_SCHEMA    #  表所在的库 (库的名字)
TABLE_NAME      #  表名       (表的名字)
ENGINE          #  引擎           
TABLE_ROWS      #  表的行数
AVG_ROW_LENGTH  #  平均行长度    (行的长度)
INDEX_LENGTH    #  索引长度     (表的大小为 行数*长度+索引长度)
TABLE_COMMENT   #  表的注释
columns         #  表的所有信息

实际应用:

统计库里表的个数

USE information_schema;
select table_schema,count(table_name) 
from information_schema.tables 
group by table_schema;

统计不同存储引擎的表名

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

统计所有非系统表, 非innodb引擎的表名单

USE information_schema;
SELECT table_schema,table_name ,ENGINE 
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
HAVING  ENGINE != 'innodb';

统计每个库总的数据量

数据量计算: 表的行数乘以行的长度

USE information_schema;
SELECT table_schema,SUM(table_rows * AVG_ROW_LENGTH + INDEX_LENGTH)/1024/1024 AS "total_mb"
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','mysql','performance_schema')
GROUP BY table_schema
ORDER BY total_mb DESC;

统计一个库的数据量

USE information_schema;
SELECT table_schema,SUM(table_rows * AVG_ROW_LENGTH)
FROM TABLES
WHERE table_schema='库名';

统计每个库下表的数据量

USE information_schema;
SELECT table_schema,table_name,SUM(table_rows * AVG_ROW_LENGTH)
FROM TABLES
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
GROUP BY table_name
ORDER BY table_schema;

统计一个表的数据量

USE information_schema;
SELECT table_schema,SUM(table_rows * AVG_ROW_LENGTH + INDEX_LENGTH)/1024/1024 
FROM information_schema.tables
WHERE table_name='表名';

统计school库下所有数据字典表名,引擎,数据行,数据量,注释等

主要使用的是columns表内数据

USE information_schema;
SELECT  * FROM COLUMNS;
SELECT column_name,column_type,column_comment
FROM information_schema.COLUMNS
WHERE table_schema='school';

字符串拼接 CONCAT

3.1.1 模仿以下语句,生成数据库下单表的备份语句(不包含系统表),并保存至/BACKUP/sh/bak.sh

SELECT CONCAT("mysqldump -uroot -p123"," ",table_schema," " ,table_name," ",">/backup/",table_schema,"_",table_name,".sql")
FROM information_schema.TABLES
WHERE table_schema
NOT IN ('sys','information_schema','mysql','performance_schema')
INTO OUTFILE ‘/BACKUP/sh/bak.sh’;

备份的话需要在配置文件中添加参数并重启数据库

/etc/my.cnf 
secure-file-priv

最后编辑于：2019.11.19 17:19:42