省去跨表联查与注释查询的存储过程
问题：
传统的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() ;
本文来自博客园，作者：wanglifeng，转载请注明原文链接：https://www.cnblogs.com/wanglifeng717/p/15838805.html
