使用创建分表存储过程
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<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张
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;
创建分表
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;
- //
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;
- //
