Home > Database > Mysql Tutorial > mysql批量增加表中新列存储过程_MySQL

mysql批量增加表中新列存储过程_MySQL

WBOY
Release: 2016-06-01 13:02:42
Original
1249 people have browsed it

一般访问量比较大的网站,请求日志表都是每天一张表独立创建. 业务需要为每张表都添加一个新列,纠结了半天,写了个存储过程如下:

日志表结构类型 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;
Copy after login

涉及到一些mysql函数需要自己查哦!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template