汇总数据库信息的存储过程
短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒
问题：
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
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
