一般访问量比较大的网站,请求日志表都是每天一张表独立创建. 业务需要为每张表都添加一个新列,纠结了半天,写了个存储过程如下:
日志表结构类型 tbl_ads_req_20140801, tbl_ads_req_20140802 ...
delimiter //create procedure sp2()begindeclare stime varchar(32);declare etime varchar(32);declare sname varchar(128);declare lname varchar(128);declare sqlvar varchar(128);declare rest int;set stime = '20140801';set etime = '20140831';set sname = concat('tbl_ads_req_','');set lname = '';set rest = 1;while rest > 0 do set stime = (select date_format((select adddate(stime,1)),'%y%m%d')); set lname = concat(sname,stime); set sqlvar=concat(' alter table ',lname,' add app_package varchar(64)'); set @v_s=sqlvar; prepare stmt from @v_s; execute stmt; set rest = datediff(etime,stime);end while;end;// delimiter;
涉及到一些mysql函数需要自己查哦！