由于表分区用完了，程序开始报错

解决方法：增加表分区

ALTER TABLE customeventlabelsum ADD PARTITION ( 

......

PARTITION kvs_s20241209 VALUES LESS THAN (20241209), 
PARTITION kvs_s20241216 VALUES LESS THAN (20241216),
PARTITION kvs_s20241223 VALUES LESS THAN (20241223), 
PARTITION kvs_s20241230 VALUES LESS THAN (20241230)
);

mysql分区表在定义分区时，有时会定义一个maxvalue的分区，相当于一个默认分区，保证所有数据都能被分区表存储。

对于这种情况需要使用一下sql来添加分区

把MAXVALUE分区拆分为多个分区

alter table enterprise.userprofile_bak1 REORGANIZE PARTITION pmax INTO(
PARTITION p101 VALUES LESS THAN (1646064000) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB
);

执行后：

其他分区相关sql

查看每个分区的记录行数和占用大小

SELECT PARTITION_NAME,TABLE_ROWS,DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'userprofile_bak1';

清理分区数据为空，保留分区不删除，仅仅是清理数据，命令如下

alter table bm_scenes_data_reminder truncate partition p20210104; 

删除分区

alter table bm_scenes_data_reminder drop partition p20210104; 

查看所有表的可用分区数量

SELECT
	table_name,
	count(*) 
FROM
	information_schema.`PARTITIONS` 
WHERE
	table_name NOT IN ( 'information_schema', 'performance_schema', 'sys' ) 
	AND partition_name IS NOT NULL 
	AND partition_method IS NOT NULL 
	AND table_rows = 0 
GROUP BY
	table_name;

案例:

系统有操作系统表sys_log，实现每天删除90天前的分区并同时建一个4天后的分区（即每天将4天后的分区创建表），步骤如下：

1. ##新建普通表，只执行一次

CREATE TABLE `sys_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `begin_time` datetime DEFAULT NULL COMMENT '开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '结束时间',
  `spend_mills` int(11) DEFAULT NULL COMMENT '运行时长,单位ms',
  `username` varchar(100) DEFAULT NULL COMMENT '用户id',
  `log_status` int(11) NOT NULL DEFAULT '0' COMMENT '运行状态,[0]成功[1]失败',
  `code` int(11) NOT NULL DEFAULT '0' COMMENT '错误码',
  `remote_addr` varchar(50) DEFAULT '' COMMENT '远程地址',
  `request_uri` varchar(255) DEFAULT NULL COMMENT '请求路径',
  `user_agent` text COMMENT '用户代理',
  `req_data` text NOT NULL COMMENT '请求参数',
  `resp_data` longtext NOT NULL COMMENT '返回结果',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_log_begintime` (`begin_time`) USING BTREE COMMENT '系统日志的beginTime字段索引'
) ENGINE=Innodb DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

2. ##修改成分区表，只执行一次，留一个当前时间的分区和将来的future分区

ALTER TABLE `sys_log` DROP PRIMARY KEY, ADD PRIMARY KEY(`id`,`begin_time`);
alter table sys_log partition by RANGE (to_days(begin_time)) (
    PARTITION p20210816 VALUES LESS THAN (738383),
    PARTITION future VALUES LESS THAN MAXVALUE
);

3. ##每天执行以下的分区操作，进行增加一天的分区,如

ALTER TABLE sys_log drop PARTITION future;
ALTER TABLE sys_log ADD PARTITION (PARTITION p20210817 VALUES LESS THAN (738384) ENGINE = InnoDB);
ALTER TABLE sys_log ADD PARTITION (PARTITION p20210818 VALUES LESS THAN (738385) ENGINE = InnoDB);
ALTER TABLE sys_log ADD PARTITION (PARTITION p20210819 VALUES LESS THAN (738386) ENGINE = InnoDB);
ALTER TABLE sys_log ADD PARTITION (PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

4. ##定时任务配置，每天执行一次分区的清理与创建

30 4 * * * /bin/python /home/testuser/SyslogPartitionClear.py >/dev/null 2>&1

脚本：

#!/usr/bin/python
# -*- coding: utf8 -*-

import mysql.connector
import datetime

class SyslogPartitionClear(object):
    'SyslogPartitionClear clearData'
    def clearData(self,deleteTime,afterTime):

        db = None    
        try:
            db = mysql.connector.connect(host='127.0.0.1',port=3306,user='webuser', password='123456', database='icnoclx_webdb',cha
rset="utf8", use_unicode=True)
            
            cursor = db.cursor()
            cursor.execute("select to_days(DATE_ADD(NOW(),INTERVAL 4 DAY))")
            results = cursor.fetchall()
            
            ##
            partitionIntegerValue = 0;
            for row in results: 
                partitionIntegerValue = row[0]
            
            try:
                print "ALTER TABLE sys_log drop PARTITION p"+deleteTime+";"
                cursor.execute("ALTER TABLE sys_log drop PARTITION p"+deleteTime+";")          
            except Exception, e:
                print e
            
            try:
                print "ALTER TABLE sys_log drop PARTITION future;"
                cursor.execute("ALTER TABLE sys_log drop PARTITION future;")          
            except Exception, e:
                print e           
            
            try:
                print "ALTER TABLE sys_log ADD PARTITION (PARTITION p"+afterTime+" VALUES LESS THAN ("+str(partitionIntegerValue)+"
) ENGINE = InnoDB);"
                cursor.execute("ALTER TABLE sys_log ADD PARTITION (PARTITION p"+afterTime+" VALUES LESS THAN ("+str(partitionIntege
rValue)+") ENGINE = InnoDB);")      
            except Exception, e:
                print e             
            
            try:
                print "ALTER TABLE sys_log ADD PARTITION (PARTITION future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);"
                cursor.execute("ALTER TABLE sys_log ADD PARTITION (PARTITION future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);")
     
            except Exception, e:
                print e               
            
            db.commit()
            
        except Exception, e: 
            db.rollback()
            print e
        finally:
            db.close()
        
if __name__ == '__main__':

    now=datetime.datetime.now()
    delta=datetime.timedelta(days=-90)
    before_day = now + delta
    deleteTime = before_day.strftime('%Y%m%d')
    
    after_day = now + datetime.timedelta(days=4)
    afterTime = after_day.strftime('%Y%m%d')
    
    obj = SyslogPartitionClear()
    obj.clearData(deleteTime,afterTime)