省去跨表联查与注释查询的存储过程

 问题：

　　传统的select * from table 存在以下几个问题：

        1.字段含义及“魔法数字”需要另开窗口对照查看，例如status有1,2,3,4,5

        2.外键字段引用的是对方表的id，要知道id对应信息，需要另开窗口查询，或者写跨表联查语句。例如：该订单对应的合作方具体信息是什么

        3.该记录被其他表引用了的情况也需要另外进行查询。例如：该合作方有多少订单及详情。

 解决方案：

　　编写存储过程。

　　1.将字段comment内容输出在表头位置；

　　2.对于该表中的外键字段，去对应的表中查出外键对应的记录详情展示出来；

　　3.其他表当做外键引用了该表时，将这些表表中的记录查出来，兼顾效率in_sub_limit 限制结果集，因为：该表可能被被其他N个表当外键引用，每个表对应又有百万条记录；

　　总结：去除跨表联查，正向反向被引用的记录查出来。

高级查询工具用法： tbl_query ( in_var                       [要查询的表] ,in_col                         [需要查询的字段,*代表全部,可定制,形如：\"id,name,code\"] ,in_where                    [where条件,支持limit] ,in_sub_limit                [子查询limit限制条数] )

1 -- 打印query存储过程的帮助信息
2 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
3 DROP PROCEDURE IF EXISTS tbl_query_help;
4 DELIMITER %%
5 CREATE PROCEDURE tbl_query_help()
6 BEGIN
7 CALL tbl_query(\'\',\'\',\'\',\'\');
8 END %%
9 DELIMITER ;
10
11
12 -- --------------------------------------------------------------------------
13 -- 作者：王李峰
14 -- 功能：高级查询，去除跨表联查
15 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
16 -- --------------------------------------------------------------------------
17 DROP PROCEDURE IF EXISTS tbl_query;
18 DELIMITER %%
19 CREATE PROCEDURE tbl_query( in_var VARCHAR ( 255 ),in_col VARCHAR(1024),in_where VARCHAR(1024) ,in_sub_limit VARCHAR(1024))
20 label:BEGIN
21
22 SET group_concat_max_len = 4294967295;
23
24 DROP TABLE IF EXISTS
25 test_main_tbl_col,
26 test_child_tbl,
27 test_child_tbl_col;
28
29 SET @main_tbl = in_var;
30 SET @in_sub_limit=in_sub_limit;
31 SET @main_tbl_if_all = \'yes\';
32 SET @in_where=in_where;
33
34 -- 如果输入的表名是空，则打印帮助信息-----------------------------------------------------------------------------------------------------------
35 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
36 if(@main_tbl IS NULL OR @main_tbl=\'\') then
37 SELECT \'作者：王李峰\' AS col, \'功能：高级查询，无须跨表联查\' AS col ,\'前提：要有外键，该工具自动匹配外键进行\' AS col
38 UNION all
39 SELECT \'\' AS col ,\'\' AS col ,\'\' AS col
40 UNION ALL
41 SELECT \'全部表tbl_query语句\' AS col, \'参数提示\',\'\' AS col
42 UNION all
43 SELECT
44 (
45 SELECT GROUP_CONCAT(
46 CONCAT_WS(\'\'
47 ,\'-- 表注释: \',t1.TABLE_COMMENT,\'\\r\\n\'
48 ,\'-- 用法: tbl_query( in_var[要查询的表] ,in_col[需要查询的字段] ,in_where[where条件,支持limit] ,in_sub_limit[子查询limit限制条数] ) \\r\\n\'
49 ,\'-- 字段列表: \',((SELECT GROUP_CONCAT(t.COLUMN_NAME) FROM information_schema.columns t WHERE t.TABLE_SCHEMA= DATABASE() and t.TABLE_NAME=t1.TABLE_NAME)),\'\\r\\n\'
50 ,\'call tbl_query(\\\'\',t1.TABLE_NAME,\"\',\'*\',\' where 1=1 limit 50\',\'32\'\",\');\')
51 SEPARATOR \'\\r\\n\\r\\n\')
52 FROM
53 information_schema.tables t1
54 WHERE
55 t1.table_schema= DATABASE()
56 ) AS col
57 ,\'tbl_query( in_var[要查询的表] ,in_col[需要查询的字段] ,in_where[where条件,支持limit] ,in_sub_limit[子查询limit限制条数] ) \' AS col
58 ,\'ps:1.in_sub_limit参数子查询中limit限制数字一般是8的倍数，例如：32 \\r\\n2.in_where参数中支持针对in_var参数输入的表自定义where语句\' AS col
59 UNION all
60 SELECT \'\' AS col ,\'\' AS col ,\'\' AS col
61 UNION ALL
62 SELECT \'表名\' AS col ,\'调用方式\' AS col ,\'表所有字段注释\' AS col
63 UNION ALL
64 SELECT
65 t1.TABLE_NAME
66 /*
67 -- 定义活动物品，可以是虚拟的也可以是现实的物
68 -- id,name,description,status,create_time,last_update_time
69 call query(\'tbl_ams_activity_item\',\'*\',\' where 1=1 limit 50\',\' 50\');
70 */
71 ,(
72 SELECT
73 CONCAT_WS(\'\'
74 ,\'-- 表注释: \',t2.TABLE_COMMENT,\'\\r\\n\'
75 ,\'-- 用法: tbl_query( in_var[要查询的表] ,in_col[需要查询的字段] ,in_where[where条件,支持limit] ,in_sub_limit[子查询limit限制条数] ) \\r\\n\'
76 ,\'-- 字段列表: \',((SELECT GROUP_CONCAT(t.COLUMN_NAME) FROM information_schema.columns t WHERE t.TABLE_SCHEMA= DATABASE() and t.TABLE_NAME=t2.TABLE_NAME)),\'\\r\\n\'
77 ,\'call tbl_query(\\\'\',t2.TABLE_NAME,\"\',\'*\',\' where 1=1 limit 50\',\'32\'\",\');\')
78 FROM
79 information_schema.tables t2
80 WHERE
81 t2.table_schema= DATABASE() AND t2.TABLE_NAME=t1.TABLE_NAME
82 )
83 /*
84 -- 格式化对齐输出所有字段的注释
85 \"字段注释：\"{
86 \"id\" : \"物理主键（自增）\",
87 \"name\" : \"物品名称\",
88 \"description\" : \"描述\",
89 \"status\" : \"状态（1有效，0无效，2初始状态）\",
90 \"create_time\" : \"创建时间\",
91 \"last_update_time\" : \"更新时间\"}
92 */
93 ,(SELECT
94 CONCAT_WS(\'\',\'\"字段注释：\"{\\r\\n\',
95 GROUP_CONCAT(
96 CONCAT_WS(\'\',
97 CONCAT_WS( \'\',\'\"\',t.COLUMN_NAME,\'\"\')
98 ,repeat(\' \',
99 (
100 (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)
101 -
102 LENGTH(CONCAT_WS( \'\',\'\"\',t.COLUMN_NAME,\'\"\'))
103 )
104 )
105 /*第三列：注释*/
106 ,CONCAT_WS(\'\',\' : \"\',t.COLUMN_COMMENT,\'\"\')
107 -- -----------------------------------------------------
108 )
109 ORDER BY t.ORDINAL_POSITION SEPARATOR \',\\r\\n\'
110 ),\'\\r\\n}\'
111 )
112 FROM information_schema.columns t
113 WHERE t.TABLE_SCHEMA = DATABASE() and t.TABLE_NAME=t1.TABLE_NAME
114 )
115 FROM information_schema.tables t1
116 WHERE t1.table_schema= DATABASE() ;
117 -- 打印完帮助信息，直接跳出程序
118 leave label;
119 END if;
120
121
122 -- -- 判断自定义字段------------------------------------------
123 SET @in_col=in_col;
124 -- 如果输入为空，则是默认全部字段
125 if(@in_col IS NULL OR @in_col=\'\' OR @in_col=\'\\t\' OR @in_col=\'*\'OR @in_col=\' *\' OR @in_col=\'* \' OR @in_col=\' * \' ) then -- 防止用户无效输入,这个匹配\'\',两个单引号之间有任意个空格都能匹配
126 SET @main_tbl_if_all = \'yes\';
127 ELSE
128 SET @main_tbl_if_all = \'no\';
129 -- 输入id,name,code 转换为：\'id\',\'name\',\'code\'
130 SET @user_define_main_tbl_cols =CONCAT_WS(\'\',\"\'\",REPLACE(@in_col,\',\',\"\',\'\"),\"\'\");
131 END if;
132
133 -- SELECT @user_define_main_tbl_cols;
134
135
136 -- 建表---------------------------------------------------------------------
137
138 CREATE TABLE IF NOT EXISTS `test_child_tbl` (
139 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT \'主键\',
140 `main_tbl_name` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT \'主表名\',
141 `child_tbl_name` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT \'字表名\',
142 `main_tbl_foreign_key` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT \'主表外键字段名，如user_uuid\',
143 `ref_child_tbl_id` varchar(256) COLLATE utf8_bin DEFAULT NULL COMMENT \'字表中被引用的键,如，user表中uuid\',
144 PRIMARY KEY (`id`),
145 UNIQUE KEY `uni_test_child_tbl` (`main_tbl_name`,`main_tbl_foreign_key`)
146 ) COMMENT=\'测试：有哪些子表，即外键关联到的表\';
147
148 CREATE TABLE IF NOT EXISTS `test_child_tbl_col` (
149 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT \'主键\',
150 `child_tbl_name` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT \'字表名\',
151 `col` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT \'子表需要展示的字段\',
152 `col_desc` VARCHAR(500) COLLATE utf8_bin DEFAULT NULL COMMENT \'字段注释\',
153 PRIMARY KEY (`id`),
154 UNIQUE KEY `uni_test_child_tbl_col` (`child_tbl_name`,`col`)
155 ) COMMENT=\'测试：字表中要展示的字段\';
156
157 CREATE TABLE IF NOT EXISTS `test_main_tbl_col` (
158 `id` int(10) NOT NULL AUTO_INCREMENT COMMENT \'主键\',
159 `main_tbl_name` varchar(200) COLLATE utf8_bin NOT NULL COMMENT \'主表名\',
160 `col` varchar(200) COLLATE utf8_bin NOT NULL COMMENT \'主表需要展示的字段\',
161 PRIMARY KEY (`id`),
162 UNIQUE KEY `uni_test_main_tbl_col` (`main_tbl_name`,`col`)
163 ) COMMENT=\'测试：主表中需要查询哪些字段\';
164
165
166 -- 默认插入主表所有字段sql
167 SET @insert_tbl_all_cols =
168 \'INSERT ignore INTO test_main_tbl_col(main_tbl_name,col)
169 SELECT @main_tbl,t1.COLUMN_NAME
170 FROM information_schema.COLUMNS t1
171 WHERE
172 t1.table_schema= DATABASE() AND
173 t1.TABLE_NAME =@main_tbl\';
174
175 -- 判断并插入：是默认插入所有字段，还是插入自定义字段
176 SELECT
177 IF
178 (
179 -- 如果是yes插入所有字段
180 @main_tbl_if_all = \'yes\',
181 CONCAT_WS( \'\', @insert_tbl_all_cols, \' order by t1.ORDINAL_POSITION \' ),
182 -- 如果非yes，插入自定义字段
183 -- insert ingnore into test_main_tbl_col(main_tbl_name,col) values (\'tbl_ams_activity_info\',\'id\'),(\'tbl_ams_activity_info\',\'name\'),(\'tbl_ams_activity_info\',\'code\')
184 CONCAT_WS(\'\',\'insert ignore into test_main_tbl_col(main_tbl_name,col) values (\',\"\'\",@main_tbl,\"\',\",REPLACE(@user_define_main_tbl_cols, \',\',CONCAT_WS(\'\',\'),(\',\"\'\",@main_tbl,\"\'\",\",\")),\')\')
185 )
186 INTO @insert_main_tbl_col;
187
188 PREPARE stmt FROM @insert_main_tbl_col;
189 EXECUTE stmt;
190 DEALLOCATE PREPARE stmt;
191
192
193 -- select @insert_main_tbl_col;
194
195 -- 插入子表：按照外键找出所有子表名及关联的外键等字段
196 INSERT IGNORE INTO test_child_tbl ( main_tbl_name, child_tbl_name, main_tbl_foreign_key, ref_child_tbl_id )
197 SELECT *
198 FROM
199 (
200 SELECT
201 t.TABLE_NAME AS tbl_name,
202 k.REFERENCED_TABLE_NAME AS rf_name,
203 k.column_name AS col_name,
204 k.REFERENCED_COLUMN_NAME AS rf_col
205 FROM
206 information_schema.TABLE_CONSTRAINTS t
207 JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME
208 AND t.TABLE_NAME = k.TABLE_NAME
209 AND t.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
210 WHERE
211 t.CONSTRAINT_TYPE = \'FOREIGN KEY\'
212 AND t.table_schema = DATABASE()
213 AND k.CONSTRAINT_SCHEMA=DATABASE()
214 AND t.TABLE_NAME = @main_tbl
215 ) t;
216
217 -- 把所有子表的所有col字段出并插入
218 INSERT IGNORE INTO test_child_tbl_col ( child_tbl_name, col, col_desc )
219 SELECT
220 t1.TABLE_NAME,
221 t1.COLUMN_NAME,
222 t1.COLUMN_COMMENT
223 FROM
224 information_schema.COLUMNS t1
225 WHERE
226 t1.table_schema = DATABASE ( )
227 AND t1.TABLE_NAME IN ( SELECT DISTINCT child_tbl_name FROM test_child_tbl )
228 ORDER BY t1.ORDINAL_POSITION;
229
230 -- ---------------------------------------------------------------------------------------------------------------------------------
231 -- ---------------------------------------------------------------------------------------------------------------------------------
232 -- ---------------------------------------------------------------------------------------------------------------------------------
233 -- ---------------------------------------------------------------------------------------------------------------------------------
234 -- ---------------------------------------------------------------------------------------------------------------------------------
235 -- ---------------------------------------------------------------------------------------------------------------------------------
236
237
238
239
240 -- set @main_tbl=\'tbl_ams_user_behavior\';
241 -- set @main_tbl=\'tbl_ams_raffle_activity\';
242 -- set @main_tbl=\'tbl_ams_user_specific_behavior\';
243 -- 根据外键找到主表被哪些表引用了。就是一对多的情况
244 SELECT
245 group_concat(CONCAT_WS(\'\',\' @row_num_\',tbl_name,\'_\',col_name,\':= 0 , \') SEPARATOR \' \') AS \'@row_num_fk_tbl_name\'
246 ,group_concat(CONCAT_WS(\'\',\' , \',tbl_name,\'_\',col_name ) SEPARATOR \' \') AS \'@fk_tbl_name\'
247 ,if(group_concat(CONCAT_WS(\'\',\' %\',tbl_name,\'_\',col_name,\'% as \',tbl_name,\'_\',col_name ) SEPARATOR \' , \') IS NULL,\'\',CONCAT_WS(\'\',\' , \',group_concat(CONCAT_WS(\'\',\' %\',tbl_name,\'_\',col_name,\'% as \',tbl_name,\'_\',col_name ) SEPARATOR \' , \'))) AS \'@fk_tbl_name_as\'
248 ,group_concat(CONCAT_WS(\'\',\",\'被\",tbl_name,\' : \',col_name,\" 引用 : \",tbl_comment,\"\'\") SEPARATOR \' \') AS \'@fk_tbl_name_ref_header\'
249 ,group_concat(CONCAT_WS(\'\',\'@var_fk_\',tbl_name,\':=\',rf_name,\'.\',rf_col,\' , \') SEPARATOR \' \') AS \'@var_fk_tbl_name\'
250 INTO @row_num_fk_tbl_name,@fk_tbl_name,@fk_tbl_name_as,@fk_tbl_name_ref_header,@var_fk_tbl_name
251 FROM
252 (SELECT
253 t.TABLE_NAME AS tbl_name,
254 k.column_name AS col_name,
255 k.REFERENCED_TABLE_NAME AS rf_name,
256 k.REFERENCED_COLUMN_NAME AS rf_col,
257 tb.TABLE_COMMENT AS tbl_comment
258 FROM
259 information_schema.TABLE_CONSTRAINTS t
260 JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
261 ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME
262 JOIN INFORMATION_SCHEMA.tables tb
263 ON tb.TABLE_NAME=t.TABLE_NAME
264 AND t.TABLE_NAME = k.TABLE_NAME
265 AND t.CONSTRAINT_SCHEMA=k.CONSTRAINT_SCHEMA
266 AND tb.TABLE_SCHEMA=DATABASE() AND k.CONSTRAINT_SCHEMA=DATABASE()
267 WHERE t.CONSTRAINT_TYPE=\'FOREIGN KEY\' AND t.table_schema =DATABASE() AND k.REFERENCED_TABLE_NAME=@main_tbl
268 ) t;
269
270
271
272 -- 主语句表头：前半部分 -打印出带注释的表头，必须limit 0形式：select * from ( select \'xx\',\'yy\',\'kk\' limit 0) 这样才能只剩余表头，没有数据
273 /*
274 select * from
275 (
276 select \'id : {物理主键（自增）}\',\'create_time : {创建时间}\',\'user_behavior_id : {tbl_ams_user_behavior表id}\',\'被tbl_ams_activity_rule引用 : 活动规则\' ,\'被tbl_ams_user_behavior_record引用 : 用户行为记录表\'
277 limit 0
278 ) t
279 */
280 SELECT
281 CONCAT_WS(\'\',\'select * from ( select \',group_concat(CONCAT_WS(\'\',\"\'\",t1.COLUMN_NAME,\' : {\',t1.COLUMN_COMMENT,\"}\'\") order BY c.id),@fk_tbl_name_ref_header,\' limit 0 ) t \')
282 INTO @select_cols_comment
283 FROM
284 information_schema.COLUMNS t1
285 JOIN test_main_tbl_col c ON c.col=t1.COLUMN_NAME
286 WHERE
287 t1.table_schema= DATABASE() AND
288 t1.TABLE_NAME =@main_tbl ;
289
290 -- SELECT @select_cols_comment;
291
292 /*
293 -- 主语句 ： 后部分，将字段列表打印出来
294 (select
295 @row_num_tbl_ams_activity_rule:= 0 , @row_num_tbl_ams_user_behavior_record:= 0 , id,create_time,user_behavior_id , %tbl_ams_activity_rule% as tbl_ams_activity_rule , %tbl_ams_user_behavior_record% as tbl_ams_user_behavior_record
296 from tbl_ams_user_specific_behavior where 1=1 limit 50
297 ) t
298 */
299 SELECT CONCAT_WS(\'\',\' (select \',@row_num_fk_tbl_name,cols,@fk_tbl_name_as,\' from \',@main_tbl,\' \')
300 into @exec_sql
301 from
302 (SELECT GROUP_CONCAT(col ORDER BY id) AS cols FROM test_main_tbl_col WHERE main_tbl_name=@main_tbl) t;
303
304 -- SELECT @exec_sql;
305
306 -- 将外键字段标识出来%xx% ,建表必须写as temp,@开头的字段名不合法
307 DROP TABLE if exists test_temp;
308 CREATE TABLE test_temp as
309 SELECT @exec_sql:=REPLACE(@exec_sql,main_tbl_foreign_key,CONCAT_WS(\'\',\'%\',main_tbl_foreign_key,\'%\')) AS temp
310 FROM test_child_tbl
311 WHERE main_tbl_name=@main_tbl;
312 DROP TABLE if exists test_temp;
313 /*
314 union all
315 select
316 id,create_time,user_behavior_id , tbl_ams_activity_rule , tbl_ams_user_behavior_record
317 from
318 (select
319 @row_num_tbl_ams_activity_rule:= 0 , @row_num_tbl_ams_user_behavior_record:= 0 , id,create_time,%user_behavior_id% , %tbl_ams_activity_rule% as tbl_ams_activity_rule , %tbl_ams_user_behavior_record% as tbl_ams_user_behavior_record
320 from tbl_ams_user_specific_behavior where 1=1 limit 50
321 ) t
322 */
323 SELECT CONCAT_WS(\'\',\' union all select \',cols,@fk_tbl_name,\' from \',@exec_sql)
324 into @exec_sql
325 from
326 (SELECT GROUP_CONCAT(col ORDER BY id) AS cols FROM test_main_tbl_col WHERE main_tbl_name=@main_tbl) t;
327
328
329 -- SELECT @exec_sql;
330
331 -- 主语句
332 /*
333
334
335 select
336 *
337 from
338 (
339 select \'id : {物理主键（自增）}\',\'create_time : {创建时间}\',\'user_behavior_id : {tbl_ams_user_behavior表id}\',\'被tbl_ams_activity_rule引用 : 活动规则\' ,\'被tbl_ams_user_behavior_record引用 : 用户行为记录表\' limit 0
340 ) t
341 union all
342 select
343 id,create_time,user_behavior_id , tbl_ams_activity_rule , tbl_ams_user_behavior_record
344 from
345 (select
346 @row_num_tbl_ams_activity_rule:= 0 , @row_num_tbl_ams_user_behavior_record:= 0 , id,create_time,%user_behavior_id% , %tbl_ams_activity_rule% as tbl_ams_activity_rule , %tbl_ams_user_behavior_record% as tbl_ams_user_behavior_record
347 from tbl_ams_user_specific_behavior where 1=1 limit 50
348 ) t
349 */
350 SET @exec_sql=CONCAT_WS(\'\',@select_cols_comment,@exec_sql);
351
352 -- SELECT @exec_sql;
353
354
355 -- 替换组主语句中被%xx%标识出来的外键字段为子查询
356 DROP TABLE IF EXISTS test_temp;
357 CREATE TABLE if NOT exists test_temp AS
358 SELECT
359 @exec_sql := REPLACE (
360 @exec_sql, -- 原始值主语句
361 CONCAT_WS( \'\', \'%\', main_tbl_foreign_key, \'%\' ), -- 要替换的对象，及被%xx%标记的外键字段
362 CONCAT_WS( \'\', query_ref_tbl_info, \' as \', main_tbl_foreign_key ) -- 替换为子查询
363 ) AS temp
364 FROM
365 (
366 SELECT
367 CONCAT_WS(\'\',\'(CONCAT_WS(\"\",\',\'(\',\'select concat_ws(\"\",\"{\",trim(\", \\r\\n\" from replace(group_concat(\',
368 cols,\'),\"$\",\"\\\\\"\")),\"}\") from \',child_tbl_name,\' where \',ref_child_tbl_id,\' = \',main_tbl_name,\'.\',
369 main_tbl_foreign_key,\')\',\',\\\'\\\\r\\\\n\\\\r\\\\n\',cols_desc,\'\\\'))\'
370 ) AS query_ref_tbl_info,
371 t.*
372 FROM
373 (SELECT test_child_tbl.*
374 ,
375 (SELECT GROUP_CONCAT(
376 CONCAT_WS(\'\',
377 CONCAT_WS( \'\', \'\"$\', col, \'$\' ),
378 -- 打印对齐空格
379 REPEAT(\' \',
380 (
381 ( SELECT MAX( length( CONCAT_WS( \'\', \'\"$\', col, \'$\' ) ) ) FROM test_child_tbl_col WHERE child_tbl_name = t.child_tbl_name )
382 -
383 LENGTH( CONCAT_WS( \'\', \'\"$\', col, \'$\' ) )
384 )
385 ),
386 CONCAT_WS( \'\', \' : $\",ifnull(\', col, \',\" \")\', \',\"$, \\r\\n\"\' )
387 ) ORDER BY id SEPARATOR \',\\r\\n\'
388 )
389 FROM test_child_tbl_col t
390 WHERE child_tbl_name = test_child_tbl.child_tbl_name
391 ) AS cols
392 ,
393 (SELECT CONCAT_WS(\'\',\'\"字段注释：\"{\\r\\n\',
394 GROUP_CONCAT(CONCAT_WS(\'\',
395 CONCAT_WS( \'\', \'\"\', t.col, \'\"\' ),
396 REPEAT(\' \',
397 (
398 ( SELECT MAX( length( CONCAT_WS( \'\', \'\"\', col, \'\"\' ) ) ) FROM test_child_tbl_col WHERE child_tbl_name = t.child_tbl_name )
399 -
400 LENGTH( CONCAT_WS( \'\', \'\"\', t.col, \'\"\' ) )
401 )
402 ),
403 CONCAT_WS( \'\', \' : \"\', t.col_desc, \'\"\' )
404 )
405 ORDER BY id SEPARATOR \',\\r\\n\'
406 ),
407 \'\\r\\n}\'
408 )
409 FROM test_child_tbl_col t
410 WHERE child_tbl_name = test_child_tbl.child_tbl_name
411 ) AS cols_desc
412 FROM test_child_tbl
413 WHERE main_tbl_name = @main_tbl
414 ) t
415 ) tt;
416
417 DROP TABLE IF EXISTS test_temp;
418
419
420
421
422
423 -- ----------------------------------------------------------------------
424 -- ----------------------------------------------------------------------
425 -- ----------------------------------------------------------------------
426 -- 找出外键关系
427 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
428 -- ----------------------------------------------------------------------
429 -- ----------------------------------------------------------------------
430 -- ----------------------------------------------------------------------
431
432
433 DROP TABLE IF EXISTS test_temp;
434 CREATE TABLE test_temp AS
435 SELECT @exec_sql:=REPLACE(@exec_sql,CONCAT_WS(\'\',\'%\',tbl_name,\'_\',col_name,\'%\'),query_ref_tbl_info) AS temp
436 FROM
437 (
438 SELECT
439 CONCAT_WS(\'\',\'(CONCAT_WS(\"\",\',\"\'总行数：\',\",counts,\',\" \\\\r\\\\n\",(\',\'select replace(group_concat(concat_ws(\"\",\',row_num_sum,\',\": {\",\',cols,\',\"}\")\',\' SEPARATOR \"\\\\r\\\\n\\\\r\\\\n\"),\"$\",\"\\\\\"\") \',\' from \',tbl_name,\' where \',col_name,\' = \',rf_name,\'.\',rf_col,\' and @row_num_\',tbl_name,\'_\',col_name,\' < \',@in_sub_limit,\' ) \',\',\\\'\\\\r\\\\n\\\\r\\\\n\',cols_desc,\'\\\'))\'
440 ) AS query_ref_tbl_info , t.*
441 from
442 (
443 SELECT
444 CONCAT_WS(\'\',\'@row_num_\',t.TABLE_NAME,\'_\',k.column_name,\':=\',\'@row_num_\',t.TABLE_NAME,\'_\',k.column_name,\' + 1\') AS row_num_sum,
445 t.TABLE_NAME AS tbl_name,
446 k.REFERENCED_TABLE_NAME AS rf_name,
447 k.column_name AS col_name,
448 k.REFERENCED_COLUMN_NAME AS rf_col
449 ,CONCAT_WS(\'\',TRIM(\', \\\"\' from group_concat(CONCAT_WS(\'\',\'\"$\',c.COLUMN_NAME,\'$:$\",\',\'ifnull(\',c.COLUMN_NAME,\',\" \")\',\',\"$, \"\') ORDER BY c.ORDINAL_POSITION )),\'\"\') AS cols
450 ,( CONCAT_WS(\'\',\'\"字段注释：\"{\\r\\n\',
451 GROUP_CONCAT(
452 CONCAT_WS(\'\',
453 -- -----------------------------------------------------
454 /*第一列：`id`=id `create_time`=\'create_time\' */
455 CONCAT_WS( \'\',\'\"\',c.COLUMN_NAME,\'\"\')
456 /* 第二列：对齐用的空格*/
457 ,repeat(\' \',
458 ((SELECT MAX(length(CONCAT_WS( \'\',\'\"\',tt.COLUMN_NAME,\'\"\'))) FROM information_schema.COLUMNS tt WHERE tt.TABLE_SCHEMA =DATABASE() and tt.TABLE_NAME =c.TABLE_NAME)
459 -
460 LENGTH(CONCAT_WS( \'\',\'\"\',c.COLUMN_NAME,\'\"\'))
461 )
462 )
463 /*第三列：注释*/
464 ,CONCAT_WS(\'\',\' : \"\',c.COLUMN_COMMENT,\'\"\')
465 -- -----------------------------------------------------
466 )
467 SEPARATOR \',\\r\\n\'),\'\\r\\n}\')
468 ) AS cols_desc
469 ,
470 CONCAT_WS(\'\',\'( select count(*) from \',t.TABLE_NAME,\' where \',k.column_name,\' = \',k.REFERENCED_TABLE_NAME,\'.\', k.REFERENCED_COLUMN_NAME,\' )\') AS counts
471 ,
472 (
473 SELECT
474 cc.COLUMN_NAME
475 FROM
476 INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tt,
477 INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cc
478 WHERE
479 tt.TABLE_NAME = cc.TABLE_NAME
480 AND tt.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
481 AND tt.TABLE_SCHEMA = DATABASE()
482 AND cc.CONSTRAINT_SCHEMA=DATABASE()
483 AND tt.CONSTRAINT_TYPE = \'PRIMARY KEY\'
484 AND tt.TABLE_NAME=t.TABLE_NAME
485 ) AS tbl_name_pk
486
487 FROM
488 information_schema.TABLE_CONSTRAINTS t
489 JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME
490 JOIN information_schema.COLUMNS c ON c.TABLE_NAME=k.TABLE_NAME
491 AND t.TABLE_NAME = k.TABLE_NAME
492 AND c.table_schema=DATABASE()
493 AND t.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
494 WHERE
495 t.CONSTRAINT_TYPE = \'FOREIGN KEY\'
496 AND t.table_schema = DATABASE()
497 AND k.CONSTRAINT_SCHEMA=DATABASE()
498 and k.REFERENCED_TABLE_NAME=@main_tbl
499 GROUP BY tbl_name,rf_name,col_name,rf_col
500 ) t
501 ) s;
502
503
504
505 DROP TABLE IF EXISTS test_temp;
506
507 SET @exec_sql=CONCAT_WS(\'\',@exec_sql,\' \',@in_where,\' ) t \');
508 PREPARE stmt FROM @exec_sql;
509 EXECUTE stmt;
510 DEALLOCATE PREPARE stmt;
511
512
513 DROP TABLE IF EXISTS test_main_tbl_col;
514 DROP TABLE IF EXISTS test_child_tbl;
515 DROP TABLE IF EXISTS test_child_tbl_col;
516
517
518 /*
519
520 SET @in_where=\"where id= 2 limit 1,3\";
521 SELECT SUBSTRING_INDEX(@in_where,\' limit \',-1);-- 1,3
522 SELECT SUBSTRING_INDEX(@in_where,\' limit \',1); -- where id= 2
523
524 SELECT LOCATE(\' limit \',@in_where); -- 含有：>0 12
525 SELECT LOCATE(\' limits \',@in_where); -- 不含有：>0 0
526
527 -- 拆分 where子句中的limit条件，用作后续使用
528 if( LOCATE(\' limit \',@in_where) >0) then -- 包含limit子句
529 SET @limit_phase=CONCAT_WS(\'\',\' limit \',SUBSTRING_INDEX(@in_where,\' limit \',-1));
530 SET @in_where=SUBSTRING_INDEX(@in_where,\' limit \',1);
531 ELSE
532 SET @limit_phase=\' \';
533 END if;
534
535
536
537
538 -- 产生sql，每行作为一个json
539 -- 如下：
540 select replace(concat_ws(\"\",\"{\",\"$id$:$\",ifnull(id,\" \"),\"$, \",\"$user_uuid$:$\",ifnull(user_uuid,\" \"),\"$,
541 \",\"$activity_item_id$:$\",ifnull(activity_item_id,\" \"),\"$, \",\"$piece_number$:$\",ifnull(piece_number,\" \"),\"$, \",\"$create_time$:$\",
542 ifnull(create_time,\" \"),\"$, \",\"$last_update_time$:$\",ifnull(last_update_time,\" \"),\"$\",\"}\"),\"$\",\"\\\"\") from tbl_ams_user_piece
543
544 SET @main_tbl=\'tbl_ams_user_piece\';
545
546 SELECT
547 CONCAT_WS(\'\',\'select \',cons,\' from \',@main_tbl) AS \'json\'
548 ,CONCAT_WS(\'\',\'select concat_ws(\"\",\',cons,\',\',\"\'\\\\r\\\\n\\\\r\\\\n\",cols_desc,\"\')\",\' from \',@main_tbl) AS \'json带注释\'
549 FROM
550 (
551 SELECT
552 CONCAT_WS(\'\',\'replace(concat_ws(\"\",\"{\",\',cols,\',\"}\"),\"$\",\"\\\\\"\")\') AS cons
553 ,t.cols_desc
554 FROM
555 (
556 SELECT
557 CONCAT_WS(\'\',TRIM(\', \\\"\' from group_concat(CONCAT_WS(\'\',\'\"$\',c.COLUMN_NAME,\'$:$\",\',\'ifnull(\',c.COLUMN_NAME,\',\" \")\',\',\"$, \"\') ORDER BY c.ORDINAL_POSITION )),\'\"\') AS cols
558 ,( CONCAT_WS(\'\',\'\"字段注释：\"{\\r\\n\',
559 GROUP_CONCAT(
560 CONCAT_WS(\'\',
561 -- -----------------------------------------------------
562
563 CONCAT_WS( \'\',\'\"\',c.COLUMN_NAME,\'\"\')
564
565 ,repeat(\' \',
566 ((SELECT MAX(length(CONCAT_WS( \'\',\'\"\',tt.COLUMN_NAME,\'\"\'))) FROM information_schema.COLUMNS tt WHERE tt.TABLE_SCHEMA =DATABASE() and tt.TABLE_NAME =c.TABLE_NAME)
567 -
568 LENGTH(CONCAT_WS( \'\',\'\"\',c.COLUMN_NAME,\'\"\'))
569 )
570 )
571
572 ,CONCAT_WS(\'\',\' : \"\',c.COLUMN_COMMENT,\'\"\')
573 -- -----------------------------------------------------
574 )
575 SEPARATOR \',\\r\\n\'),\'\\r\\n}\')
576 ) AS cols_desc
577 FROM information_schema.COLUMNS c
578 WHERE c.TABLE_SCHEMA=DATABASE() AND c.TABLE_NAME=@main_tbl
579 ) t
580 ) ts;
581
582 */
583
584 END %%
585
586 DELIMITER ;
587
588 CALL tbl_query_help() ;

来源：https://www.cnblogs.com/wanglifeng717/p/15838805.html
图文来源于网络，如有侵权请联系删除。