Mysql multi-log table result set splicing Stored Procedure
Generally, a single-day log only records the log information of the current day. If you need to view the log information within January, You Need To splice the result set of the daily log table, and union is usually used.
Storage Process:
drop PROCEDURE if EXISTS unionSp;DELIMITER //create procedure unionSp(sTime varchar(32), eTime varchar(32),tchema varchar(32))begindeclare sqlVar varchar(1024000);declare rest int;declare tableName varchar(1024);set rest = 100;set sqlVar='';while rest > 0 do set sTime = (select DATE_FORMAT((select ADDDATE(sTime,1)),'%Y%m%d')); set tableName=CONCAT('tbl_req_',sTime); select count(1) from information_schema.tables where table_name = tableName and TABLE_SCHEMA=tchema into @cnt; if @cnt != 0 thenif rest=1 then set sqlVar=CONCAT(sqlVar,' SELECT DISTINCT channel_id,app_id from tbl_req_',sTime);ELSE set sqlVar=CONCAT(sqlVar,' SELECT DISTINCT channel_id,app_id from tbl_req_',sTime,' UNION');END IF;END if;set rest = DATEDIFF(eTime,sTime);END while;set @v_s=sqlVar;prepare stmt from @v_s;EXECUTE stmt;DEALLOCATE PREPARE stmt;end;// DELIMITER;call unionSp('20140730','20140930','biz_date')
Union: union means to combine the results of two or more queries.
Required: the number of columns in the Two Queries must be consistent.
Recommendation: The types of columns can be different, but we recommend that you query each column, and you want the corresponding types to be the same
Data from multiple tables: The names of the columns retrieved from multiple SQL statements may be inconsistent. The column names of the first SQL statement shall prevail.
If the rows extracted from different statements are identical (the values of each column are the same), union merges the same rows and retains only one row. We can also understand that union removes duplicate rows.
If you do not want to remove duplicate rows, you can use union all