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

更多阅读前往-都市菜鸟

转载于:https://www.cnblogs.com/vliegen/archive/2011/08/11/2135598.html