省去跨表联查与注释查询的存储过程
问题：
传统的select * from table 存在以下几个问题：
1.字段含义及“魔法数字”需要另开窗口对照查看，例如status有1,2,3,4,5
2.外键字段引用的是对方表的id，要知道id对应信息，需要另开窗口查询，或者写跨表联查语句。例如：该订单对应的合作方具体信息是什么
3.该记录被其他表引用了的情况也需要另外进行查询。例如：该合作方有多少订单及详情。
解决方案：
编写存储过程" title="存储过程">存储过程。
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
3DROPPROCEDUREIFEXISTS tbl_query_help;
4 DELIMITER %%
5CREATEPROCEDURE tbl_query_help()
6BEGIN
7 CALL tbl_query("","","","");
8END%%
9DELIMITER ;
10
11
12-- --------------------------------------------------------------------------
13-- 作者：王李峰
14-- 功能：高级查询，去除跨表联查
15-- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
16-- --------------------------------------------------------------------------
17DROPPROCEDUREIFEXISTS tbl_query;
18 DELIMITER %%
19CREATEPROCEDURE tbl_query( in_var VARCHAR ( 255 ),in_col VARCHAR(1024),in_where VARCHAR(1024) ,in_sub_limit VARCHAR(1024))
20 label:BEGIN
21
22SET group_concat_max_len =4294967295;
23
24DROPTABLEIFEXISTS
25test_main_tbl_col,
26test_child_tbl,
27test_child_tbl_col;
28
29SET@main_tbl= in_var;
30SET@in_sub_limit=in_sub_limit;
31SET@main_tbl_if_all="yes";
32SET@in_where=in_where;
33
34-- 如果输入的表名是空，则打印帮助信息-----------------------------------------------------------------------------------------------------------
35-- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
36if(@main_tblISNULLOR@main_tbl="") then
37SELECT"作者：王李峰"AS col, "功能：高级查询，无须跨表联查"AS col ,"前提：要有外键，该工具自动匹配外键进行"AS col
38UNIONall
39SELECT""AS col ,""AS col ,""AS col
40UNIONALL
41SELECT"全部表tbl_query语句"AS col, "参数提示",""AS col
42UNIONall
43SELECT
44 (
45SELECT GROUP_CONCAT(
46 CONCAT_WS(""
47 ,"-- 表注释: ",t1.TABLE_COMMENT,"
"
48 ,"-- 用法: tbl_query( in_var[要查询的表] ,in_col[需要查询的字段] ,in_where[where条件,支持limit] ,in_sub_limit[子查询limit限制条数] )
"
49 ,"-- 字段列表: ",((SELECT GROUP_CONCAT(t.COLUMN_NAME) FROM information_schema.columns t WHERE t.TABLE_SCHEMA=DATABASE() and t.TABLE_NAME=t1.TABLE_NAME)),"
"
50 ,"call tbl_query("",t1.TABLE_NAME,"","*"," where 1=1 limit 50","32"",");")
51 SEPARATOR "
")
52FROM
53 information_schema.tables t1
54WHERE
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
2.in_where参数中支持针对in_var参数输入的表自定义where语句"AS col
59UNIONall
60SELECT""AS col ,""AS col ,""AS col
61UNIONALL
62SELECT"表名"AS col ,"调用方式"AS col ,"表所有字段注释"AS col
63UNIONALL
64SELECT
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 ,(
72SELECT
73 CONCAT_WS(""
74 ,"-- 表注释: ",t2.TABLE_COMMENT,"
"
75 ,"-- 用法: tbl_query( in_var[要查询的表] ,in_col[需要查询的字段] ,in_where[where条件,支持limit] ,in_sub_limit[子查询limit限制条数] )
"
76 ,"-- 字段列表: ",((SELECT GROUP_CONCAT(t.COLUMN_NAME) FROM information_schema.columns t WHERE t.TABLE_SCHEMA=DATABASE() and t.TABLE_NAME=t2.TABLE_NAME)),"
"
77 ,"call tbl_query("",t2.TABLE_NAME,"","*"," where 1=1 limit 50","32"",");")
78FROM
79 information_schema.tables t2
80WHERE
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("",""字段注释："{
",
95 GROUP_CONCAT(
96 CONCAT_WS("",
97 CONCAT_WS( "",""",t.COLUMN_NAME,""")
98 ,repeat("",
99 (
100 (SELECTMAX(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 )
109ORDERBY t.ORDINAL_POSITION SEPARATOR ",
"
110 ),"
}"
111 )
112FROM information_schema.columns t
113WHERE t.TABLE_SCHEMA =DATABASE() and t.TABLE_NAME=t1.TABLE_NAME
114 )
115FROM information_schema.tables t1
116WHERE t1.table_schema=DATABASE() ;
117-- 打印完帮助信息，直接跳出程序
118 leave label;
119ENDif;
120
121
122-- -- 判断自定义字段------------------------------------------
123SET@in_col=in_col;
124-- 如果输入为空，则是默认全部字段
125if(@in_colISNULLOR@in_col=""OR@in_col=""OR@in_col="*"OR@in_col=" *"OR@in_col="* "OR@in_col=" * " ) then-- 防止用户无效输入,这个匹配"",两个单引号之间有任意个空格都能匹配
126SET@main_tbl_if_all="yes";
127ELSE
128SET@main_tbl_if_all="no";
129-- 输入id,name,code 转换为："id","name","code"
130SET@user_define_main_tbl_cols=CONCAT_WS("",""",REPLACE(@in_col,",","",""),""");
131ENDif;
132
133-- SELECT @user_define_main_tbl_cols;
134
135
136-- 建表---------------------------------------------------------------------
137
138CREATETABLEIFNOTEXISTS `test_child_tbl` (
139 `id` int(10) unsigned NOTNULL AUTO_INCREMENT COMMENT "主键",
140 `main_tbl_name` varchar(200) COLLATE utf8_bin DEFAULTNULL COMMENT "主表名",
141 `child_tbl_name` varchar(256) COLLATE utf8_bin DEFAULTNULL COMMENT "字表名",
142 `main_tbl_foreign_key` varchar(200) COLLATE utf8_bin DEFAULTNULL COMMENT "主表外键字段名，如user_uuid",
143 `ref_child_tbl_id` varchar(256) COLLATE utf8_bin DEFAULTNULL COMMENT "字表中被引用的键,如，user表中uuid",
144PRIMARYKEY (`id`),
145UNIQUEKEY `uni_test_child_tbl` (`main_tbl_name`,`main_tbl_foreign_key`)
146 ) COMMENT="测试：有哪些子表，即外键关联到的表";
147
148CREATETABLEIFNOTEXISTS `test_child_tbl_col` (
149 `id` int(10) unsigned NOTNULL AUTO_INCREMENT COMMENT "主键",
150 `child_tbl_name` varchar(200) COLLATE utf8_bin DEFAULTNULL COMMENT "字表名",
151 `col` varchar(200) COLLATE utf8_bin DEFAULTNULL COMMENT "子表需要展示的字段",
152 `col_desc` VARCHAR(500) COLLATE utf8_bin DEFAULTNULL COMMENT "字段注释",
153PRIMARYKEY (`id`),
154UNIQUEKEY `uni_test_child_tbl_col` (`child_tbl_name`,`col`)
155 ) COMMENT="测试：字表中要展示的字段";
156
157CREATETABLEIFNOTEXISTS `test_main_tbl_col` (
158 `id` int(10) NOTNULL AUTO_INCREMENT COMMENT "主键",
159 `main_tbl_name` varchar(200) COLLATE utf8_bin NOTNULL COMMENT "主表名",
160 `col` varchar(200) COLLATE utf8_bin NOTNULL COMMENT "主表需要展示的字段",
161PRIMARYKEY (`id`),
162UNIQUEKEY `uni_test_main_tbl_col` (`main_tbl_name`,`col`)
163 ) COMMENT="测试：主表中需要查询哪些字段";
164
165
166-- 默认插入主表所有字段sql
167SET@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-- 判断并插入：是默认插入所有字段，还是插入自定义字段
176SELECT
177IF
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 )
186INTO@insert_main_tbl_col;
187
188PREPARE stmt FROM@insert_main_tbl_col;
189EXECUTE stmt;
190DEALLOCATEPREPARE stmt;
191
192
193-- select @insert_main_tbl_col;
194
195-- 插入子表：按照外键找出所有子表名及关联的外键等字段
196INSERT IGNORE INTO test_child_tbl ( main_tbl_name, child_tbl_name, main_tbl_foreign_key, ref_child_tbl_id )
197SELECT*
198FROM
199(
200SELECT
201 t.TABLE_NAME AS tbl_name,
202 k.REFERENCED_TABLE_NAME AS rf_name,
203 k.column_name AScol_name,
204 k.REFERENCED_COLUMN_NAME AS rf_col
205FROM
206 information_schema.TABLE_CONSTRAINTS t
207JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME
208AND t.TABLE_NAME = k.TABLE_NAME
209AND t.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
210WHERE
211 t.CONSTRAINT_TYPE ="FOREIGN KEY"
212AND t.table_schema =DATABASE()
213AND k.CONSTRAINT_SCHEMA=DATABASE()
214AND t.TABLE_NAME =@main_tbl
215) t;
216
217-- 把所有子表的所有col字段出并插入
218INSERT IGNORE INTO test_child_tbl_col ( child_tbl_name, col, col_desc )
219SELECT
220 t1.TABLE_NAME,
221 t1.COLUMN_NAME,
222 t1.COLUMN_COMMENT
223FROM
224 information_schema.COLUMNS t1
225WHERE
226 t1.table_schema =DATABASE ( )
227AND t1.TABLE_NAME IN ( SELECTDISTINCT child_tbl_name FROM test_child_tbl )
228ORDERBY 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-- 根据外键找到主表被哪些表引用了。就是一对多的情况
244SELECT
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 " , ") ISNULL,"",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"
250INTO@row_num_fk_tbl_name,@fk_tbl_name,@fk_tbl_name_as,@fk_tbl_name_ref_header,@var_fk_tbl_name
251FROM
252 (SELECT
253 t.TABLE_NAME AS tbl_name,
254 k.column_name AScol_name,
255 k.REFERENCED_TABLE_NAME AS rf_name,
256 k.REFERENCED_COLUMN_NAME AS rf_col,
257 tb.TABLE_COMMENT AS tbl_comment
258FROM
259 information_schema.TABLE_CONSTRAINTS t
260JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
261ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME
262JOIN INFORMATION_SCHEMA.tables tb
263ON tb.TABLE_NAME=t.TABLE_NAME
264AND t.TABLE_NAME = k.TABLE_NAME
265AND t.CONSTRAINT_SCHEMA=k.CONSTRAINT_SCHEMA
266AND tb.TABLE_SCHEMA=DATABASE() AND k.CONSTRAINT_SCHEMA=DATABASE()
267WHERE 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/*
274select * 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*/
280SELECT
281 CONCAT_WS("","select * from ( select ",group_concat(CONCAT_WS("",""",t1.COLUMN_NAME," : {",t1.COLUMN_COMMENT,"}"") orderBY c.id),@fk_tbl_name_ref_header," limit 0 ) t ")
282INTO@select_cols_comment
283FROM
284 information_schema.COLUMNS t1
285JOIN test_main_tbl_col c ON c.col=t1.COLUMN_NAME
286WHERE
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*/
299SELECT CONCAT_WS(""," (select ",@row_num_fk_tbl_name,cols,@fk_tbl_name_as," from ",@main_tbl,"")
300into@exec_sql
301from
302 (SELECT GROUP_CONCAT(col ORDERBY 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,@开头的字段名不合法
307DROPTABLEifexists test_temp;
308CREATETABLE test_temp as
309SELECT@exec_sql:=REPLACE(@exec_sql,main_tbl_foreign_key,CONCAT_WS("","%",main_tbl_foreign_key,"%")) AStemp
310FROM test_child_tbl
311WHERE main_tbl_name=@main_tbl;
312DROPTABLEifexists 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*/
323SELECT CONCAT_WS(""," union all select ",cols,@fk_tbl_name," from ",@exec_sql)
324into@exec_sql
325from
326 (SELECT GROUP_CONCAT(col ORDERBY 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
335select
336 *
337from
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
341union all
342select
343 id,create_time,user_behavior_id , tbl_ams_activity_rule , tbl_ams_user_behavior_record
344from
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*/
350SET@exec_sql=CONCAT_WS("",@select_cols_comment,@exec_sql);
351
352-- SELECT @exec_sql;
353
354
355-- 替换组主语句中被%xx%标识出来的外键字段为子查询
356DROPTABLEIFEXISTS test_temp;
357CREATETABLEifNOTexists test_temp AS
358SELECT
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 ) AStemp
364FROM
365 (
366SELECT
367 CONCAT_WS("","(CONCAT_WS("",","(","select concat_ws("","{",trim(",
" 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.*
372FROM
373 (SELECT test_child_tbl.*
374 ,
375 (SELECT GROUP_CONCAT(
376 CONCAT_WS("",
377 CONCAT_WS( "", ""$", col, "$" ),
378-- 打印对齐空格
379 REPEAT("",
380 (
381 ( SELECTMAX( 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, "," ")", ","$,
"" )
387 ) ORDERBY id SEPARATOR ",
"
388 )
389FROM test_child_tbl_col t
390WHERE child_tbl_name = test_child_tbl.child_tbl_name
391 ) AS cols
392 ,
393 (SELECT CONCAT_WS("",""字段注释："{
",
394 GROUP_CONCAT(CONCAT_WS("",
395 CONCAT_WS( "", """, t.col, """ ),
396 REPEAT("",
397 (
398 ( SELECTMAX( 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 )
405ORDERBY id SEPARATOR ",
"
406 ),
407"
}"
408 )
409FROM test_child_tbl_col t
410WHERE child_tbl_name = test_child_tbl.child_tbl_name
411 ) AS cols_desc
412FROM test_child_tbl
413WHERE main_tbl_name =@main_tbl
414 ) t
415 ) tt;
416
417DROPTABLEIFEXISTS 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
433DROPTABLEIFEXISTS test_temp;
434CREATETABLE test_temp AS
435SELECT@exec_sql:=REPLACE(@exec_sql,CONCAT_WS("","%",tbl_name,"_",col_name,"%"),query_ref_tbl_info) AStemp
436FROM
437(
438SELECT
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.*
441from
442 (
443SELECT
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 AScol_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,"," ")",","$, "") ORDERBY c.ORDINAL_POSITION )),""") AS cols
450 ,( CONCAT_WS("",""字段注释："{
",
451 GROUP_CONCAT(
452 CONCAT_WS("",
453-- -----------------------------------------------------
454/*第一列：`id`=id `create_time`="create_time" */
455 CONCAT_WS( "",""",c.COLUMN_NAME,""")
456/* 第二列：对齐用的空格*/
457 ,repeat("",
458 ((SELECTMAX(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 ",
"),"
}")
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 (
473SELECT
474 cc.COLUMN_NAME
475FROM
476 INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tt,
477 INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cc
478WHERE
479 tt.TABLE_NAME = cc.TABLE_NAME
480AND tt.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
481AND tt.TABLE_SCHEMA =DATABASE()
482AND cc.CONSTRAINT_SCHEMA=DATABASE()
483AND tt.CONSTRAINT_TYPE ="PRIMARY KEY"
484AND tt.TABLE_NAME=t.TABLE_NAME
485 ) AS tbl_name_pk
486
487FROM
488 information_schema.TABLE_CONSTRAINTS t
489JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME
490JOIN information_schema.COLUMNS c ON c.TABLE_NAME=k.TABLE_NAME
491AND t.TABLE_NAME = k.TABLE_NAME
492AND c.table_schema=DATABASE()
493AND t.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
494WHERE
495 t.CONSTRAINT_TYPE ="FOREIGN KEY"
496AND t.table_schema =DATABASE()
497AND k.CONSTRAINT_SCHEMA=DATABASE()
498and k.REFERENCED_TABLE_NAME=@main_tbl
499GROUPBY tbl_name,rf_name,col_name,rf_col
500 ) t
501) s;
502
503
504
505DROPTABLEIFEXISTS test_temp;
506
507SET@exec_sql=CONCAT_WS("",@exec_sql,"",@in_where," ) t ");
508PREPARE stmt FROM@exec_sql;
509EXECUTE stmt;
510DEALLOCATEPREPARE stmt;
511
512
513DROPTABLEIFEXISTS test_main_tbl_col;
514DROPTABLEIFEXISTS test_child_tbl;
515DROPTABLEIFEXISTS test_child_tbl_col;
516
517
518/*
519
520SET @in_where="where id= 2 limit 1,3";
521SELECT SUBSTRING_INDEX(@in_where," limit ",-1);-- 1,3
522SELECT SUBSTRING_INDEX(@in_where," limit ",1); -- where id= 2
523
524SELECT LOCATE(" limit ",@in_where); -- 含有：>0 12
525SELECT LOCATE(" limits ",@in_where); -- 不含有：>0 0
526
527-- 拆分 where子句中的limit条件，用作后续使用
528if( 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);
531ELSE
532 SET @limit_phase=" ";
533END if;
534
535
536
537
538-- 产生sql，每行作为一个json
539-- 如下：
540select 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$:$",
542ifnull(create_time," "),"$, ","$last_update_time$:$",ifnull(last_update_time," "),"$","}"),"$",""") from tbl_ams_user_piece
543
544SET @main_tbl="tbl_ams_user_piece";
545
546SELECT
547CONCAT_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带注释"
549FROM
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("",""字段注释："{
",
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 ",
"),"
}")
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
584END%%
585
586DELIMITER ;
587
588 CALL tbl_query_help() ;
tbl_query存储过程
本文来自云海天，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/15838805.html
以上是 省去跨表联查与注释查询的存储过程 的全部内容， 来源链接： utcz.com/z/536259.html
