用的工具是: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
- declare v_table_name varchar(200);
- declare flag int default 0;
- declare curl cursor for select DISTINCT table_name from tcc_method_coverage;
- declare continue handler for sqlstate '02000' set flag = 1;
- open curl;
- fetch curl into v_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'));
- fetch curl into v_table_name;
- end WHILE;
- close curl;
- end
create_table_if_not_exist:
参数类型是:
- `new_table_name` varchar(200)
- BEGIN
- declare v_create_table_sql varchar(1000);
- set @v_create_table_sql = CONCAT('create table if not exists ', new_table_name, ' (
- id int(11) NOT NULL AUTO_INCREMENT,
- name varchar(500) DEFAULT NULL,
- class_coverage double DEFAULT NULL,
- class_ratio varchar(100) DEFAULT NULL,
- method_coverage double DEFAULT NULL,
- method_ratio varchar(100) DEFAULT NULL,
- block_coverage double DEFAULT NULL,
- block_ratio varchar(100) DEFAULT NULL,
- line_coverage double DEFAULT NULL,
- line_ratio varchar(100) DEFAULT NULL,
- class_fail char(1) DEFAULT NULL,
- method_fail char(1) DEFAULT NULL,
- block_fail char(1) DEFAULT NULL,
- line_fail char(1) DEFAULT NULL,
- class_id int(11) DEFAULT NULL,
- table_name varchar(200) DEFAULT NULL,
- dateTime datetime DEFAULT NULL,
- primary key(id)
- ) ENGINE=InnoDB DEFAULT CHARSET=gbk');
- #select v_create_table_sql;
- PREPARE stmt from @v_create_table_sql;
- EXECUTE stmt;
- END
drop_table_index:
参数类型是:
- `new_table_name` varchar(200)
- BEGIN
- declare v_index_name varchar(500);
- declare v_select_index_sql varchar(500);
- declare v_drop_index_sql varchar(500);
- set v_index_name = CONCAT(new_table_name, '_cid');
- select count(*) into @cnt from information_schema.statistics where table_name=new_table_name and index_name=v_index_name;
- if @cnt > 0 then
- set @v_drop_index_sql = CONCAT('drop index ', v_index_name, ' on ', new_table_name);
- prepare stmt from @v_drop_index_sql;
- EXECUTE stmt;
- end if;
- END
insert_data_to_table:
参数类型是:
- `new_table_name` varchar(200),`table_name` varchar(200)
- BEGIN
- declare v_insert_sql varchar(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
- ) SELECT 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
- FROM tcc_method_coverage
- WHERE table_name = \'', table_name, '\'');
- PREPARE stmt from @v_insert_sql;
- EXECUTE stmt;
- END
create_table_index:
参数类型是:
- `new_table_name` varchar(200)
- BEGIN
- declare v_index_name varchar(500);
- declare v_table_column varchar(500);
- declare v_create_index_sql varchar(500);
- set v_index_name = CONCAT(new_table_name, '_cid');
- set v_table_column = CONCAT(new_table_name, '(', 'class_id', ')');
- #create index
- set @v_create_index_sql = CONCAT('create index ', v_index_name, ' on ', v_table_column);
- prepare stmt from @v_create_index_sql;
- EXECUTE stmt;
- END
也是参考了网上的一些资料,然后自己跌跌撞撞地写出来的mysql存储过程代码,记录在此,权当一份记忆(毕竟,咱也稍微写过一下存储过程,呵呵)。当然拆表的事情也可以通过java+jdbc去做。不过,在这里,的确是存储过程更直接方便一些。
转载于:https://blog.51cto.com/memory/1026976