汇总数据库信息的存储过程
问题：
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();
mysql数据库运行结果如下：
本文来自博客园，作者：wanglifeng，转载请注明原文链接： https://www.cnblogs.com/wanglifeng717/p/15830527.html
