MySQL Stored Procedure for table splitting

The MySQL stored procedure is widely used. The following describes the MySQL Stored Procedure for table sharding. It is helpful for you to learn about the MySQL stored procedure.

Mysql tables or sub-tables have a certain amount of data, which may be or ..) at this time, it is very necessary to subscribe to the table, the simple way is to directly write
-- Assume that the table is divided into 64 tables by user_id.

 
 

  
  
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.

Although the MySQL stored procedure is not very familiar, I learned a little and wrote two scripts, one full + one incremental script to split the table.
The online database also practices 8 sub-tables. Each sub-table records are split into 64 sub-tables.
The full time is 150 minutes. When full data is used, several sub-tables can be run together. I run three sub-tables at the same time.
The incremental time is 4-minute and 4-minute run for each table Shard, which is 8 minutes in total. In this way, the downtime and application release take 20 minutes.

Script:

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

-- Define the table for which the cursor is to be split and define the end time of a quantity)

 
 

  
  
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  
 
 

-- Load the required value into a variable

 
 

  
  
fetch c_ids into v_id,v_spid;  
  
  
if v_exit = 0 then  
  
  
set @vv_id = v_id;  
 
 

-- Obtain the table with the data according to the modulo Field

 
 

  
  
select mod(v_spid,64) into c_table;  
 
 

-- Assemble dynamic SQL statements

 
 

  
  
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 at a time to improve efficiency. Remember to set auto_commit before executing the stored procedure.

 
 

  
  
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 ();
  
  
 
  
  
#### Incremental script ######
  
  
 
  
  
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> = '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

 
 

  
  
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;       
 
 

-- If the data already exists, delete

 
 

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

If the time between the full and incremental operations is long, you can set the time and perform the incremental operations several times to shorten the final downtime, you know...
Call sp_xf_add_item ()//
 

In-depth discussion on MySQL Lock Mechanism

Set of MySQL Fields

MySQL Field Type

Mysql foreign Key Usage Analysis

Detailed description of MySQL Data Table types