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

 原表和对应的分区

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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 );

 

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

#!/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

 

通过脚本删除指定的分区

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

#!/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

　　

新增/删除分区后确认

?

1 2 3 4 5 6 7

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';