問題：
mysql日常開發程序中，資料庫、表的很多資訊分散在不同的工具和不同的界面中，來回切換查找非常麻煩，
解決方式：
基于這個問題，寫了一個存盤程序，將這些日常需要的資訊集合在一個存盤程序中，查詢起來非常方便，
工具合集中寫了其他存盤程序的呼叫方式和說明，為的是方便解決其他問題，后續分享這些問題和解決方式，
1 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 2 DROP PROCEDURE IF EXISTS show_db_info; 3 4 DELIMITER %% 5 CREATE PROCEDURE show_db_info() 6 label:BEGIN 7 -- ############################################################################ 8 -- ############ 列印一個庫中每個表的各種資訊 ############## 9 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 10 -- ############################################################################ 11 12 -- 設定庫名 13 SET @dbname= DATABASE(); 14 15 16 DROP TABLE if exists fk_tbl; 17 CREATE TABLE if NOT exists fk_tbl as 18 SELECT 19 t.CONSTRAINT_TYPE AS 'fk', 20 t.TABLE_NAME AS 'tbl_name', 21 t.CONSTRAINT_NAME AS 'fk_name', 22 k.COLUMN_NAME AS 'fk_col', 23 CONCAT( 24 '來自 ', 25 k.REFERENCED_TABLE_NAME, 26 ' 表 (', 27 k.REFERENCED_COLUMN_NAME, 28 ')' 29 ) AS 'come_from' 30 FROM 31 information_schema.TABLE_CONSTRAINTS t 32 JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k 33 ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME 34 AND t.TABLE_NAME = k.TABLE_NAME 35 AND t.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA 36 WHERE t.CONSTRAINT_TYPE = 'FOREIGN KEY' 37 AND t.table_schema = DATABASE() ; 38 39 DROP TABLE if EXISTS index_tbl; 40 CREATE table if not exists index_tbl as 41 SELECT 42 IF( 43 non_unique = 0, 44 '唯一鍵', 45 '索引' 46 ) AS 'index_type', 47 TABLE_NAME AS 'tbl_name', 48 index_name AS 'index_name', 49 column_name AS 'index_col', 50 s.SEQ_IN_INDEX AS 'order', 51 (SELECT GROUP_CONCAT(i.column_name ORDER BY i.seq_in_index) FROM information_schema.statistics i WHERE i.table_schema = DATABASE() and i.table_name=s.table_name AND i.index_name=s.index_name GROUP BY i.index_name) AS 'bind_col' 52 FROM 53 information_schema.statistics s 54 WHERE table_schema = DATABASE(); 55 56 -- SELECT * FROM index_tbl; 57 58 59 60 61 -- ######################################################################## 62 -- 列印表資訊 63 -- ######################################################################## 64 65 SET @help_code=' 66 -- 表注釋: @tbl_comment 67 68 -- 欄位串列：@allColumnList 69 70 call insert_code_generator("@in_var_tbl_name"); 71 72 call select_code_generator("@in_var_tbl_name","list|obj","static_query_col","dynamic_query_col","list_query_col"); 73 74 call update_code_generator("@in_var_tbl_name","update_col","dynamic_update_col","static_query_col","dynamic_query_col","list_query_col"); 75 76 call tbl_query("@in_var_tbl_name","*"," where 1=1 limit 50 ","32"); 77 78 call insert_sql_generator("@in_var_tbl_name","exclude_col_list"); 79 80 call delete_drop_sql_generator("WHERE \'2021-05-19 22:19:56.724\' <= LEFT(create_time,23) and LEFT(create_time,23)<=\'2021-05-19 22:19:56.728\'","include_tbl_list","exclude_tbl_list","0"); 81 /* 82 方法使用幫助： 83 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717 84 insert代碼生成器用法: 85 insert_code_generator( in_var_tbl_name[要插入的表名] ) 86 87 select代碼生成器用法: 88 select_code_generator 89 ( in_var_tbl_name [要查詢的表] 90 ,in_var_return_type [回傳型別，list|ojb] 91 ,in_static_col_list [靜態查詢條件,欄位串列,形如："id,name,code"] 92 ,in_dynamic_col_list [動態查詢條件,欄位串列,形如："id,name,code"] 93 ,in_list_col_list [動態集合in查詢條件欄位串列,形如："id,name,code"] 94 ) 95 96 update代碼生成器用法: 97 update_code_generator 98 ( in_var_tbl_name [要查詢的表] 99 ,in_var_update_col_list [要更新的欄位串列] 100 ,in_var_dynamic_update_col_list [要動態更新的欄位串列] 101 ,in_static_col_list [靜態查詢條件,欄位串列,形如："id,name,code"] 102 ,in_dynamic_col_list [動態查詢條件,欄位串列,形如："id,name,code"] 103 ,in_list_col_list [動態集合in查詢條件欄位串列,形如："id,name,code"] 104 ) 105 106 高級查詢工具用法： 107 tbl_query 108 ( in_var [要查詢的表] 109 ,in_col [需要查詢的欄位,*代表全部,可定制,形如："id,name,code"] 110 ,in_where [where條件,支持limit] 111 ,in_sub_limit [子查詢limit限制條數] ) 112 113 insert_sql生成器用法： 114 insert_sql_generator 115 ( tbl_name_list [要生成insert-sql的表名串列：例如："tbl_name1,tbl_name2,tbl_name3"] 116 ,exclude_col_list [不需要列印的欄位串列： 例如："name,code,id"] ) 117 118 delete_drop_sql生成器用法： 119 delete_drop_sql_generator 120 (var_where [where條件,例如：" where LEFT(CREATE_time,19)>\'2021-08-04\'"] 121 ,var_include_tbl_list [要包含的表名串列,優先于var_exclude_tbl_list,例如："tbl_name1,tbl_name2"] 122 ,var_exclude_tbl_list [要排除的表名串列,僅在var_include_tbl_list為空時生效,例如："tbl_name1,tbl_name2"] 123 ,var_greater_than_value[符合where條件要過濾的值,count(*)>=0 ] 124 ) 125 */ 126 127 128 '; 129 130 -- 表名，注釋名 131 SELECT 132 (@row_id:=@row_id +1) AS '序號', 133 s.table_name AS '表名', 134 s.table_comment AS '注釋', 135 136 /* 137 -- 格式化對齊輸出所有欄位的注釋 138 "欄位注釋："{ 139 "id" : "物理主鍵（自增）", 140 "name" : "物品名稱", 141 "description" : "描述", 142 "status" : "狀態（1有效，0無效，2初始狀態）", 143 "create_time" : "創建時間", 144 "last_update_time" : "更新時間"} 145 */ 146 (SELECT 147 CONCAT_WS('','"欄位注釋："{\r\n', 148 GROUP_CONCAT( 149 CONCAT_WS('', 150 CONCAT_WS( '','"',t.COLUMN_NAME,'":') 151 ,repeat(' ', 152 ( 153 (SELECT MAX(length(CONCAT_WS( '','"',s.COLUMN_NAME,'"'))) FROM information_schema.columns s WHERE s.TABLE_SCHEMA= DATABASE() and s.TABLE_NAME=t.TABLE_NAME) 154 - 155 LENGTH(CONCAT_WS( '','"',t.COLUMN_NAME,'"')) 156 ) 157 ) 158 /*第三列：注釋*/ 159 , 160 -- "tbl_cbm_user_info的uuid | varchar(64) | 非空 | 默認值: null | 字符編碼: utf8mb4 | 校驗編碼: utf8mb4_bin | 索引 | idx_ali_user_info_user_uuid(user_uuid)" 161 CONCAT_WS('','"',t.column_comment,' | ',t.COLUMN_TYPE,' | ',if(t.is_nullable='YES','可空','非空'),case t.COLUMN_KEY when 'PRI' then ' | 主鍵' else '' END 162 ,CONCAT_WS('',' | 默認值: ',IFNULL(t.COLUMN_DEFAULT,'null')) 163 ,CONCAT('',' | 字符編碼: ',t.CHARACTER_SET_NAME) 164 ,CONCAT('',' | 校驗編碼: ',t.COLLATION_NAME) 165 ,( 166 IFNULL( 167 ( 168 SELECT 169 GROUP_CONCAT( CONCAT(' | ',index_type,': ','(',bind_col,')')SEPARATOR '') 170 FROM index_tbl i WHERE i.tbl_name =t.TABLE_NAME AND i.index_col =t.COLUMN_NAME 171 ) 172 ,' | 無索引') 173 ) 174 , 175 -- | 外鍵: fk_ali_user_info_user_uuid來自 tbl_cbm_user_info 表 (uuid) 176 ( 177 SELECT 178 CONCAT(' | 外鍵: ',come_from) 179 FROM fk_tbl f WHERE f.tbl_name =t.TABLE_NAME AND f.fk_col=t.COLUMN_NAME 180 ) 181 182 ,'"') 183 -- ----------------------------------------------------- 184 ) ORDER BY t.ORDINAL_POSITION SEPARATOR ',\r\n' 185 ),'\r\n}' 186 ) 187 FROM information_schema.columns t 188 WHERE t.TABLE_SCHEMA = DATABASE() and t.TABLE_NAME=s.table_name 189 ) AS '欄位串列', 190 REPLACE( 191 replace( 192 REPLACE(@help_code,'@tbl_comment',s.TABLE_COMMENT) 193 ,'@in_var_tbl_name' 194 ,s.TABLE_NAME 195 ) 196 ,'@allColumnList' 197 ,(SELECT GROUP_CONCAT(t.COLUMN_NAME) FROM information_schema.columns t WHERE t.TABLE_SCHEMA= DATABASE() and t.TABLE_NAME=s.TABLE_NAME) 198 ) AS '工具合集', 199 s.table_rows AS '記錄數', 200 TRUNCATE(s.data_length / 1024 / 1024, 2) AS '資料容量(MB)', 201 TRUNCATE(s.index_length / 1024 / 1024, 2) AS '索引容量(MB)', 202 s.create_time AS '創建時間' 203 FROM 204 information_schema.tables s 205 JOIN (SELECT @row_id:=0) temp 206 WHERE s.table_schema = @dbname 207 AND s.table_name NOT IN ('index_tbl','fk_tbl') 208 ORDER BY s.data_length DESC,s.index_length DESC ; 209 210 211 212 -- ######################################################################## 213 -- 外鍵約束 214 -- ######################################################################## 215 216 217 SELECT 218 (@row_id:=@row_id +1) AS '序號', 219 t.CONSTRAINT_TYPE AS '約束型別', 220 t.TABLE_NAME AS '表名', 221 t.CONSTRAINT_NAME AS '約束名', 222 k.COLUMN_NAME AS '相關列', 223 CONCAT('來自 ', k.REFERENCED_TABLE_NAME, ' 表 (', k.REFERENCED_COLUMN_NAME, ')' ) AS '來自' 224 FROM 225 information_schema.TABLE_CONSTRAINTS t 226 JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k 227 JOIN (SELECT @row_id:=0) temp 228 ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME 229 AND t.TABLE_NAME = k.TABLE_NAME 230 AND t.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA 231 WHERE t.CONSTRAINT_TYPE = 'FOREIGN KEY' 232 AND t.table_schema = @dbname AND t.table_name NOT IN ('index_tbl','fk_tbl'); 233 234 235 -- ######################################################################## 236 -- 索引串列 237 -- ######################################################################## 238 239 SELECT 240 (@row_id:=@row_id +1) AS '序號',t.* 241 FROM 242 ( 243 SELECT 244 IF(non_unique = 0,'unique','normal_index') AS '索引型別', 245 TABLE_NAME AS '表名', 246 index_name AS '索引名', 247 GROUP_CONCAT(column_name ORDER BY seq_in_index) AS '相關列' 248 FROM 249 information_schema.statistics 250 WHERE table_schema = @dbname AND table_name IN (SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA = @dbname) 251 AND table_name NOT IN ('index_tbl','fk_tbl') 252 GROUP BY TABLE_NAME, INDEX_NAME 253 ) t 254 JOIN (SELECT @row_id:=0) temp; 255 256 257 DROP TABLE if EXISTS index_tbl; 258 DROP TABLE if exists fk_tbl; 259 260 261 END %% 262 DELIMITER ; 263 264 CALL show_db_info();show_db_info存盤程序
mysql資料庫運行結果如下：
本文來自博客園，作者：wanglifeng，轉載請注明原文鏈接： https://www.cnblogs.com/wanglifeng717/p/15830527.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/420435.html
標籤：MySQL
上一篇：資料庫相關作業流程與工具
下一篇：insert陳述句生成的存盤程序