MySQL動(dòng)態(tài)創(chuàng)建表，數(shù)據(jù)分表的存儲(chǔ)過(guò)程

2024-07-24 12:44:18

字體：大中小

供稿：網(wǎng)友

復(fù)制代碼代碼如下:

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)從一張表查詢記錄插入到動(dòng)態(tài)創(chuàng)建的新表中
SET `@i`= `@i`+1;

END WHILE;
END

您可能感興趣的文章:

深入mysql創(chuàng)建自定義函數(shù)與存儲(chǔ)過(guò)程的詳解MySQL5創(chuàng)建存儲(chǔ)過(guò)程的示例在MySQL中創(chuàng)建帶有IN和OUT參數(shù)的存儲(chǔ)過(guò)程的方法MySQL實(shí)現(xiàn)創(chuàng)建存儲(chǔ)過(guò)程并循環(huán)添加記錄的方法mysql 存儲(chǔ)過(guò)程中變量的定義與賦值操作mysql存儲(chǔ)過(guò)程詳解MySQL存儲(chǔ)過(guò)程使用實(shí)例詳解mysql創(chuàng)建存儲(chǔ)過(guò)程實(shí)現(xiàn)往數(shù)據(jù)表中新增字段的方法分析

上一篇：mysql主從庫(kù)不同步問(wèn)題解決方法

下一篇：Mysql 數(shù)據(jù)庫(kù)更新錯(cuò)誤的解決方法