汇总数据库信息的存储过程
问题：
mysql日常开发过程中，数据库、表的很多信息分散在不同的工具和不同的界面中，来回切换查找非常麻烦。
解决方式：
基于这个问题，写了一个存储过程，将这些日常需要的信息集合在一个存储过程中，查询起来非常方便。
工具合集中写了其他存储过程的调用方式和说明，为的是方便解决其他问题，后续分享这些问题和解决方式。
1-- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng7172DROPPROCEDUREIFEXISTS show_db_info;
3
4 DELIMITER %%
5CREATEPROCEDURE show_db_info()
6 label:BEGIN
7-- ############################################################################
8-- ############ 打印一个库中每个表的各种信息 ##############
9-- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
10-- ############################################################################
11
12-- 设置库名
13SET@dbname=DATABASE();
14
15
16DROPTABLEifexists fk_tbl;
17CREATETABLEifNOTexists fk_tbl as
18SELECT
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"
30FROM
31 information_schema.TABLE_CONSTRAINTS t
32JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
33ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME
34AND t.TABLE_NAME = k.TABLE_NAME
35AND t.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
36WHERE t.CONSTRAINT_TYPE ="FOREIGN KEY"
37AND t.table_schema =DATABASE() ;
38
39DROPTABLEifEXISTS index_tbl;
40CREATEtableifnotexists index_tbl as
41SELECT
42IF(
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 ORDERBY 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 GROUPBY i.index_name) AS"bind_col"
52FROM
53 information_schema.statistics s
54WHERE table_schema =DATABASE();
55
56-- SELECT * FROM index_tbl;
57
58
59
60
61-- ########################################################################
62-- 打印表信息
63-- ########################################################################
64
65SET@help_code="
66-- 表注释: @tbl_comment
67
68-- 字段列表：@allColumnList
69
70call insert_code_generator("@in_var_tbl_name");
71
72call select_code_generator("@in_var_tbl_name","list|obj","static_query_col","dynamic_query_col","list_query_col");
73
74call update_code_generator("@in_var_tbl_name","update_col","dynamic_update_col","static_query_col","dynamic_query_col","list_query_col");
75
76call tbl_query("@in_var_tbl_name","*"," where 1=1 limit 50 ","32");
77
78call insert_sql_generator("@in_var_tbl_name","exclude_col_list");
79
80call delete_drop_sql_generator("WHERE "2021-05-1922:19:56.724" <= LEFT(create_time,23) and LEFT(create_time,23)<="2021-05-1922:19:56.728"","include_tbl_list","exclude_tbl_list","0");
81/*
82方法使用帮助：
83-- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
84insert代码生成器用法:
85 insert_code_generator( in_var_tbl_name[要插入的表名] )
86
87select代码生成器用法:
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
96update代码生成器用法:
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
113insert_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
118delete_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-- 表名，注释名
131SELECT
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 (SELECTMAX(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 (
168SELECT
169 GROUP_CONCAT( CONCAT(" | ",index_type,": ","(",bind_col,")")SEPARATOR "")
170FROM 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 (
177SELECT
178 CONCAT(" | 外键: ",come_from)
179FROM fk_tbl f WHERE f.tbl_name =t.TABLE_NAME AND f.fk_col=t.COLUMN_NAME
180 )
181
182 ,""")
183-- -----------------------------------------------------
184 ) ORDERBY t.ORDINAL_POSITION SEPARATOR ",
"
185 ),"
}"
186 )
187FROM information_schema.columns t
188WHERE t.TABLE_SCHEMA =DATABASE() and t.TABLE_NAME=s.table_name
189 ) AS"字段列表",
190REPLACE(
191replace(
192REPLACE(@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"记录数",
200TRUNCATE(s.data_length /1024/1024, 2) AS"数据容量(MB)",
201TRUNCATE(s.index_length /1024/1024, 2) AS"索引容量(MB)",
202 s.create_time AS"创建时间"
203FROM
204 information_schema.tables s
205JOIN (SELECT@row_id:=0) temp
206WHERE s.table_schema =@dbname
207AND s.table_name NOTIN ("index_tbl","fk_tbl")
208ORDERBY s.data_length DESC,s.index_length DESC ;
209
210
211
212-- ########################################################################
213-- 外键约束
214-- ########################################################################
215
216
217SELECT
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"来自"
224FROM
225 information_schema.TABLE_CONSTRAINTS t
226JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
227JOIN (SELECT@row_id:=0) temp
228ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME
229AND t.TABLE_NAME = k.TABLE_NAME
230AND t.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
231WHERE t.CONSTRAINT_TYPE ="FOREIGN KEY"
232AND t.table_schema =@dbnameAND t.table_name NOTIN ("index_tbl","fk_tbl");
233
234
235-- ########################################################################
236-- 索引列表
237-- ########################################################################
238
239SELECT
240 (@row_id:=@row_id+1) AS"序号",t.*
241FROM
242(
243SELECT
244IF(non_unique =0,"unique","normal_index") AS"索引类型",
245 TABLE_NAME AS"表名",
246 index_name AS"索引名",
247 GROUP_CONCAT(column_name ORDERBY seq_in_index) AS"相关列"
248FROM
249 information_schema.statistics
250WHERE table_schema =@dbnameAND table_name IN (SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA =@dbname)
251AND table_name NOTIN ("index_tbl","fk_tbl")
252GROUPBY TABLE_NAME, INDEX_NAME
253 ) t
254JOIN (SELECT@row_id:=0) temp;
255
256
257DROPTABLEifEXISTS index_tbl;
258DROPTABLEifexists fk_tbl;
259
260
261END%%
262DELIMITER ;
263
264 CALL show_db_info();
show_db_info存储过程
mysql数据库运行结果如下：
本文来自云海天，作者：wanglifeng，转载请注明原文链接： https://www.cnblogs.com/wanglifeng717/p/15830527.html
以上是 汇总数据库信息的存储过程 的全部内容， 来源链接： utcz.com/z/536256.html
