存储过程
背景:因单表数据太大, 需要表按时间分区 分区字段 down_time 按天分 要求自动创建
创建分区表
mysql的分区字段,必须包含在主键字段内
常见错误提示
错误提示:#1503
A PRIMARY KEY MUST INCLUDE ALL COLUMNS IN THE TABLE'S PARTITIONING FUNCTION
MySQL主键的限制,每一个分区表中的公式中的列,必须在主键/unique key 中包括
-- 添加存储过程(添加日分区的存储过程):
DELIMITER $$
DROP PROCEDURE IF EXISTS `time_partition_procedure`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `time_partition_procedure`()
BEGIN
SELECT REPLACE(b.partition_name,'p','') INTO @in_date FROM information_schema.PARTITIONS b WHERE b.TABLE_SCHEMA='db10' and b.table_name ='msg_down_list' ORDER BY b.partition_ordinal_position DESC LIMIT 1;
SET @max_date= DATE_ADD(@in_date,INTERVAL 1 DAY)+0 ;
SET @date= DATE_ADD(@in_date,INTERVAL 1 DAY)+0 ;
SET @sql=CONCAT('ALTER TABLE msg_down_list add PARTITION (PARTITION p',@date,' VALUES LESS THAN (TO_DAYS(''',@max_date,''')));');
SELECT @sql;
SELECT @max_date;
PREPARE strsql FROM @sql; #预执行sql
EXECUTE strsql; #执行sql
DEALLOCATE PREPARE strsql; #释放sql
COMMIT;
END$$
DELIMITER ;
-- 删除存储过程
drop procedure if exists p_del_slow;
drop procedure if exists p_del_error;
-- 列出所有存储过程
select specific_name from mysql.proc;
-- 查看某一个存储过程的具体内容
select body from mysql.proc where specific_name = 'p_del_slow';
-- 调用存储过程
call p_del_slow();
定时任务
-- 查看定时配置开启
show variables like '%event_sche%';
set global event_scheduler=on;
-- 添加定时任务(创建每天执行存储的事件 每天凌晨3点执行):
DELIMITER $$
CREATE EVENT `db10`.`time_partition_event`
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(CURRENT_DATE+1,INTERVAL 3 HOUR)
ON COMPLETION PRESERVE
ENABLE DO
BEGIN
CALL time_partition_procedure();
END$$
DELIMITER ;
-- 查看本机所有的事件
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
-- 查看事件任务
SHOW EVENTS ;
-- 删除定时任务
drop event if exists time_partition_event;
-- 开启定时任务
alter event time_partition_event on completion preserve enable;
-- 关闭定时任务
alter event time_partition_event on completion preserve disable;