mysql 当天创建分区表_mysql8.0 定时创建分区表记录 每天定时创建下一天的分区表...

因单表数据太大， 需要表按时间分区 分区字段 pay_out_date 按天分 要求自动创建

1. 创建分区表

MYSQL的分区字段，必须包含在主键字段内

常见错误提示

错误提示：#1503

A PRIMARY KEY MUST INCLUDE ALL COLUMNS IN THE TABLE‘S PARTITIONING FUNCTION

MySQL主键的限制，每一个分区表中的公式中的列，必须在主键/unique key 中包括

CREATE TABLE `game_bet` (

`id` BIGINT(20) NOT NULL AUTO_INCREMENT,

`create_date` DATETIME(6) DEFAULT NULL,

`create_person` VARCHAR(50) DEFAULT NULL,

`update_date` DATETIME(6) DEFAULT NULL,

`update_person` VARCHAR(50) DEFAULT NULL,

`bet_code` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

`agent_code` VARCHAR(50) NOT NULL,

`game_id` INT(11) NOT NULL,

`game_name` VARCHAR(50) DEFAULT NULL,

`room_code` VARCHAR(11) NOT NULL,

`desk_code` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

`user_id` BIGINT(20) NOT NULL,

`user_name` VARCHAR(50) NOT NULL,

`round_code` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

`commission` DECIMAL(18,3) NOT NULL,

`bet_amount` DECIMAL(18,3) NOT NULL,

`valid_bet_amount` DECIMAL(18,3) NOT NULL,

`pay_out` DECIMAL(18,3) NOT NULL,

`win_amount` DECIMAL(18,3) NOT NULL,

`settle_flag` INT(11) NOT NULL,

`device` INT(11) NOT NULL,

`login_ip` VARCHAR(50) NOT NULL,

`currency` VARCHAR(50) NOT NULL,

`bet_date` DATETIME(6) DEFAULT NULL,

`pay_out_date` DATETIME(6) NOT NULL DEFAULT ‘1970-01-01 00:00:00.000000‘,

`result` VARCHAR(50) NOT NULL,

PRIMARY KEY (`id`,`pay_out_date`),

KEY `IX_game_bet_agent_code` (`agent_code`),

KEY `IX_game_bet_create_date` (`create_date`),

KEY `IX_game_bet_device` (`device`),

KEY `IX_game_bet_game_id` (`game_id`),

KEY `IX_game_bet_pay_out_date` (`pay_out_date`),

KEY `IX_game_bet_round` (`round_code`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY RANGE (to_days(`pay_out_date`))

(PARTITION p20190421 VALUES LESS THAN (737535) ENGINE = InnoDB) */

2. 创建每日新增表分区的存储过程

DELIMITER $$

USE `poker_games`$$

DROP PROCEDURE IF EXISTS `time_partition_procedure`$$

CREATE DEFINER=`root`@`%` PROCEDURE `time_partition_procedure`()

BEGIN

SELECT REPLACE(b.partition_name,‘p‘,‘‘) INTO @in_date FROM information_schema.PARTITIONS b WHERE b.table_name =‘game_bet‘ ORDER BY b.partition_ordinal_position DESC LIMIT 1;

SET @max_date= DATE_ADD(@in_date,INTERVAL 1 DAY)+0 ;

SET @date= DATE_ADD(@in_date,INTERVAL 1 DAY)+0 ;

SET @sql=CONCAT(‘ALTER TABLE game_bet add PARTITION (PARTITION p‘,@date,‘ VALUES LESS THAN (TO_DAYS(‘‘‘,@max_date,‘‘‘)));‘);

SELECT @sql;

SELECT @max_date;

PREPARE strsql FROM @sql;    #预执行sql

EXECUTE strsql;           #执行sql

DEALLOCATE PREPARE strsql;   #释放sql

COMMIT;

END$$

DELIMITER ;

3. 创建每天执行存储的事件 明天凌晨3点执行

DELIMITER $$

CREATE EVENT `poker_games`.`time_partition_event`

ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(CURRENT_DATE+1,INTERVAL 3 HOUR)

ON COMPLETION PRESERVE

ENABLE DO

BEGIN

CALL time_partition_procedure();

END;

原文：https://www.cnblogs.com/xtxtx/p/10746290.html