使用创建分表存储过程
set @field_list ='
`syslog_id` int(11) NOT NULL AUTO_INCREMENT,
`create_user` varchar(32) DEFAULT NULL,
PRIMARY KEY (`syslog_id`)
';
call branch_table('test', @field_list, 4, 2);
创建分表存储过程
DROP PROCEDURE IF EXISTS `branch_table`;
CREATE PROCEDURE `branch_table`(
IN p_table_name VARCHAR(200),
IN p_field_list VARCHAR(2048),
IN p_branch_size INT,
in p_lpad INT
)
BEGIN
/*定义变量*/
DECLARE m_begin_row INT DEFAULT 0;
WHILE m_begin_row<p_branch_size DO
/*构造语句*/
SET @MAIN_STRING = CONCAT('CREATE TABLE ', p_table_name, '_', LPAD(m_begin_row, p_lpad, 0), '(', p_field_list ,')ENGINE=InnoDB DEFAULT CHARSET=utf8;');
/*预处理*/
PREPARE main_stmt FROM @MAIN_STRING;
EXECUTE main_stmt;
SET m_begin_row=m_begin_row+1;
END WHILE;
END;
前期没有分表数据量太大后期拆表用的MySQL存储过程
简单的办法是直接写
--假设根据user_id分表,分成64张
insert into table_new_0000 select * from table_old where mod(user_id,64)=0;
insert into table_new_0001 select * from table_old where mod(user_id,64)=1;
一共64条sql,OK 搞定。但是这个一张表被全表扫描了64次,做的无用功比较多,而且导致停机时间比较长
创建分表
delimeter //
--- 全量脚本:
CREATE PROCEDURE sp_xf_move_item()
begin
declare v_exit int default 0;
declare v_spid bigint;
declare v_id bigint;
declare i int default 0;
declare c_table int;
--定义游标(要分拆的表,定义一个数量的截止时间)
declare c_ids cursor for select id,user_id from item_records_0000 where gmt_modified < '2010-8-25 00:00:00';
declare continue handler for not found set v_exit=1;
open c_ids;
repeat
--将需要的值装入变量
fetch c_ids into v_id,v_spid;
if v_exit = 0 then
set @vv_id = v_id;
--根据取模字段获取数据存在的表
select mod(v_spid,64) into c_table;
--组装动态sql
SET @SQL_CONTEXT =
CONCAT('insert into item_record_',
LPAD(c_table, 4, 0),
' select * from item_records_0000 where id = ?');
PREPARE STMT FROM @SQL_CONTEXT;
--执行sql
EXECUTE STMT using @vv_id;
DEALLOCATE PREPARE STMT;
end if;
set ii=i+1;
--100条提交一次,以提高效率,记得执行存储过程前设置auto_commit
if mod(i,100)=0 then commit;
end if;
until v_exit=1
end repeat;
close c_ids;
commit;
end;
//
set auto_commit=0;
call sp_xf_move_item();
添加数据
#### 增量脚本 ######
CREATE PROCEDURE sp_xf_add_item()
begin
declare v_exit int default 0;
declare v_spid bigint;
declare v_id bigint;
declare i int default 0;
declare c_table int;
declare c_ids cursor for select id,supplier_id from item_records_0000 where gmt_modified >= '2010-8-25 00:00:00';
declare continue handler for not found set v_exit=1;
open c_ids;
repeat
fetch c_ids into v_id,v_spid;
if v_exit = 0 then
set @vv_id = v_id;
set @v_row=0;
select mod(v_spid,64) into c_table;
--判断数据是否已经存在
SET @SQL_C =
CONCAT('select count(*) into @v_row from item_record_',
LPAD(c_table, 4, 0),
' where id = ?');
PREPARE STMT_C FROM @SQL_C;
EXECUTE STMT_C using @vv_id;
DEALLOCATE PREPARE STMT_C;
SET @SQL_INSERT =
CONCAT('insert into bbc_item_record_',
LPAD(c_table, 4, 0),
' select * from item_records_0000 where id = ?');
PREPARE STMT_I FROM @SQL_INSERT;
SET @SQL_DELETE =
CONCAT('DELETE FROM bbc_item_record_',
LPAD(c_table, 4, 0),
' where id = ?');
PREPARE STMT_D FROM @SQL_DELETE;
--如果数据已经存在,则先delete在insert
if @v_row>0 then
EXECUTE STMT_D using @vv_id;
DEALLOCATE PREPARE STMT_D;
end if;
EXECUTE STMT_I using @vv_id;
DEALLOCATE PREPARE STMT_I;
end if;
set ii=i+1;
if mod(i,100)=0 then commit;
end if;
until v_exit=1
end repeat;
close c_ids;
commit;
end;
//
call sp_xf_add_item()
分享到:
相关推荐
zxframe的demo ZxFrame为JAVA框架-支持JPA,多级缓存,读写分离,分库分表;支持通用分布式锁;...封装本地和远程任意缓存存储,使用极其简单,支持对缓存组删除 -->zxframe.cache.mgr.CacheManager
支持mysql和oracle存储过程,out参数、多结果集返回(1.6) 支持zookeeper协调主从切换、zk序列、配置zk化(1.6) 支持库内分表(1.6) 集群基于ZooKeeper管理,在线升级,扩容,智能优化,大数据处理(2.0开发版)...
传统的通用关系型数据库无法应对在数据规模剧增时导致的系统扩展性和性能问题(分库分表也不能很好解决) 传统关系数据库在数据结构变化时一般需要停机维护;空列浪费存储空间 因此,业界出现了一类面向半结构化数据...
大数据分表存储 提供开放式API 提供SDK配合使用 其他 开源数据源平台 整合了Gin框架和其他的通用功能,包括: 中间件 logger:使用logrus构建 auth:鉴权模块,使用密钥和秘密进行校验 exception:重写gin.Recovery...
封装本地和远程任意缓存存储,使用极其简单,支持对缓存组删除 -->zxframe.cache.mgr.CacheManager *.建议代理层使用nginx+keepalived,或其他类似的代替。反向代理,动静分离,请求结果缓存,静的资源也可以放CDN ...
在DB存储需求中,尽管业务不同,技术难点还是类似的,开源世界有很多DB中间件,解决方案也以通用方案为主,满足业务需要为前提,支持各种类型的需求。 Oceanus致力于打造一个功能简单、可依赖、易于上手、易于扩展...
│ 5_存储过程函数流程控制语句讲解.mp4 │ ├─新版MySQL DBA综合实战班 第05天 │ 1_课后作业讲解.mp4 │ 2_MySQL游标讲解.mp4 │ 3_MySQL触发器.mp4 │ 4_MySQL触发器课堂强化练习.mp4 │ 5_MySQL数字和时间类型...
12、支持频道绑定独立域名、各频道数据分表存储、自定义字段、后台按频道分级管理,帮助用户轻松建立大型站点。 13、支持频道绑定独立域名、各频道数据分表存储、自定义字段、后台按频道分级管理,帮助用户轻松建立...
Mycat的目标是以低成本将当前的独立数据库和应用程序平稳地迁移到云端,并解决由于数据存储和业务规模的快速增长而引起的瓶颈问题。 特性: 支持SQL92标准 遵守Mysql原生协议,跨语言,跨平台,跨数据库的通用中间件...
修复黑客路由器响应int类型可能超出数字范围Mycat的目标是以低成本将当前的独立数据库和应用程序平稳地迁移到云端,并解决由于数据存储和业务规模的快速增长而引起的瓶颈问题。MyCAT特性支持SQL92标准 遵守Mysql...
Mycat的目标是以低成本将当前的独立数据库和应用程序平稳地迁移到云端,并解决由于数据存储和业务规模的快速增长而引起的瓶颈问题。特性:支持SQL92标准遵守Mysql原生协议,跨语言,跨平台,跨数据库的通用中间件...
本系统不同于市面上的所谓MSSQL版本只是单纯的把数据库形式由ACCESS转为MSSQL,而是在程序中大量的时候存储过程、触发器,从而能够最大程度的发挥MSSQL的海量数据处理能力。 给你真实的页面执行速度体验。我们承诺绝...