原表和对应的分区



CREATE TABLE `test_part2` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`col1` varchar(10) NOT NULL DEFAULT '' COMMENT 'col1',
`col2` varchar(10) NOT NULL DEFAULT '' COMMENT 'col2',
`col3` varchar(10) NOT NULL DEFAULT '' COMMENT 'col3',
`a_date` datetime  NOT NULL DEFAULT '20210816' COMMENT '日期',
PRIMARY KEY (`id`,`a_date`)
 ) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='test'
partition by range(to_days(a_date))
 (
PARTITION p20211229 VALUES LESS THAN (TO_DAYS('20211229')),
PARTITION p20211230 VALUES LESS THAN (TO_DAYS('20211230')),
PARTITION p20211231 VALUES LESS THAN (TO_DAYS('20211231')),
PARTITION p20220101 VALUES LESS THAN (TO_DAYS('20220101'))
 );


 

通过脚本添加指定分区



#!/usr/bin/env bash


[[ -f add_partition_list ]] && rm -f add_partition_list

start_date="20220102"
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_part2 add PARTITION \
  (PARTITION p$stat_date_num VALUES LESS THAN (TO_DAYS('$stat_date_num')));" >> add_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}')
  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 < add_partition_list


 

通过脚本删除指定分区



#!/usr/bin/env bash


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

start_date="20220102"
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_part2 drop PARTITION p$stat_date_num;" >> drop_partition_list
  start_date=$(date -d "$start_date+1days" +%Y%m%d)
done

cat drop_partition_list |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