1.功能就是自动添加删除分区，并记录到管理表FjJk_Partition_Manager里包括哪些表有分区、分区名称、开始时间、间隔天数、分区数等，刚学习代码的小白，参考了下博客的代码后自己瞎改成这样，应该逻辑跟语句有大问题。
下面是代码：
create table FjJk_Partition_Manager
(
TABLE_NAME VARCHAR(30),
PARTITION_NAME VARCHAR(30),
BEGIN_TIME datetime DEFAULT NULL,
INTERVAL_DAYS INT,
PARTITION_SUM INT
)
CREATE TABLE sales (
money int(11) NOT NULL,
date TIMESTAMP
)
ALTER TABLE sales PARTITION BY RANGE (UNIX_TIMESTAMP(date))(
PARTITION jk20190421 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-21')),
PARTITION jk20190422 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-22')),
PARTITION jk20190423 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-23')),
PARTITION jk20190424 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-24')),
PARTITION jk20190425 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-25')),
PARTITION jk20190426 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-26')),
PARTITION jk20190427 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-27'))
)
INSERT INTO sales VALUES(1066,'2019-04-19 15:20:59');
INSERT INTO sales VALUES(1088,'2019-04-20 18:20:59');
INSERT INTO sales VALUES(1066,'2019-04-21 17:20:59');
INSERT INTO sales VALUES(1099,'2019-04-22 20:21:59');
INSERT INTO sales VALUES(1088,'2019-04-23 18:20:59');
INSERT INTO sales VALUES(1066,'2019-04-24 12:30:59');
INSERT INTO sales VALUES(1066,'2019-04-25 21:20:59');
INSERT INTO sales VALUES(1066,'2019-04-26 22:30:59');
CREATE DEFINER=root@localhost PROCEDURE proc_add_drop_partition(IN v_tablename VARCHAR(50),v_drop_interval INT,v_add_interval INT)
BEGIN
-- 传入变量: v_drop_interval 删除日 v_add_interval 添加日
DECLARE v_add_interval_1 INT;
DECLARE table_name varchar(30);
DECLARE udata TIMESTAMP;
SET table_name='FjJk_Partition_Manager';
SET v_add_interval_1=v_add_interval+1;
START TRANSACTION;
SET @udata=CONCAT('select data from',v_tablename);
SET @c_add= CONCAT('jk',DATE_FORMAT(DATE_ADD(NOW(),INTERVAL v_add_interval DAY),'%Y%m%d'));
SET @c_drop=CONCAT('jk',DATE_FORMAT(DATE_SUB(NOW(),INTERVAL v_drop_interval DAY),'%Y%m%d'));
SET @s=CONCAT('alter table ',v_tablename,' drop partition ', @c_drop);
SET @t=CONCAT('alter table ',v_tablename,' add partition ','(','partition ', @c_add, ' VALUES LESS THAN','(',UNIX_TIMESTAMP(DATE(DATE_ADD(NOW(),INTERVAL v_add_interval_1 DAY))),')',')');
SET @i=CONCAT('insert into ',table_name,' VALUES','(',v_tablename,@c_add,@udata, TimeStampDiff(DAY,@udata,DATE_FORMAT(now(), '%Y-%m-%d')),count(v_tablename),')');
SELECT @s;
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT @t;
PREPARE stmt FROM @t;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT @i;
PREPARE stmt FROM @i;
EXECUTE stmt;
COMMIT;
END
CREATE DEFINER=root@localhost
EVENT NewEvent
ON SCHEDULE EVERY 1 DAY STARTS '2019-04-27 01:20:00'
ON COMPLETION PRESERVE
ENABLE
DO
BEGIN
CALL proc_add_drop_partition('sales',7,1);
END;
