MySQL、Oracle元數據抽取分析

2021 年 12 月 22 日
筆記
亂七

最近接到個任務是抽取mysql和Oracle的元數據，大致就是在庫里把庫、schema、表、欄位、分區、索引、主鍵等資訊抽取出來，然後導成excel。

因為剛開始接觸元數據，對這個並不了解，就想藉助一下萬能的百度，結果百度了一圈全是告訴我sql，直接cv就行了。雖然得到了部分數據，但是查的哪個庫，哪個表我是完全不清楚的，得到的數據也不是我想要的，只有自己去官網看文檔來完成自己的任務。

授人以魚不如授人以漁，把經驗寫出來，給跟我一樣疑惑的小夥伴一個參考。

什麼是元數據

百度百科：元數據（Metadata），又稱中介數據、中繼數據，為描述數據的數據（data about data），主要是描述數據屬性（property）的資訊，用來支援如指示存儲位置、歷史數據、資源查找、文件記錄等功能。元數據算是一種電子式目錄，為了達到編製目錄的目的，必須在描述並收藏數據的內容或特色，進而達成協助數據檢索的目的。都柏林核心集（Dublin Core Metadata Initiative，DCMI）是元數據的一種應用，是1995年2月由國際圖書館電腦中心（OCLC）和美國國家超級計算應用中心（National Center for Supercomputing Applications，NCSA）所聯合贊助的研討會，在邀請52位來自圖書館員、電腦專家，共同制定規格，創建一套描述網路上電子文件之特徵。

元數據是關於數據的組織、數據域及其關係的資訊，簡言之，元數據就是關於數據的數據。

參考文檔地址

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

Oracle://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';

以上就是我個人查找元數據的流程，如有不對的地方，歡迎在評論區指正。

Tags: 亂七