MySQL动态创建表，数据分表的存储过程

121 阅读 0 评论 80 点赞

我是靠谱客的博主伶俐小鸭子，最近开发中收集的这篇文章主要介绍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 