mysql 中批量创建日志表信息脚本，mysql日志

mysql 中批量创建日志表信息脚本，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

第三个参数是数据库名称

-- ----------------------------
-- Table structure for `cpic_bank`
-- ----------------------------我的就这样注释的，也没有问题啊。是不是你的编码格式出了问题。注意一下格式。MYSQL是GBK
 

先建立一个删除脚本

运行下语句
select 'drop table '+name from sysobjects where type='U' and name like 'B%'

会出来查询结果，将查询结果复制出来
然后在数据库123中运行复制出来的查询结果