Mysql对已有数据表进行分区

对于表分区的方法有几大类型

1.range分区

2.list分区

3.hash分区

4.key分区

本文通过range进行分区。

对于已存在的数据的表结果，解决思路是先创建一张空的分区表结构，然后把数据迁移至新的表，然后编写存储过程和定时每天增加一个新的分区。

1.创建一张新的分区表结构

CREATE TABLE `vehicle_coord_range` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `region_code` varchar(64) DEFAULT NULL COMMENT '城市(区域)CODE',
  `plate_num` varchar(64) DEFAULT NULL COMMENT '车牌号',
  `lon` varchar(64) DEFAULT NULL COMMENT '经度',
  `lat` varchar(64) DEFAULT NULL COMMENT '纬度',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `createTime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `remarks` varchar(255) DEFAULT NULL COMMENT '备注信息',
  `del_flag` varchar(64) DEFAULT '0' COMMENT '逻辑删除标记（0：显示；1：隐藏）',
  `direction` varchar(64) DEFAULT NULL COMMENT '行驶方向',
  PRIMARY KEY (`id`,`create_date`)
) ENGINE=InnoDB AUTO_INCREMENT=172203 DEFAULT CHARSET=utf8 COMMENT='订单(行程) 线路实时坐标' partition by range (TO_DAYS(create_date))( #range分区结构
  PARTITION vehicle_coord_1 VALUES LESS THAN (TO_DAYS('2017-05-22')),
  PARTITION vehicle_coord_2 VALUES LESS THAN (TO_DAYS('2017-05-23'))
);

2.将初始数据导入新的表

insert into vehicle_coord_range select * from 旧表; 

其中，可能会报出  table has no partition for value XXX

是因为联合主键中作为分区的 create_date 键值有小于数据的键值，分区的建区规则是 less than，需要手动添时间范围大于条件限制的分区，而且新建的分区数值不能小于原有分区。

ALTER TABLE vehicle_coord_range ADD PARTITION(
PARTITION vehicle_coord_20171122 VALUES LESS THAN (TO_DAYS('2017-11-22'))
);

通过 count() 函数查看数据导入是否完全，如果不完全，需要重新导入

3.查看分区信息

SELECT
  partition_name part, 
  partition_expression expr, 
  partition_description descr, 
  table_rows 
FROM
  INFORMATION_SCHEMA.partitions 
WHERE
  TABLE_SCHEMA = schema() 
  AND TABLE_NAME='vehicle_coord_range'; 

如果要删除表分区

ALTER TABLE vehicle_coord_range DROP PARTITION vehicle_coord_20171209;

4.创建存储过程

本存储过程直接创建在mavicat中

BEGIN
	
  -- 表名赋值
  set @p_name = (select concat('vehicle_coord_',date_format(date_sub(curdate(),interval - 2 day),'%Y%m%d'))from dual);

  -- 表的日期限制
  set @p_date = (select date_sub(curdate(),interval - 2 day)from dual);
	

  -- 添加分区sql语句
  set @p_sql = concat('ALTER TABLE vehicle_coord_range ADD PARTITION(PARTITION ',@p_name,' VALUES LESS THAN (TO_DAYS(\'',@p_date,'\')))');
	
  -- 预处理sql，其中stmt是一个变量
  PREPARE stmt FROM @p_sql; 
  -- 执行SQL语句
  EXECUTE stmt;
  -- 释放掉预处理段
  DEALLOCATE PREPARE stmt; -- 释放掉预处理段


END

5.设定时间来每天定时调用创建新的分区

查看是否开启事件

show variables like 'event_scheduler';

如果没有开启事件，需要先开启事件

set global event_scheduler = on;

调用存储过程

call set_vehicle_coord_partition