百木园问题：
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();
来源：https://www.cnblogs.com/wanglifeng717/p/15830527.html
图文来源于网络，如有侵权请联系删除。