MySQL存储过程动态创建表，数据分表
01 BEGIN
02 DECLARE `@i` int(11);
03 DECLARE `@siteCount` int(11);
04 DECLARE `@sqlstr` VARCHAR(2560);
05 DECLARE `@sqlinsert` VARCHAR(2560); //以上声明变量
06
07 SELECT COUNT(0) into `@siteCount` FROM tbl_base_site; //计算表tbl_base_site的记录总条数
08
09 set `@i`=1;
10
11 WHILE (`@i`-1)*300<`@siteCount` DO //while循环执行
12 SET @sqlstr = CONCAT('CREATE TABLE tbl_base_syslog',`@i`,'(syslog_id INT(11) AUTO_INCREMENT PRIMARY KEY,
13 create_user VARCHAR(32),
14 description text,
15 create_time datetime,
16 site_id INT(11),
17 ip VARCHAR(64),
18 version_id SMALLINT(2),
19 module_identity VARCHAR(64),
20 right_name VARCHAR(64)
21 )');
22 prepare stmt from @sqlstr;
23 execute stmt;
24 //以上实现动态创建表
25 SET @sqlinsert = CONCAT('INSERT INTO tbl_base_syslog',`@i`,'(
26 syslog_id,
27 create_user,
28 description,
29 create_time,
30 site_id,
31 ip,
32 version_id,
33 module_identity,
34 right_name)
35 SELECT syslog_id,
36 create_user,
37 description,
38 create_time,
39 site_id,
40 ip,
41 version_id,
42 module_identity,
43 right_name
44 FROM tbl_base_syslog
45 WHERE site_id IN (select tbs.site_id from (select site_id from tbl_base_site limit ',`@i`*300,',300) as tbs)
46 ORDER BY syslog_id');
47 prepare stmt from @sqlinsert;
48 execute stmt;
49 //以上实现从一张表查询记录插入到动态创建的新表中
50 SET `@i`= `@i`+1;
51
52 END WHILE;
53
54 END
