一、数据库分区
CREATE TABLE test_log (
time datetime ,
msg VARCHAR ( 2000 )
)
alter table test_log partition by range columns ( time ) (
partition p20210801 values less than( '2021-08-01 00:00:00' )
) ;
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' ) ;
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;
END ;
CALL createPartition ( ) ;
SELECT CONCAT( 'alter table test_log add partition (partition p111 values less than(\'' , NOW ( ) , '\'))' ) ;
三、定时
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
CALL createPartition( ) ;