根据表的某个字段拆分大表成多个小表，以前做的，留作笔记-CSDN博客

用的工具是：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