Copy Code code as follows:
BEGIN
DECLARE ' @i ' int (11);
DECLARE ' @siteCount ' int (11);
DECLARE ' @sqlstr ' VARCHAR (2560);
DECLARE ' @sqlinsert ' VARCHAR (2560); The above declaration variable
SELECT COUNT (0) into the ' @siteCount ' from Tbl_base_site; Count total number of records in table Tbl_base_site
Set ' @i ' = 1;
while (' @i '-1) *300< ' @siteCount ' do//while loop execution
SET @sqlstr = CONCAT (' CREATE TABLE tbl_base_syslog ', ' @i ', ' (syslog_id INT (one) auto_increment PRIMARY KEY,
Create_user VARCHAR (32),
Description text,
Create_time datetime,
site_id INT (11),
IP VARCHAR (64),
version_id SMALLINT (2),
Module_identity VARCHAR (64),
Right_name VARCHAR (64)
)');
Prepare stmt from @sqlstr;
Execute stmt;
The above implementation dynamic creation table
SET @sqlinsert = CONCAT (' INSERT into Tbl_base_syslog ', ' @i ', ' (
SYSLOG_ID,
Create_user,
Description
Create_time,
SITE_ID,
Ip
VERSION_ID,
Module_identity,
Right_name)
SELECT syslog_id,
Create_user,
Description
Create_time,
SITE_ID,
Ip
VERSION_ID,
Module_identity,
Right_name
From Tbl_base_syslog
WHERE site_id in (select tbs.site_id from (select site_id from Tbl_base_site limit ', ' @i ' *300, ', ') as TBS)
Order by syslog_id ');
Prepare stmt from @sqlinsert;
Execute stmt;
The above implementation is inserted into a new table dynamically created from a table query record
SET ' @i ' = ' @i ' +1;
End while;
End
