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

2019-10-19 15:37:00

字体：大中小

来源：转载

供稿：网友

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

第三个参数是数据库名称

上一篇：linux下导入、导出mysql数据库命令

下一篇：MySQL数据类型和常用字段属性总结