mysql批量增加表中新列存儲過程

mysql批量增加表中新列存儲過程

一般訪問量比較大的網站,請求日志表都是每天一張表獨立創建. 業務需要為每張表都添加一個新列,糾結了半天,寫了個存儲過程如下:

日志表結構類型 tbl_ads_req_20140801, tbl_ads_req_20140802 ...

DELIMITER //
create procedure sp2()

begin

declare 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函數需要自己查哦！