CREATE TABLE SQL_P_TEST (
    id bigint NOT NULL AUTO_INCREMENT,
    name varchar(100) NOT NULL,
    mmpk datetime NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

SELECT
    partition_name part,
    partition_expression expr,
    partition_description descr,
    FROM_DAYS(partition_description) lessthan_sendtime,
    table_rows
FROM INFORMATION_SCHEMA.partitions
WHERE TABLE_SCHEMA = SCHEMA()
AND TABLE_NAME = 'SQL_P_TEST'; 

ALTER TABLE DROP partition [partition_name];

ALTER TABLE SQL_P_TEST DROP PRIMARY KEY, ADD PRIMARY KEY (id, mmpk);

ALTER TABLE SQL_P_TEST PARTITION BY RANGE (TO_DAYS(mmpk))
(
    PARTITION p20191117 VALUES LESS THAN (TO_DAYS('2019-11-18')) ENGINE = INNODB,
    PARTITION p20191118 VALUES LESS THAN (TO_DAYS('2019-11-19')) ENGINE = INNODB,
    PARTITION p20191129 VALUES LESS THAN (TO_DAYS('2019-11-20')) ENGINE = INNODB
);

CREATE PROCEDURE cluster_test.P_PARTITION_TO_DAY(IN IN_SCHEMA_NAME varchar(64), IN IN_TABLE_NAME varchar(64))
  SQL SECURITY INVOKER
  MODIFIES SQL DATA
BEGIN

    # datetime column
    DECLARE CURRENT_DATA_PAR_CNT int UNSIGNED;

    DECLARE TARGET_DATE timestamp;

    #分区的名称，格式为 p20180620
    DECLARE P_NAME varchar(10);
    DECLARE PARTITION_KEY int(11);

    # 触发时间
    SET TARGET_DATE = NOW() + INTERVAL 1 DAY;

    # 分区名称
    SET P_NAME = DATE_FORMAT(TARGET_DATE, 'p%Y%m%d');

    SET PARTITION_KEY = TO_DAYS(TARGET_DATE);

    # 查询分区名称是否已存在
    SELECT
        COUNT(*) INTO CURRENT_DATA_PAR_CNT
    FROM information_schema.partitions
    WHERE table_schema = IN_SCHEMA_NAME
    AND table_name = IN_TABLE_NAME
    AND partition_name = P_NAME;

    IF CURRENT_DATA_PAR_CNT = 0 THEN
        # 创建表空间
        SET @ALTER_SQL = CONCAT('ALTER TABLE ', IN_SCHEMA_NAME, '.', IN_TABLE_NAME,
        ' ADD PARTITION (PARTITION ', P_NAME,
        " VALUES LESS THAN (", PARTITION_KEY, ") ENGINE = InnoDB);");


        PREPARE STMT FROM @ALTER_SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
    ELSE
        # 表空间已经存在
        SELECT
            CONCAT("partition ", P_NAME, " for table ", IN_SCHEMA_NAME, ".", IN_TABLE_NAME, " already exists") AS RESULT;
    END IF;

END