汇总数据库信息的存储过程[通俗易懂]

  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("",""字段注释："{
",
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 ",
"
185                 ),"
}"
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();