Home > Database > Mysql Tutorial > body text

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

WBOY
Release: 2016-06-07 16:44:59
Original
1146 people have browsed it

MySQL中批量创建日志表信息脚本如下: drop PROCEDURE if EXISTS reqSp;DELIMITER //create procedure reqSp(sTime varchar(32)

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

第三个参数是数据库名称

--------------------------------------分割线 --------------------------------------

Ubuntu 14.04下安装MySQL

《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF

Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主从服务器

Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群

Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二进制安装

--------------------------------------分割线 --------------------------------------

本文永久更新链接地址:

linux

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