MySQL、Oracle後設資料抽取分析

這行程式碼沒Bug發表於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: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';

以上就是我個人查詢後設資料的流程,如有不對的地方,歡迎在評論區指正。

相關文章