mysql分区表批量添加/删除range按天分区(int类型）

 原表和对应的分区

CREATE TABLE `test_part1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `col1` int(11) NOT NULL DEFAULT '0' COMMENT 'col1',
  `col2` int(11) NOT NULL DEFAULT '0' COMMENT 'col2',
  `col3` int(11) NOT NULL DEFAULT '0' COMMENT 'col3',
  `a_time` int(11) NOT NULL DEFAULT '0' COMMENT 'a_time',
  PRIMARY KEY (`id`,`a_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='测试'
PARTITION BY RANGE (a_time)
(
PARTITION p20210101 VALUES LESS THAN (unix_timestamp('2021-01-02')) engine=innodb,
PARTITION p20210102 VALUES LESS THAN (unix_timestamp('2021-01-23')) engine=innodb,
...
PARTITION p20211230 VALUES LESS THAN (unix_timestamp('2021-12-31')) engine=innodb,
PARTITION p20211231 VALUES LESS THAN (unix_timestamp('2022-01-01')) engine=innodb
);

 

通过脚本添加指定分区的命令

#!/usr/bin/env bash
 
start_date="20220101"
end_date="20230101"
 
 
while [ "$start_date" -le "$end_date" ];
do
     
    stat_date_num=$(date -d "$start_date" +%Y%m%d)
    stat_date=$(date -d "$stat_date_num"+1days +%Y-%m-%d)
    echo "p$stat_date_num|alter TABLE test_part1 add PARTITION \
	(PARTITION p$stat_date_num VALUES LESS THAN (unix_timestamp('$stat_date')));" >> add_partition_list
    start_date=$(date -d "$start_date+1days" +%Y%m%d)
done
 
 
cat add_partition_list |while read line
do
    partition_name=$(echo $line |awk -F'|' '{print $1}')
    add_partition_sql=$(echo $line |awk -F'|' '{print $2}')
    mysql -S /home/data/my3367/socket/mysqld.sock -uroot -proot db2 -e "$add_partition_sql"
    if [[ $? -ne 0 ]];then
        echo "add $partition_name faild" | tee -a add_partition_faild.log
        exit
    else
        echo "add $partition_name success" | tee -a add_partition_success.log
    fi
done

 

通过脚本删除指定的分区

#!/usr/bin/env bash
#drop  partition scripts

[[ -f drop_partition_list ]] && rm -f drop_partition_list

start_date="20220101"
end_date="20230101"


while [ "$start_date" -le "$end_date" ];
do
	stat_date_num=`date -d "$start_date" +%Y%m%d`
	echo "p$stat_date_num|alter TABLE test_part1 \
	drop PARTITION p$stat_date_num;" >> drop_partition_list
	start_date=$(date -d "$start_date+1days" +%Y%m%d)
done

while read line
do
	partition_name=$(echo $line |awk -F'|' '{print $1}')
	drop_partition_sql=$(echo $line |awk -F'|' '{print $2}')
	mysql -S /home/data/my3367/socket/mysqld.sock -uroot -proot db2 -e "$drop_partition_sql"
	if [[ $? -ne 0 ]];then
		echo "drop $partition_name faild" | tee -a drop_partition_faild.log
		exit
	else
		echo "drop $partition_name success" | tee -a drop_partition_success.log
	fi
done < drop_partition_list

　　

新增/删除分区后确认

select 
partition_name,
partition_expression,
partition_method,
from_unixtime(partition_description,'%Y-%m-%d')
from information_schema.partitions 
where table_name='test_part1' and table_schema='db2';