mysql数据库分区

目录

1、创建表并添加数据

2、手动建立分区

3、查询所有分区信息

4、查询某个分区信息

5、自动建立分区(注意：// 是存储过程中的转义符，这里不是注释)

6、手动测试

7、设置定时器

---

1、创建表并添加数据

​
create table test_log
(
	time datetime,
	msg varchar(256)
)

insert into test_log VALUES ('2021-07-15',"1");
insert into test_log VALUES ('2021-07-16',"2");
insert into test_log VALUES ('2021-08-15',"3");
insert into test_log VALUES ('2021-08-16',"4");
insert into test_log VALUES ('2021-09-15',"5");
insert into test_log VALUES ('2021-09-16',"6");
insert into test_log VALUES ('2021-10-15',"7");
insert into test_log VALUES ('2021-10-16',"8");

2、手动建立分区

alter table test_log partition by range columns(time)(
	partition p202107 values less than('2021-08-01'),
	partition p202108 values less than('2021-09-01'),
	partition p202119 values less than('2021-10-01'),
	partition p202110 values less than('2021-11-01')
);

3、查询所有分区信息

select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='test_log';

4、查询某个分区信息

select * from test_log partition (p202108);

5、自动建立分区(注意：// 是存储过程中的转义符，这里不是注释)

# 月分区统计规则：p202111表示的是统计11月这个月的数据,即<2021-12-01 00:00:00的数据
DELIMITER //
#该表所在数据库名称
USE `partition_test` //
DROP PROCEDURE
IF
	EXISTS `create_partition_by_month` //
	CREATE PROCEDURE `create_partition_by_month` ( IN_SCHEMANAME VARCHAR ( 64 ), IN_TABLENAME VARCHAR ( 64 ) ) BEGIN
	DECLARE
		ROWS_CNT INT UNSIGNED;
	DECLARE
		BEGINTIME TIMESTAMP;
	DECLARE
		ENDTIME INT UNSIGNED;
	DECLARE
		PARTITIONNAME VARCHAR ( 16 );
	DECLARE
		ENDTIME_DATETIME VARCHAR ( 30 );
	# 获取下个月的第一天
	SET BEGINTIME = DATE_FORMAT(DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH),'%Y-%m-%d');
    select BEGINTIME;
	# 设置分区名称
	SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y%m' );
    select PARTITIONNAME;
	SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 MONTH );
	select endtime;
	SET ENDTIME_DATETIME = FROM_UNIXTIME( ENDTIME );
    select ENDTIME_DATETIME;
	SELECT
		COUNT(*) INTO ROWS_CNT 
	FROM
		information_schema.PARTITIONS 
	WHERE
		table_schema = IN_SCHEMANAME 
		AND table_name = IN_TABLENAME 
		AND partition_name = PARTITIONNAME;
	IF
		ROWS_CNT = 0 THEN	
			SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`', ' ADD PARTITION (PARTITION ', PARTITIONNAME, " VALUES LESS THAN ('", ENDTIME_DATETIME, "') ENGINE = InnoDB);" );
		PREPARE STMT 
		FROM
			@SQL;
		EXECUTE STMT;
		DEALLOCATE PREPARE STMT;
		ELSE SELECT
			CONCAT( "partition `", PARTITIONNAME, "` for table `", IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists" ) AS result;	
	END IF;
END //
DELIMITER;

6、手动测试

call create_partition_by_month("partition_test", "test_log");

7、设置定时器

DELIMITER $$
#该表所在的数据库名称
USE `partition_test`$$
CREATE EVENT IF NOT EXISTS `Month_partition_test_log`
ON SCHEDULE EVERY 1 hour   #执行周期，还有天、月等等
STARTS '2020-08-10 17:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating partitions By month'
DO BEGIN
    #调用刚才创建的存储过程，第一个参数是数据库名称，第二个参数是表名称
    CALL partition_test.create_partition_by_month("partition_test", "test_log");
END$$
DELIMITER ;