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

  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() ;

Original: https://www.cnblogs.com/wanglifeng717/p/15838805.html
Author: 王李峰
Title: 省去跨表联查与注释查询的存储过程

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/507858/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球