問題：
傳統的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() ;tbl_query存盤程序
本文來自博客園，作者：wanglifeng，轉載請注明原文鏈接：https://www.cnblogs.com/wanglifeng717/p/15838805.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/420445.html
標籤：其他