MySQL 分区

最新推荐文章于 2024-09-22 15:18:13 发布

Snoopy7713

最新推荐文章于 2024-09-22 15:18:13 发布

阅读量88

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/Snoopy7713/article/details/84163679

版权

mysql 专栏收录该内容

40 篇文章 0 订阅

订阅专栏

MYSQL 存储过程，定义告警日志分区

DROP TABLE IF EXISTS antiTamperLog;
CREATE TABLE antiTamperLog
(
   eventId              INT NOT NULL,
   tamperType           INT NOT NULL,
   filePath             VARCHAR(4096) NOT NULL,
   startTime            DATETIME NOT NULL,
   content              VARCHAR(2000)
)
ENGINE = INNODB
DEFAULT CHARSET = UTF8
PARTITION BY RANGE(TO_DAYS(startTime))
(
  PARTITION a201112 VALUES LESS THAN (TO_DAYS('2011-12-01'))
);

DROP TABLE IF EXISTS attacklog;
CREATE TABLE attacklog
(
   eventId              INT NOT NULL,
   startTime            DATETIME,
   logType              INT NOT NULL,
   attackPhase          VARCHAR(20),
   attackLevel          CHAR(1),
   srcIp                VARCHAR(39) NOT NULL,
   dstIp                VARCHAR(39) NOT NULL,
   attackProtocol       VARCHAR(10) NOT NULL,
   attackDomain         VARCHAR(100) NOT NULL,
   attackPath           VARCHAR(512) NOT NULL,
   attackParameter      VARCHAR(512) NOT NULL,
   ruleFileName         VARCHAR(20) NOT NULL,
   ruleId               INT NOT NULL,
   ruleDesc             VARCHAR(200) NOT NULL,
   websiteId            CHAR(36) NOT NULL,
   hostId               INT NOT NULL,
   content              VARCHAR(2000)
)
ENGINE = INNODB
DEFAULT CHARSET = UTF8
PARTITION BY RANGE(TO_DAYS(startTime))
(
  PARTITION p201112 VALUES LESS THAN (TO_DAYS('2011-12-01'))
);

/* 创建存储过程 */
DROP PROCEDURE IF EXISTS `cssp`.`Set_Partition`;

DELIMITER $$

CREATE PROCEDURE `cssp`.`Set_Partition`(IN prex VARCHAR(10),IN n INT,IN tablename VARCHAR(20))
BEGIN
DECLARE a INT;
DECLARE b INT;
DECLARE pname VARCHAR(20);
DECLARE pdate VARCHAR(20);

SET @a = 2012;
SET @b = 12;

WHILE @a <= n DO
  SET @b = 1;
  WHILE @b <= 12 DO
  #生成分区名称例如 201201
   IF @b < 10 THEN
     SET @pname = CONCAT(@a,'0',@b);
   ELSE
     SET @pname = CONCAT(@a,@b);
   END IF;
   #生成LESS THAN的时间例如分区名为201201则时间为201202
   IF @b < 9 THEN
     SET @pdate = CONCAT(@a,'0',@b+1,'01');
   ELSEIF @b = 12 THEN
     SET @pdate = CONCAT(@a+1,'01','01');
   ELSE
     SET @pdate = CONCAT(@a,@b+1,'01');
   END IF;

   SET @s1=CONCAT('ALTER TABLE ',tablename,' ADD PARTITION (PARTITION ',prex,@pname,' VALUES LESS THAN (TO_DAYS (''',DATE(@pdate),''')))');
   PREPARE stmt2 FROM @s1;
   EXECUTE stmt2;
   DEALLOCATE PREPARE stmt2;
   SET @b = @b + 1;
  END WHILE;
  SET @a = @a + 1;
END WHILE;
END$$

DELIMITER ;

CALL Set_Partition('a',2015,'attacklog');
CALL Set_Partition('p',2015,'antitamperlog');

#查看分区
SELECT partition_name
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_SCHEMA='cssp' AND table_name='attacklog' OR table_name='antiTamperLog';