MySQL靜態創立表，數據分表的存儲進程。本站提示廣大學習愛好者：（MySQL靜態創立表，數據分表的存儲進程）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL靜態創立表，數據分表的存儲進程正文
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; //盤算表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
