Batch create log table information scripts in mysql, mysql logs

Batch create log table information scripts in mysql, mysql logs

In mysql, the script for creating log information tables in batches through the stored procedure is as follows:

drop PROCEDURE if  EXISTS reqSp;DELIMITER //create procedure reqSp(sTime varchar(32), eTime varchar(32),tchema varchar(32))begindeclare 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.

Mysql console table creation script

------------------------------
-- Table structure for 'cpic _ bank'
-- ---------------------------- It's okay for me to annotate it like this. Is there a problem with your encoding format. Note the format. MYSQL is GBK

Mysql Databases batch delete data tables with the same prefix

Create a delete script first

Run the following statement:
Select 'drop table' + name from sysobjects where type = 'U' and name like 'B %'

The query results are displayed, and the query results are copied.
Then run the copied query results in database 123.