MySQL、Oracle元数据抽取分析

最近接到个任务是抽取mysql和Oracle的元数据，大致就是在库里把库、schema、表、字段、分区、索引、主键等信息抽取出来，然后导成excel。

因为刚开始接触元数据，对这个并不了解，就想借助一下万能的百度，结果百度了一圈全是告诉我sql，直接cv就行了。虽然得到了部分数据，但是查的哪个库，哪个表我是完全不清楚的，得到的数据也不是我想要的，只有自己去官网看文档来完成自己的任务。

授人以鱼不如授人以渔，把经验写出来，给跟我一样疑惑的小伙伴一个参考。

什么是元数据

参考文档地址

mysql:https://docs.oracle.com/cd/E17952_01/index.html

Oracle:https://docs.oracle.com/en/database/oracle/oracle-database/index.html

先说MySQL

mysql的元数据都在information_schema库中以视图的形式存在，只能看，不能修改。

进入文档后直接看information_schema的介绍，里面详细介绍了每一个表和字段。

常用的查询mysql元数据sql

-- 数据库
SELECT `schema_name` 库名,`DEFAULT_CHARACTER_SET_NAME` 默认字符集 FROM `SCHEMATA`

-- 表
SELECT `TABLE_NAME` 表名,`TABLE_COMMENT` 描述,`TABLE_TYPE` 表类型  FROM`TABLES`

-- 字段
SELECT `TABLE_SCHEMA` 库名,`TABLE_NAME` 表名,`COLUMN_NAME` 字段名,`COLUMN_COMMENT` 字段描述,`DATA_TYPE` 字段类型,`CHARACTER_MAXIMUM_LENGTH` 长度,`IS_NULLABLE` 是否为空
FROM `COLUMNS`

-- 分区
SELECT `TABLE_SCHEMA` 库名,`TABLE_NAME` 表名,`PARTITION_NAME` 分区名,`PARTITION_ORDINAL_POSITION` 分区编号,`PARTITION_EXPRESSION` 分区函数表达式
FROM `PARTITIONS` WHERE partition_name IS NOT NULL

-- 视图
SELECT * FROM `VIEWS`

-- 索引
SELECT * FROM STATISTICS

-- 主键
SELECT * FROM `COLUMNS` WHERE COLUMN_KEY = 'PRI'

再说Oracle

Oracle里的元数据在静态数据字典视图。

我们是不能直接访问数据字典表的，但可以通过数据字典视图访问其中的信息。要列出可用的数据字典视图，查询视图DICTIONARY就可以。

在静态数据字典视图里有三大类开头的视图：ALL_、DBA_、USER_

• ALL_视图显示所有的信息，当前用户，包括从其他架构中对象的当前用户的模式以及信息访问，如果当前用户拥有的权限或角色授权的方式访问这些对象。
• DBA_视图显示整个数据库中的所有相关信息。DBA_视图仅供管理员使用。只有拥有SELECT ANY DICTIONARY权限的用户才能访问它们。此权限DBA在系统最初安装时分配给角色。
• USER_视图显示从当前用户的模式中的所有的信息。查询这些视图不需要特殊权限。

ALL_，DBA_以及USER_对应于单个数据字典表视图通常是几乎相同的。

官网：

然后就可以通过文档说明找到对应的视图。

常用的sql：

-- 模式
SELECT * FROM DBA_TABLESPACES;

-- 表
select *
from user_tables ut LEFT JOIN user_tab_comments utc ON ut.TABLE_NAME = utc.TABLE_NAME;

-- 字段
select t.TABLE_NAME 表名,t.COLUMN_NAME 描述,t.DATA_TYPE 类型,t.DATA_LENGTH 长度,t.NULLABLE 是否为空,t.DATA_DEFAULT 默认值,s.comments 描述
from user_tab_columns t LEFT JOIN user_col_comments s ON t.COLUMN_NAME = s.COLUMN_NAME WHERE t.TABLE_NAME ='ASSET_BASE_TABLE';

-- 分区
SELECT * FROM USER_PART_KEY_COLUMNS;

-- 索引
SELECT * FROM USER_INDEXES;

-- 视图
SELECT * FROM USER_VIEWS;

-- 主键
select cu.* from USER_CONS_COLUMNS cu, USER_CONSTRAINTS au
where cu.constraint_name = au.constraint_name and au.constraint_type = 'P';

以上就是我个人查找元数据的流程，如有不对的地方，欢迎在评论区指正。

---