shell脚本, 按天创建MySQL的表,生成后七天的表,并且删除第前n天的表。
#!/bin/bash
#execute all script in specified directory
set -e
s=`date +"%Y%m%d"`
echo $s
for i in {0..7}
do
times=`date -d "+${i} day" +"%Y%m%d"`;
result=`mysql -h127.0.0.1 -N -P3306 -uroot -pXXX <<EOF
use a;
create table if not exists c_$times(
ID varchar(50) NOT NULL COMMENT '主键ID',
version bigint(20) NOT NULL DEFAULT 0,
creation datetime NOT NULL DEFAULT current_timestamp(),
last_modified datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
quit
EOF`
echo $result
if [ $? -eq 0 ]
then echo "end create success_"$times
else echo "end create error_"$times
fi
done
startTimes=`date -d "14 days ago" +%Y%m%d`;
echo "start drop table c_"$startTimes
deleteResult=`mysql -h127.0.0.1 -N -P3306 -uroot -pXXXX <<EOF
use a;
drop table if exists c_$startTimes;
quit
EOF`
echo $deleteResult
if [ $? -eq 0 ]
then echo "end drop success_"$startTimes
else echo "end drop error_"$startTimes
fi
共通性有点差,不好维护,修改如下,假设有个基础表,每次都是创建跟基础表一样的结构的表,执行脚本的步骤是
sh a.sh abcd
a.sh是脚本的名字
abcd是表名
要创建的表名是abcd_20200616
这样就可以只维护一套脚本,改变参数就可以了。
#!/bin/bash
#execute all script in specified directory
set -e
table_name_prefix=$1
database='abc'
s=`date +"%Y%m%d"`
echo "start operate $table_name_prefix"$s
startTimes=`date -d "7 days ago" +%Y%m%d`
for i in {1..7}
do
times=`date -d "+${i} day" +"%Y%m%d"`;
table_name="$table_name_prefix"_"$times"
table_name_drop="$table_name_prefix"_"$startTimes"
# echo "create table if not exists $table_name as select a.* from $table_name_prefix a where 0=1;"
result=`mysql -h172.0.0.1 -N -P3306 -uxxxx -pxxxx <<EOF
use $database;
create table if not exists $table_name as select a.* from $table_name_prefix a where 0=1;
quit
EOF
`
done
echo "start drop table "$table_name_prefix"_"$startTimes
deleteResult=`mysql -h172.0.0.1 -N -P3306 -uxxxx -pxxxxx <<EOF
use $database;
drop table if exists $table_name_drop;
quit
EOF
`
echo "end operate $table_name_prefix"