在shell脚本中登录Mysql并执行SQL语句

有一个需求是，自动删除MYSQL中的过期数据，只保留最近一周的数据。

在shell脚本中登录Mysql并执行SQL语句，示例代码如下：

#!/bin/bash

# get 7 days ago according to input date. e.g. if input date is 20180410,it will delete those records on or before 20180403
wanted_date=`date -d "$1 7 days ago" +%Y%m%d`

echo "0==}=========> CAUTION! Those records on or before $wanted_date will be removed!"
echo "0==}=========> Are you sure to continue? yes/no"
read option
if [ "$option" == "yes" ]; then
  echo You made a good choice.
  echo ----------
elif [ "$option" == "no" ];then
  echo Goodbye~
  exit 0
else
  echo PLASE INPUT yes OR no THEN TRY AGAIN!
  exit 0
fi

# to call SQL statement at MySQL prompt
mysql -h 172.33.101.123 -P 3306 -u tony -pYourPassword -D YourDbName <<EOF
select current_date();
use tony_db;
desc confirmed_order_data;
select count(*) from confirmed_order_data where paid_date<="$wanted_date";
delete from confirmed_order_data WHERE paid_date<="$wanted_date";
select count(*) from confirmed_order_data where paid_date<="$wanted_date";

EOF

脚本已测过，没问题，可以拿去用了。如果你觉得好用，回来给我点个赞哈 ^_