MySQL 分区

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';