mysql自动按时间分区实例

#添加删除表分区存储过程
DELIMITER || 
DROP PROCEDURE IF EXISTS drop_Partition || 
CREATE PROCEDURE drop_Partition (IN databaseName VARCHAR(50),IN tableName VARCHAR(50)) 
L_END:BEGIN
   DECLARE i INT DEFAULT 0;
   WHILE i <= 2 DO
   SELECT PARTITION_NAME INTO @MIN_PARTITION
   FROM information_schema.PARTITIONS
   WHERE TABLE_SCHEMA = databaseName AND TABLE_NAME = tableName
   ORDER BY partition_description ASC LIMIT 1;
  
	SET @S=CONCAT('ALTER TABLE ',tableName,' DROP PARTITION ',@MIN_PARTITION);
	PREPARE stmt FROM @s;
        EXECUTE stmt;
        SELECT @s;
	SET i=i+1;
   END WHILE;
END L_END;|| 
DELIMITER ;

DELIMITER||
DROP EVENT IF EXISTS auto_drop_partitions||
CREATE EVENT auto_drop_partitions
	NO SCHEDULE
	EVERY 1 DAY STARTS '2016-12-01 23:59:59'
	BEGIN
	CALL drop_Partition('adverttj','records');
	END||
DELIMITER ;


CALL drop_Partition('adverttj','records');

SELECT partition_description INTO @MIN_PARTITION
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'adverttj' AND TABLE_NAME = 'records'
ORDER BY partition_description ASC LIMIT 1;

ALTER TABLE `records` DROP PARTITION p20161128

#查看分区分区
SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='records'


#添加删除表分区存储过程
DELIMITER || 
DROP PROCEDURE IF EXISTS drop_Partition || 
CREATE PROCEDURE drop_Partition (IN databaseName VARCHAR(50),IN tableName VARCHAR(50)) 
L_END:BEGIN
   DECLARE i INT DEFAULT 0;
   WHILE i <= 2 DO
   SELECT partition_description INTO @MIN_PARTITION
   FROM information_schema.PARTITIONS
   WHERE TABLE_SCHEMA = datavaseName AND TABLE_NAME = tableName
   ORDER BY partition_description ASC LIMIT 1;
  
	SET @S=CONCAT('ALTER TABLE ',tableName,' DROP PARTITION p',@MIN_PARTITION,')';
	PREPARE stmt FROM @s;
        EXECUTE stmt;
        SELECT @s;
	SET i=i+1;
   END WHILE;
END L_END;|| 
DELIMITER ;

CALL drop_Partition('adverttj','records');

SELECT partition_description INTO @MIN_PARTITION
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'adverttj' AND TABLE_NAME = 'records'
ORDER BY partition_description ASC LIMIT 1;

ALTER TABLE `records` DROP PARTITION p20161128

#查看分区分区
SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='records'