因為在自測過程中,創(chuàng)建了很多數(shù)據(jù)庫,一個個手動刪除屬實有點對不起程序員這個身份,那么有沒有簡單的sql語句操作來進行批量刪除數(shù)據(jù)庫呢?于是便有了本篇文章
上面圖片是AI創(chuàng)作,未經(jīng)允許,不可商用哦!
前情提示
刪庫跑路需謹慎,
放棄一切亦不易。
思路
了解到數(shù)據(jù)庫或表的信息都保存在MySQL內置的 information_schema數(shù)據(jù)庫的SCHEMATA表中,因此是否可以通過like查詢information_schema中的相關表名,拼接SQL,進行批量刪除。
實操
批量刪除數(shù)據(jù)庫(以前綴為 ‘test_’ 示例)
SELECT CONCAT( 'DROP DATABASE ', SCHEMA_NAME, ';')
FROM information_schema.SCHEMATA
WHERE information_schema.SCHEMATA.SCHEMA_NAME LIKE 'test_%';
如下圖,查詢結果是拼接好的sql,CV一下,執(zhí)行即可
批量刪除表(以前綴為 ‘df_’ 示例)
SELECT CONCAT( 'DROP TABLE ', TABLE_NAME, ';')
FROM information_schema.TABLES
WHERE information_schema.TABLES.TABLE_NAME LIKE 'df_%';
如下圖,同樣查詢結果是拼接好的sql,CV一下,執(zhí)行即可
到這里已經(jīng)實現(xiàn)了批量刪除操作,下面是引發(fā)的探究,如時間緊張可先贊后看,保持良好習慣!
MySQL 5.7 自帶的四個數(shù)據(jù)庫
在Mysql5.7版本中自帶4個數(shù)據(jù)庫:information_schema、mysql、performance_schema、sys。
mysql
mysql的核心數(shù)據(jù)庫,類似于sql server中的master表,主要負責存儲數(shù)據(jù)庫的用戶、權限設置、關鍵字等mysql自己需要使用的控制和管理信息。(常用的如在mysql.user表中修改root用戶的密碼)
performance_schema
主要用于收集數(shù)據(jù)庫服務器性能參數(shù)。并且?guī)炖锉淼拇鎯σ婢鶠镻ERFORMANCE_SCHEMA,而用戶是不能創(chuàng)建存儲引擎為PERFORMANCE_SCHEMA的表。MySQL5.7默認是開啟的。
sys
sys庫所有的數(shù)據(jù)源來自:performance_schema。目標是把performance_schema的復雜度降低,讓DBA能更好的閱讀這個庫里的內容。讓DBA更快的了解DB的運行情況。
information_schema
提供了訪問數(shù)據(jù)庫元數(shù)據(jù)的方式。(元數(shù)據(jù)是關于數(shù)據(jù)的數(shù)據(jù),如數(shù)據(jù)庫名、表名、列的數(shù)據(jù)類型和訪問權限等。有時用于表述該信息的其他術語包括“數(shù)據(jù)詞典”和“系統(tǒng)目錄”) 該位置存儲有關MySQL服務器維護的所有其他數(shù)據(jù)庫的信息。
information_schema數(shù)據(jù)庫表簡介
該INFORMATION_SCHEMA數(shù)據(jù)庫包含幾個只讀表。它們實際上是視圖,而不是基表,因此沒有與它們關聯(lián)的文件,并且不能在它們上設置觸發(fā)器。另外,沒有使用該名稱的數(shù)據(jù)庫目錄。雖然你可以選擇INFORMATION_SCHEMA與一個默認的數(shù)據(jù)庫USE 語句,你只能讀取表的內容,不能執(zhí)行 INSERT、UPDATE或 DELETE。
對于大多數(shù)INFORMATION_SCHEMA表,每個MySQL用戶都有權訪問它們,但只能看到表中與用戶具有適當訪問權限的對象相對應的行。在某些情況下(例如,表中的ROUTINE_DEFINITION列 INFORMATION_SCHEMA ROUTINES),特權不足的用戶會看到NULL。某些表具有不同的特權要求;為此,在適用的表格說明中提到了這些要求。例如,InnoDB表(名稱以開頭的表INNODB_)需要PROCESS特權。
分表
information_schema庫共計有61張表。下面僅以批量刪除數(shù)據(jù)庫和表涉及到分表展開簡單介紹
SCHEMATA表
模式是數(shù)據(jù)庫,因此 SCHEMATA表提供了有關數(shù)據(jù)庫的信息。該SCHEMATA表包含以下列:
- CATALOG_NAME:模式所屬的目錄的名稱。此值始終為def
- SCHEMA_NAME:模式的名稱
- DEFAULT_CHARACTER_SET_NAME:架構默認字符集
- DEFAULT_COLLATION_NAME:模式默認排序規(guī)則
- SQL_PATH:此值始終為NULL
模式名稱也可以從該SHOW DATABASES語句中獲得:
mysql> show databases like ‘mysql’;
±-----------------+
| Database (mysql) |
±-----------------+
| mysql |
±-----------------+
1 row in set (0.00 sec)
————————————————
TABLES表
該TABLES表提供有關數(shù)據(jù)庫中表的信息。該TABLES表包含以下列:
TABLE_CATALOG:該表所屬的目錄的名稱。此值始終為def。
TABLE_SCHEMA:表所屬的模式(數(shù)據(jù)庫)的名稱。
TABLE_NAME:表的名稱。
TABLE_TYPE:BASE TABLE為一個表、 VIEW用于一個視圖、SYSTEM VIEW為一個INFORMATION_SCHEMA 表。該TABLES表未列出 TEMPORARY表。
ENGINE:表的存儲引擎。對于分區(qū)表,ENGINE顯示所有分區(qū)使用的存儲引擎的名稱。
VERSION:表.frm 文件的版本號。
ROW_FORMAT:該行的存儲格式(Fixed, Dynamic,Compressed, Redundant,Compact)。對于 MyISAM表,Dynamic 對應于myisamchk -dvv報告為的內容Packed。InnoDB表格式可以是Redundant或者
Compact使用時的 Antelope文件格式,或者 Compressed或Dynamic 使用時Barracuda的文件格式。TABLE_ROWS:行數(shù)。一些存儲引擎(例如 MyISAM)存儲準確的計數(shù)。對于其他存儲引擎(例如)InnoDB,該值是一個近似值,可能與實際值相差40%至50%。在這種情況下,請使用SELECT
COUNT(*)以獲得準確的計數(shù)。 TABLE_ROWS是NULL對 INFORMATION_SCHEMA表。
對于InnoDB表,行數(shù)只是SQL優(yōu)化中使用的粗略估計。(如果InnoDB表已分區(qū),則也是如此。)AVG_ROW_LENGTH:平均行長。
DATA_LENGTH 對于MyISAM,DATA_LENGTH 是數(shù)據(jù)文件的長度(以字節(jié)為單位)。 對于InnoDB,DATA_LENGTH
是為聚簇索引分配的大約空間量(以字節(jié)為單位)。具體來說,它是聚簇索引大?。ㄒ皂摓閱挝唬┏艘訧nnoDB頁面大小。MAX_DATA_LENGTH 對于MyISAM, MAX_DATA_LENGTH是數(shù)據(jù)文件的最大長度。給定使用的數(shù)據(jù)指針大小,這是表中可以存儲的數(shù)據(jù)字節(jié)總數(shù)。 未使用InnoDB。
INDEX_LENGTH 對于MyISAM,INDEX_LENGTH 是索引文件的長度(以字節(jié)為單位)。 對于InnoDB,INDEX_LENGTH
是為非聚簇索引分配的大約空間量(以字節(jié)為單位)。具體來說,它是非聚集索引大?。ㄒ皂摓閱挝唬┑目偤统艘?InnoDB頁面大小。DATA_FREE 已分配但未使用的字節(jié)數(shù)。 InnoDB表報告表所屬的表空間的可用空間。對于位于共享表空間中的表,這是共享表空間的可用空間。如果您使用多個表空間,并且表具有自己的表空間,則可用空間僅用于該表??捎每臻g是指完全可用范圍中的字節(jié)數(shù)減去安全裕量。即使可用空間顯示為0,只要不需要分配新的盤區(qū),也可以插入行。
對于NDB群集,DATA_FREE顯示磁盤上為磁盤上的磁盤數(shù)據(jù)表或碎片分配但未使用的空間。(該DATA_LENGTH列中報告了內存中數(shù)據(jù)資源的使用情況。)
對于分區(qū)表,此值僅是估計值,可能不是絕對正確。在這種情況下,獲取此信息的一種更準確的方法是查詢 INFORMATION_SCHEMA
PARTITIONS表,如本示例所示:
SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = ‘mydb’ AND TABLE_NAME = ‘mytable’;AUTO_INCREMENT:下一個AUTO_INCREMENT值。
CREATE_TIME:創(chuàng)建表的時間。
UPDATE_TIME 數(shù)據(jù)文件的最后更新時間。對于某些存儲引擎,此值為NULL。例如, InnoDB將多個表存儲在其 系統(tǒng)表空間中,并且數(shù)據(jù)文件時間戳不適用。即使 文件每次表模式與每個InnoDB在單獨的表 .ibd文件, 改變緩沖
可以延緩寫入到數(shù)據(jù)文件,因此,文件的修改時間是從最后插入,更新或刪除的時間不同。對于MyISAM,使用數(shù)據(jù)文件時間戳;但是,在Windows上,時間戳不會通過更新進行更新,因此該值不準確。
UPDATE_TIME顯示last UPDATE, INSERT或 DELETE對InnoDB未分區(qū)表執(zhí)行
的時間戳記值。對于MVCC,時間戳記值反映了
COMMIT時間,該時間被視為最后更新時間。重新啟動服務器或從InnoDB數(shù)據(jù)字典緩存中刪除表時,時間戳記不會保留。
該UPDATE_TIME列還顯示了分區(qū)InnoDB表的此信息。CHECK_TIME 上次檢查表的時間。并非所有存儲引擎這次都更新,在這種情況下,該值始終為 NULL。 對于分區(qū)InnoDB表, CHECK_TIME始終為 NULL。
TABLE_COLLATION:該表的默認排序規(guī)則。輸出沒有顯式列出表的默認字符集,但是排序規(guī)則名稱以字符集名稱開頭。
CHECKSUM:實時校驗和值(如果有)。
CREATE_OPTIONS 與一起使用的額外選項CREATE TABLE。 CREATE_OPTIONS顯示 partitioned表是否已分區(qū)。 CREATE_OPTIONS顯示ENCRYPTION為在每個表文件表空間中創(chuàng)建的表指定的 子句。
在禁用嚴格模式的情況下創(chuàng)建表時 ,如果不支持指定的行格式,則使用存儲引擎的默認行格式。表的實際行格式在ROW_FORMAT
列中報告。CREATE_OPTIONS顯示CREATE TABLE語句中指定的行格式。
更改表的存儲引擎時,不適用于新存儲引擎的表選項將保留在表定義中,以便在必要時將具有其先前定義的選項的表恢復到原始存儲引擎。該CREATE_OPTIONS列可能顯示保留的選項。TABLE_COMMENT:創(chuàng)建表時使用的注釋(或有關MySQL為什么無法訪問表信息的信息)文章來源:http://www.zghlxwxcb.cn/news/detail-795415.html
參考
MySQL之自帶四庫之information_schema庫文章來源地址http://www.zghlxwxcb.cn/news/detail-795415.html
到了這里,關于MySQL:想實現(xiàn)sql語句進行批量刪除數(shù)據(jù)庫或表,而引發(fā)的熬夜探究的文章就介紹完了。如果您還想了解更多內容,請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章,希望大家以后多多支持TOY模板網(wǎng)!