Shell脚本执行SQL

通过Shell脚本来执行一些sql命令或sql文件，对我们的日常快速运维是很方便的；下面记录一下简单的用法：
1.删除相关数据
简单shell脚本如下

#!/bin/bash

read -p "Enter your username, please: " username

read -p "Enter your password, please: " pswd

User=$username
Password=$pswd
Host=localhost

function exec_sql {
    echo 'Start to execute SQL Script ...'
  
	#execute SQL Script
	qry_result_num="$1"

	if [ "$?" -ne "0" ]
	then
		echo "Occur error when execute SQL script ..."
		exit
	fi

	echo 'Done Successfully.'
}


echo "*********************************************************************"
echo "Job1数据清洗 ------------------------------------------------------1"
echo "Job2数据清洗 ------------------------------------------------------2"
echo "Job3数据清洗 ------------------------------------------------------3"
echo "Job4数据清洗 ------------------------------------------------------4"
echo "Job5数据清洗 ------------------------------------------------------5"
echo "*********************************************************************"
	
read -p "Select the SQL script you want to execute: [1/2/3/4/5] " input

case $input in
        1)
                exec_sql `mysql -h $Host -u$User -p$Password -s -e "source job1.sql"`
                ;;
        2)
                exec_sql `mysql -h $Host -u$User -p$Password -s -e "source job2.sql"`
                ;;
		3)
                exec_sql `mysql -h $Host -u$User -p$Password -s -e "source job3.sql"`
                ;;
		4)
                exec_sql `mysql -h $Host -u$User -p$Password -s -e "source job4.sql"`
                ;;
		5)
                exec_sql `mysql -h $Host -u$User -p$Password -s -e "source job5.sql"`
                ;;
        *)
                echo "Just enter 1 or 2 or 3 or 4 or 5, please."
                exit
                ;;
esac

脚本说明：
User=数据库用户名
Password=用户密码
Host=数据库所在主机名（localhost）
sql脚本文件（job1.sql/job2.sql/job3.sql/job4.sql/job5.sql）放在了和Shell相同的目录（目录以自己喜好放置即可），sql脚本文件内容是写好的正确的sql脚本（删除操作是危险操作，脚本请务必本地测试通过），如job1.sql的内容：

– 指定操作的数据库名
use foo_db;
– 一系列的delete动作
DELETE FROM table1 WHERE …;
DELETE FROM table2 WHERE …;
…
–当且仅当所有语句正确执行后提交事务
COMMIT;

测试如下：

[root@JD sql_shell]# ./demo.sh
Enter your username, please: root
Enter your password, please: 123456
echo “*********************************************************************”
echo “Job1数据清洗 ------------------------------------------------------1”
echo “Job2数据清洗 ------------------------------------------------------2”
echo “Job3数据清洗 ------------------------------------------------------3”
echo “Job4数据清洗 ------------------------------------------------------4”
echo “Job5数据清洗 ------------------------------------------------------5”
echo “*********************************************************************”
Select the SQL script you want to execute: [1/2/3/4/5] 1
mysql: [Warning] Using a password on the command line interface can be insecure.
Start to execute SQL Script …
Done Successfully.
[root@JD sql_shell]#

2.查询数据
当然也可以不通过sql文件，直接在Shell脚本中写简单的sql语句，如

#!/bin/bash

Host=localhost
User=root
Password=123456

echo 'Execute SQL Script ...'

result_desc=""

qry_result_num=`mysql -h$Host -u$User -p$Password << EOF
use foo_db;
select count(*) as result  from table where 1=1;
EOF`

if $qry_result_num == 0
then
	result_desc="No data in result"
else
	result_desc="Finds "$qry_result_num" records"
fi

echo $result_desc

echo 'Done Successfully.'

也可以

#execute SQL Script
qry_result_num=`mysql -h$Host -u$User -p$Password -s -e "use foo_db; select count(1) from table where 1=1;"`

也可以将查询结果放到指定目录下的文件中，方便统计

#!/bin/bash

Host=localhost
User=root
Password=123456

echo 'Execute SQL Script ...'

`mysql -h $Host -u$User -p$Password -s -e "use foo_db; select * from table where 1=1 into outfile '/home/mysql/data/mysqldata2/tmpdir/demoResult.txt';"`

echo 'Done Successfully.'

将查询结果写入到指定文件，MySQL可能会报没有权限等安全问题，具体解决方法可参考https://blog.csdn.net/k_young1997/article/details/84104475
注：需确保运行的机器已安装MySQL