mysql数据库分区-根据时间分区

一、数据库分区

-- 创建表
CREATE TABLE test_log (
	time datetime,
  msg VARCHAR ( 2000 )
-- 	id INT NOT NULL AUTO_INCREMENT,
-- 	PRIMARY KEY (id)
)
-- 创建分区
alter table test_log partition by range columns(time)(
-- 	在日期之前创建分区
	partition p20210801 values less than('2021-08-01 00:00:00')
-- 	partition p20210802 values less than('2021-08-02'),
-- 	partition p20210803 values less than('2021-08-03')
-- 	partition p0 values less than maxvalue
);
-- 添加分区
alter table test_log add partition (partition p0 values less than maxvalue);

alter table test_log add partition (partition p20210804 values less than(TO_DAYS('2021-08-04')));
alter table test_log add partition (partition p20210805 values less than('2021-08-05'));

-- 删除分区
alter table test_log drop partition p0;
alter table test_log drop partition p111;
alter table test_log drop partition p20210801;

-- 添加数据
insert into test_log(time,msg) values('2021-08-01 10:11:13', 'hi');
insert into test_log(time,msg) values('2021-08-02 10:12:10', 'ni');
insert into test_log(time,msg) values('2021-08-03 10:12:10', 'hao');
insert into test_log(time,msg) values('2021-08-04 10:12:10', 'hao');
-- 查询分区
select partition_name, partition_description as val from information_schema.partitions
where table_name='test_log' and table_schema='demo1';

SELECT * FROM test_log
SELECT * FROM test_log1

DROP TABLE test_log
DROP TABLE test_log1

二、存储过程

-- 删除存储过程
DROP PROCEDURE IF EXISTS createPartition;

-- 创建存储过程 创建分区
CREATE PROCEDURE createPartition () BEGIN
	DECLARE str VARCHAR(100);
	DECLARE date VARCHAR(50) DEFAULT DATE_FORMAT( DATE_ADD(NOW(), interval 2 minute), '%Y-%m-%d %H:%i:00' );-- 定义变量
-- 		date TIMESTAMP DEFAULT NOW();-- 定义变量
-- 		SET @words = date;
-- 		alter table test_log add partition (partition p0 values less than(date));

		SET str = CONCAT('alter table test_log add partition (partition p',DATE_FORMAT( DATE_ADD(NOW(), interval 2 minute), '%Y%m%d%H%i' ),' values less than(\'',date,'\'))');
		SET @sql_v = str;
		prepare stmt from @sql_v;
		EXECUTE stmt;
		
-- 		alter table test_log add partition (partition p111 values less than(date));

END;

-- 调用存储过程
CALL createPartition ();

SELECT CONCAT('alter table test_log add partition (partition p111 values less than(\'',NOW(),'\'))');

三、定时

-- 查看事件任务状态 OFF未启用
SHOW VARIABLES LIKE 'event%';
SHOW VARIABLES LIKE 'event_scheduler';

SELECT @@event_scheduler;
-- 开启事件任务
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

SET GLOBAL event_scheduler=0;
-- 查看事件任务
SHOW EVENTS;

-- 查看事件任务错误 - 权限不足
SELECT * FROM mysql.EVENT
-- 开启事件任务
ALTER EVENT test_log_event ON COMPLETION PRESERVE ENABLE
-- 关闭事件任务
ALTER EVENT test_log_event ON COMPLETION PRESERVE DISABLE
-- 删除事件任务
DROP EVENT [IF EXISTS] event_name


-- 若计划任务存在则删除
DROP EVENT IF EXISTS test_log_event;
-- 创建计划任务
CREATE EVENT test_log_event
  ON SCHEDULE EVERY 1 MINUTE
-- 	开始时间
  STARTS CURRENT_TIMESTAMP
-- 	保存
  ON COMPLETION PRESERVE disable
DO
-- BEGIN 
-- 执行存储过程
  CALL createPartition();
-- END $$