用的工具是:Navicat for MySQL,在其中定义了几个存储过程。要拆分的大表:tcc_method_coverage,其中有一个字段名:table_name,我们就是要根据table_name的值,将tcc_method_coverage表的数据进行分组,譬如,table_name的值为abc,那么,就将tcc_method_coverage表中所有table_name值为abc的记录全部“挪移”到一张新表abc_mc,并且,给这张新表建立索引。
总共5个存储过程:
总执行入口(transfer_method_data):
begin
declarev_table_namevarchar(200);
declareflagintdefault0;
declarecurlcursorforselectDISTINCTtable_namefromtcc_method_coverage;
declarecontinuehandlerforsqlstate'02000'setflag = 1;
opencurl;
fetchcurlintov_table_name;
WHILE(flag != 1) DO
call create_table_if_not_exist(CONCAT(v_table_name, '_mc'));
call drop_table_index(CONCAT(v_table_name, '_mc'));
call insert_data_to_table(CONCAT(v_table_name, '_mc'), v_table_name);
call create_table_index(CONCAT(v_table_name, '_mc'));
fetchcurlintov_table_name;
endWHILE;
closecurl;
end
create_table_if_not_exist:
参数类型是:
`new_table_name`varchar(200)
BEGIN
declarev_create_table_sqlvarchar(1000);
set@v_create_table_sql = CONCAT('create table if not exists ', new_table_name, ' (
id int(11)NOTNULLAUTO_INCREMENT,
namevarchar(500)DEFAULTNULL,
class_coverage doubleDEFAULTNULL,
class_ratio varchar(100)DEFAULTNULL,
method_coverage doubleDEFAULTNULL,
method_ratio varchar(100)DEFAULTNULL,
block_coverage doubleDEFAULTNULL,
block_ratio varchar(100)DEFAULTNULL,
line_coverage doubleDEFAULTNULL,
line_ratio varchar(100)DEFAULTNULL,
class_fail char(1)DEFAULTNULL,
method_fail char(1)DEFAULTNULL,
block_fail char(1)DEFAULTNULL,
line_fail char(1)DEFAULTNULL,
class_id int(11)DEFAULTNULL,
table_name varchar(200)DEFAULTNULL,
dateTime datetime DEFAULTNULL,
primarykey(id)
) ENGINE=InnoDB DEFAULTCHARSET=gbk');
#selectv_create_table_sql;
PREPAREstmtfrom@v_create_table_sql;
EXECUTEstmt;
END
drop_table_index:
参数类型是:
`new_table_name` varchar(200)
BEGIN
declarev_index_namevarchar(500);
declarev_select_index_sqlvarchar(500);
declarev_drop_index_sqlvarchar(500);
setv_index_name = CONCAT(new_table_name,'_cid');
selectcount(*)into@cntfrominformation_schema.statisticswheretable_name=new_table_nameandindex_name=v_index_name;
if @cnt > 0 then
set@v_drop_index_sql = CONCAT('drop index ', v_index_name,' on ', new_table_name);
preparestmtfrom@v_drop_index_sql;
EXECUTEstmt;
endif;
END
insert_data_to_table:
参数类型是:
`new_table_name`varchar(200),`table_name`varchar(200)
BEGIN
declarev_insert_sqlvarchar(1000);
set@v_insert_sql = CONCAT('insert into ', new_table_name, ' (
name,
class_coverage,
class_ratio,
method_coverage,
method_ratio,
block_coverage,
block_ratio,
line_coverage,
line_ratio,
class_fail,
method_fail,
block_fail,
line_fail,
class_id,
table_name,
dateTime
) SELECTname,
class_coverage,
class_ratio,
method_coverage,
method_ratio,
block_coverage,
block_ratio,
line_coverage,
line_ratio,
class_fail,
method_fail,
block_fail,
line_fail,
class_id,
table_name,
dateTime
FROMtcc_method_coverage
WHEREtable_name = \'', table_name,'\'');
PREPAREstmtfrom@v_insert_sql;
EXECUTEstmt;
END
create_table_index:
参数类型是:
`new_table_name`varchar(200)
BEGIN
declarev_index_namevarchar(500);
declarev_table_columnvarchar(500);
declarev_create_index_sqlvarchar(500);
setv_index_name = CONCAT(new_table_name,'_cid');
setv_table_column = CONCAT(new_table_name,'(','class_id',')');
#createindex
set@v_create_index_sql = CONCAT('create index ', v_index_name,' on ', v_table_column);
preparestmtfrom@v_create_index_sql;
EXECUTEstmt;
END
也是参考了网上的一些资料,然后自己跌跌撞撞地写出来的mysql存储过程代码,记录在此,权当一份记忆(毕竟,咱也稍微写过一下存储过程,呵呵)。当然拆表的事情也可以通过java+jdbc去做。不过,在这里,的确是存储过程更直接方便一些。