Batch create log table information scripts in MySQL

Batch create log table information scripts in MySQL

The script for creating log tables in batches in MySQL is as follows:

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 ('20140901', '20160901', 'dbname ')

The start time of the first parameter is 20141001.

The end time of the second parameter is 20151001.

The third parameter is the database name.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

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

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: