mysql 分表 存储过程,通用分表存储过程

通用分表存储过程

使用创建分表存储过程

Java代码

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);

创建分表存储过程

Java代码

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

/*构造语句*/

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张

Java代码

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次，做的无用功比较多，而且导致停机时间比较长

创建分表

Java代码

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();

添加数据

Java代码

#### 增量脚本 ######

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()

http://www.dengb.com/Mysql/487735.htmlwww.dengb.comtruehttp://www.dengb.com/Mysql/487735.htmlTechArticle通用分表存储过程 使用创建分表存储过程 Java代码 set @field_list = `syslog_id` int(11) NOT NULL AUTO_INCREMENT, `create_user` varchar(32) DEFAULT NULL, PRIMARY KE...