您的当前位置:首页 --> MYSQL教程 |
MySQL动态创建表,数据分表的存储过程 |
浏览次数:1077 关键词 ( ) |
复制代码 代码如下: 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 |
下一条 SQL计算timestamp的差值的方法 上一条 mysql数据库replace、regexp的用法 |
数千企业与站长的选择,7*24小时稳定运行
了解更多套餐详细