General table sharding procedure

The Java code 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 code 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 /* Define the variable */DECLARE m_begin_row int default 0; WHILE m_begin_row <p_branch_size DO/* Construction statement */SET @ MAIN_STRING = CONCAT ('create table', p_table_name ,'_', LPAD (m_begin_row, p_lpad, 0), '(', p_field_list, ') ENGINE = InnoDB default charset = utf8;');/* preprocessing */PREPARE main_stmt FROM @ MAIN_STRING; EXECUTE main_stmt; SET m_begin_row = m_begin_row + 1; end while; END, Into 64 pieces of Java code 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; A total of 64 SQL statements, OK. However, this table is scanned 64 times by the entire table, which is useless and causes a long downtime. the Java code delimeter // --- full script: 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; -- defines the cursor (table to be split, define a quantity deadline) declare c_ids cursor for select id, user_id from item_records_0000 where gmt_modified <'2017-8-25 00:00:00 '; declare continue handler for not f Ound set v_exit = 1; open c_ids; repeat -- load the required value into the variable fetch c_ids into v_id, v_spid; if v_exit = 0 then set @ vv_id = v_id; -- Obtain the table where data exists based on the modulo field select mod (v_spid, 64) into c_table; -- assemble dynamic 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; -- EXECUTE SQL execute stmt using @ vv_id; deallocate prepare stmt; end if; set ii = I + 1; -- submit 100 entries once, to improve efficiency, remember to set auto_commit if mod (I, 100) = 0 then commit; end if; until v_exit = 1 end repeat; close c_ids; commit; end before executing the stored procedure; // set auto_commit = 0; call sp_xf_move_item (); add data Java code #### incremental script ##### create procedure sp_xf_add_item () begin declare v_exit int default 0; declare v_s Pid 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> = '2017-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; -- determine whether the data already exists @ 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 ignore _', 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; -- if the data already exists, delete the data in 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 ()