表名类似这这样,按日期2018-月份-天数,这样的表列表,怎么一次获取所以得表数据直接研究了一天多,查了好多资料,我自己解决了,贴出来,大家可以参考下DROPPROCEDUREIFEXISTSlist_...
表名类似这这样,按日期2018-月份-天数,这样的表列表,怎么一次获取所以得表数据直接
研究了一天多,查了好多资料,我自己解决了,贴出来,大家可以参考下
DROP PROCEDURE IF EXISTS list_v;
CREATE PROCEDURE list_v()
BEGIN
DECLARE v_sql_1 VARCHAR(8000)DEFAULT '';
DECLARE tableN VARCHAR(20);
DECLARE num INT DEFAULT 0;
DECLARE fi INT DEFAULT 1;
DECLARE company_list CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'visit_2018_%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET num=1;
OPEN company_list;
FETCH company_list INTO tableN;
WHILE num <> 1 DO
if(fi = 1) then
SET v_sql_1 = CONCAT(v_sql_1,'select * from `',tableN,'`');
SET fi = 2;
else
SET v_sql_1 = CONCAT(v_sql_1,' union select * from `',tableN,'`');
end if;
FETCH company_list INTO tableN;
END WHILE;
CLOSE company_list;
SET @sql_1 = v_sql_1;
PREPARE stmt1 FROM @sql_1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END;
CALL list_v();
展开