代碼如下:

BEGIN 
DECLARE `@i` int(11); 
DECLARE `@siteCount` int(11); 
DECLARE `@sqlstr` VARCHAR(2560); 
DECLARE `@sqlinsert` VARCHAR(2560); //以上聲明變量 
SELECT COUNT(0) into `@siteCount` FROM tbl_base_site; //計(jì)算表tbl_base_site的記錄總條數(shù) 
set `@i`=1; 
WHILE (`@i`-1)*300<`@siteCount` DO //while循環(huán)執(zhí)行 
SET @sqlstr = CONCAT('CREATE TABLE tbl_base_syslog',`@i`,'(syslog_id INT(11) 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; 
//以上實(shí)現(xiàn)動(dòng)態(tài)創(chuàng)建表 
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,',300) as tbs) 
ORDER BY syslog_id'); 
prepare stmt from @sqlinsert; 
execute stmt; 
//以上實(shí)現(xiàn)從一張表查詢(xún)記錄插入到動(dòng)態(tài)創(chuàng)建的新表中 
SET `@i`= `@i`+1; 

END WHILE; 
END