MySql自动分区_MySQL添加分区

自动分区需要开启MySql中的事件调度器,可以通过如下命令查看是否开启了调度器

show variables like '%scheduler%';

如果没开启的话通过如下指令开启

SET GLOBAL event_scheduler = 1;

①.、创建一个分区表

CREATE TABLE sales (
id INT AUTO_INCREMENT,
amount DOUBLE NOT NULL,
createTime DATETIME NOT NULL,
PRIMARY KEY(id, createTime)
) ENGINE=Innodb
PARTITION BY RANGE (YEAR(createTime))
(
PARTITION p_2010 VALUES LESS THAN (2011),#2011年以前一个分区
PARTITION p_2011 VALUES LESS THAN (2012) #2011-2012一个分区
)

查看当前表分区信息：

   SELECT PARTITION_NAME,PARTITION_DESCRIPTION,PARTITION_METHOD,TABLE_ROWS,CREATE_TIME
   FROM information_schema.PARTITIONS
   WHERE TABLE_SCHEMA = 'demo' AND TABLE_NAME = 'sales' ORDER BY partition_description DESC LIMIT 1000;

新建一个存储过程用于给分区表新增分区

DROP PROCEDURE IF EXISTS create_Partition ;
CREATE PROCEDURE create_Partition (IN databaseName VARCHAR(50),IN tableName VARCHAR(50),IN partitionNamePrefix VARCHAR(50))
L_END:BEGIN
 DECLARE MAX_PARTITION_DESCRIPTION VARCHAR(255) DEFAULT "";
 DECLARE P_NAME VARCHAR(255) DEFAULT "";      
 DECLARE P_DESCRIPTION VARCHAR(255) DEFAULT "";  
 DECLARE i INT DEFAULT 1;
 DECLARE ISEXIST_PARTITION VARCHAR(255) DEFAULT "";
 DECLARE PARTITION_COUNT INT DEFAULT 1; #一次性增加一个分区数

 SELECT IFNULL(PARTITION_NAME,"") INTO ISEXIST_PARTITION FROM information_schema.PARTITIONS
  WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName  LIMIT 1 ;
 
 IF ISEXIST_PARTITION <=> "" THEN
   SELECT "Partition table not is exist" AS "*****ERROR*****";
   LEAVE  L_END;
 END IF;

 SELECT IFNULL(partition_description,"") INTO MAX_PARTITION_DESCRIPTION  FROM information_schema.PARTITIONS
  WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName ORDER BY partition_description DESC LIMIT 1;

 IF MAX_PARTITION_DESCRIPTION <=> "" THEN
   SELECT "Partition table is error" AS "*****ERROR*****";
   LEAVE  L_END;
 END IF;
 
SET MAX_PARTITION_DESCRIPTION = REPLACE(MAX_PARTITION_DESCRIPTION, '\'', '');
WHILE i <= PARTITION_COUNT DO
       SET P_DESCRIPTION = MAX_PARTITION_DESCRIPTION◆i;
       SET P_NAME = REPLACE(P_DESCRIPTION, '-', '');
       SET @S=CONCAT('ALTER TABLE ',tableName,' ADD PARTITION  (PARTITION ',partitionNamePrefix,P_NAME,' VALUES LESS THAN (',P_DESCRIPTION,'))');
       SELECT @S;
       PREPARE stmt2 FROM @S;
       EXECUTE stmt2;
       DEALLOCATE PREPARE stmt2;
       SET i = i ◆ 1 ;
END WHILE;          
END L_END;

创建一个事件定时器用于定时创建分区

DELIMITER $$
CREATE EVENT auto_set_partitions
 ON SCHEDULE
 EVERY 5 MINUTE
 COMMENT 'auto set partitions for table '
   DO
  BEGIN
CALL create_Partition('mysql','test','p');
  END $$

以上就是土嘎嘎小编为大家整理的MySql自动分区相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用,就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！