MySQL預存程序動態建立表，資料分表

BEGINDECLARE `@i` int(11);DECLARE `@siteCount` int(11);DECLARE `@sqlstr` VARCHAR(2560);DECLARE `@sqlinsert` VARCHAR(2560);  //以上聲明變數SELECT COUNT(0) into `@siteCount` FROM tbl_base_site;  //計算表tbl_base_site的記錄總條數set `@i`=1;WHILE (`@i`-1)*300<`@siteCount` DO  //while迴圈執行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;//以上實現動態建立表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;//以上實現從一張表查詢記錄插入到動態建立的新表中SET `@i`= `@i`+1;END WHILE;END

VeryMule專註於打造自己的網上商城