MySQL自动按天加分区(很low版)

-- 这个是加分区的函数

delimiter $$
CREATE DEFINER=`root`@`%` PROCEDURE `数据库名`.`存名`(IN schemaname varchar(64),IN tablename varchar(64))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '用于新建分区'
BEGIN 
declare p_name varchar(64);
declare p_time varchar(64);
SELECT from_days(MAX(t.PARTITION_DESCRIPTION)+1) into p_time FROM information_schema.`PARTITIONS` t WHERE t.TABLE_SCHEMA=schemaname AND t.TABLE_NAME=tablename;
SELECT date_format(from_days(MAX(t.PARTITION_DESCRIPTION)),'p%Y%m%d') into p_name FROM information_schema.`PARTITIONS` t WHERE t.TABLE_SCHEMA=schemaname AND t.TABLE_NAME=tablename;

set @v_add=concat('alter TABLE ',schemaname,'.',tablename,' ADD PARTITION (PARTITION ',p_name,' VALUES LESS THAN (to_days("',p_time,'")) ENGINE = InnoDB);');
PREPARE stmt from @v_add;
EXECUTE stmt;

END;$$


-- 加分区用的event -- 
delimiter $$
CREATE DEFINER=`root`@`%` EVENT `数据库名`.`尸检名` ON 
SCHEDULE EVERY 1 DAY STARTS '2024-05-01 00:00:00' ON COMPLETION PRESERVE ENABLE 
COMMENT 'xxx' 
DO BEGIN
CALL `数据库名`.`存名`('数据库名','要加的表名');
END;$$




-- 开启events
show variables like 'event%';
-- 如果是off
-- set global event_scheduler=on;

-- 检查事件状态
select EVENT_SCHEMA,EVENT_NAME,DEFINER,STATUS,LAST_EXECUTED,EVENT_COMMENT from information_schema.events;