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

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