创建管理表记录MYSQL自增自减问题 80C

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;

1个回答

mysql里不是有专门的数据库放元数据的吗,为啥还要自己来统计。

lzm519
lzm519 我小白,可以改下代码看看吗
9 个月之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问