mysql 在创建批处理脚本日志表信息

mysql在批处理脚本通过存储过程如下所示创建日志信息表:

drop PROCEDURE if
EXISTS reqSp;
DELIMITER //
create procedure reqSp(sTime varchar(32), eTime varchar(32),tchema varchar(32))
begin
declare sName varchar(128);
declare uid varchar(128);
declare orderId varchar(128);
declare sqlVar varchar(1024);
declare rest int;
set rest = 1;
while rest > 0 do
set sTime = (select DATE_FORMAT((select ADDDATE(sTime,1)),'%Y%m%d'));
set sName = CONCAT('tbl_resp_',sTime);
set uid = CONCAT('idx_id_resp_',sTime);
set orderId = CONCAT('idx_order_resp_',sTime);
select count(1) from information_schema.tables where table_name = sName
and TABLE_SCHEMA=tchema into @cnt;
if @cnt = 0 then
set sqlVar=CONCAT(' create table ',sName,
' ( id varchar(60) NOT NULL, ',
'
order varchar(100) NOT NULL, ',
'
ads int(10) NOT NULL, ',
'
type varchar(60) NOT NULL, ',
'
group int(10) NOT NULL, ',
'
template int(10) NOT NULL, ',
'
banner varchar(100) DEFAULT NULL, ',
'
app varchar(100) DEFAULT NULL, ',
'
create_time datetime NOT NULL, ',
'
key ',uid ,' (uid),',
'
key ',orderId ,' (order_id)',
'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ');
set @v_s=sqlVar;
prepare stmt from @v_s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end if;
set rest = DATEDIFF(eTime,sTime);
END while;
end;
//
DELIMITER;

call reqSp('20141001','20151001','dbname')

第一个參数開始时间 20141001

第二个参数结束时间 20151001

第三个参数是数据库名称

版权声明：本文博主原创文章，博客，未经同意不得转载。