通用分表存儲過程 使用創建分表存儲過程 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; 一共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()
