mysql range分区

一、对已有表分区和创建： https://blog.csdn.net/orangleliu/article/details/57088338

1、在已经存在表中，修改分区表

alter table t_fenqu PARTITION by RANGE (TO_DAYS(create_date))
(
	PARTITION p0 VALUES LESS THAN (TO_DAYS('2018-06-01')),
	PARTITION pmax VALUES LESS THAN MAXVALUE
);

a、异常：A PRIMARY KEY must include all columns in the table's partitioning function （需要将分区字段设置成主键）

2、增、删分区

-- 删除分区
alter table t_fenqu DROP PARTITION pmax;

-- 添加分区
alter table t_fenqu add PARTITION (
	PARTITION p1 VALUES LESS THAN (TO_DAYS('2018-07-01')),
	PARTITION p2 VALUES LESS THAN (TO_DAYS('2018-08-01')),
	PARTITION p3 VALUES LESS THAN (TO_DAYS('2018-09-01')),
	PARTITION pmax VALUES LESS THAN MAXVALUE
);

3、查询分区

SELECT
  partition_name, 
  partition_expression, 
  partition_description, 
  FROM_DAYS(partition_description), 
  table_rows 
FROM
  INFORMATION_SCHEMA.partitions 
WHERE
  TABLE_SCHEMA = SCHEMA() 
  AND TABLE_NAME='t_fenqu'; --这里是表名

二、mysql表分区  https://blog.csdn.net/qq173684423/article/details/53994264

三、存储过程 自动创建时间分区

DROP PROCEDURE IF EXISTS pro_auto_create_partitionByDate;
CREATE PROCEDURE pro_auto_create_partitionByDate(
	in dataBaseName varchar(50), in tableName varchar(50)
)x_end:BEGIN
	-- 变量声明 Start
	DECLARE x_date VARCHAR(50);
-- 	DECLARE x_exist_par_max_sql VARCHAR(255);
	-- 变量声明 End


	-- 查询当前时间 加上一个月
	select DATE_FORMAT(date_add(NOW(), INTERVAL 1 MONTH),'%Y-%m') into x_date from dual;
	
	-- 拼接添加 分区SQL
	SET @x_sql = CONCAT('alter table ',tableName,' add PARTITION(', 
								'PARTITION ','`p',x_date,'-01`',' VALUES LESS THAN ('
								'TO_DAYS(\'',x_date,'-01','\')','))'
								);
						SELECT @x_sql;  
           PREPARE stmt2 FROM @x_sql;  
           EXECUTE stmt2;  
           DEALLOCATE PREPARE stmt2;
	COMMIT;
end x_end;

四、创建事件

-- 查看定时任务是否开启
show variables like '%event_scheduler'; 
-- 没有开启 开启一下
SET GLOBAL event_scheduler = ON;

-- 创建事件 
DELIMITER $$  
drop event if exists auto_create_partition_time  $$  
create event auto_create_partition_time 
	on schedule 
	every 1 minute  
	starts sysdate()  
do  
BEGIN  
    call pro_auto_create_partitionByDate('mytest','t_fenqu');
END$$  
delimiter ;