For websites with a large access volume, the request log table adds a new column for each table to create a business independently for each table every day. after a long time, the stored procedure is as follows: the log table structure type is tbl_ads_req_20140801 and tbl_ads_req_20140802DELIMITER. the request log table is created independently on a single table every day. the business needs to add a new column for each table. after a long time, the stored procedure is as follows:
Log table structure type 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 = '000000'; set eTime = '000000'; 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;
Some mysql functions need to be checked by yourself!