汇总数据库信息的存储过程

问题：

　　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 
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      
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();

mysql数据库运行结果如下：

 本文来自云海天，作者：wanglifeng，转载请注明原文链接： https://www.cnblogs.com/wanglifeng717/p/15830527.html