對(duì)于自動(dòng)化運(yùn)維，諸如備份恢復(fù)之類的，DBA經(jīng)常需要將SQL語(yǔ)句封裝到shell腳本。本文描述了在Linux環(huán)境下mysql數(shù)據(jù)庫(kù)中，shell腳本下調(diào)用sql語(yǔ)句的幾種方法，供大家參考。對(duì)于腳本輸出的結(jié)果美化，需要進(jìn)一步完善和調(diào)整。以下為具體的示例及其方法。

1、將SQL語(yǔ)句直接嵌入到shell腳本文件中

代碼如下:
--演示環(huán)境 
[root@SZDB ~]# more /etc/issue 
CentOS release 5.9 (Final) 
Kernel /r on an /m 
 
root@localhost[(none)]> show variables like 'version'; 
+---------------+------------+ 
| Variable_name | Value      | 
+---------------+------------+ 
| version       | 5.6.12-log | 
+---------------+------------+ 
 
[root@SZDB ~]# more shell_call_sql1.sh  
#!/bin/bash 
# Define log 
TIMESTAMP=`date +%Y%m%d%H%M%S` 
LOG=call_sql_${TIMESTAMP}.log 
echo "Start execute sql statement at `date`." >>${LOG} 
 
# execute sql stat 
mysql -uroot -p123456 -e " 
tee /tmp/temp.log 
drop database if exists tempdb; 
create database tempdb; 
use tempdb 
create table if not exists tb_tmp(id smallint,val varchar(20)); 
insert into tb_tmp values (1,'jack'),(2,'robin'),(3,'mark'); 
select * from tb_tmp; 
notee 
quit" 
 
echo -e "/n">>${LOG} 
echo "below is output result.">>${LOG} 
cat /tmp/temp.log>>${LOG} 
echo "script executed successful.">>${LOG} 
exit; 
 
[root@SZDB ~]# ./shell_call_sql1.sh  
Logging to file '/tmp/temp.log' 
+------+-------+ 
| id   | val   | 
+------+-------+ 
|    1 | jack  | 
|    2 | robin | 
|    3 | mark  | 
+------+-------+ 
Outfile disabled. 

2、命令行調(diào)用單獨(dú)的SQL文件

代碼如下:
[root@SZDB ~]# more temp.sql  
tee /tmp/temp.log 
drop database if exists tempdb; 
create database tempdb; 
use tempdb 
create table if not exists tb_tmp(id smallint,val varchar(20)); 
insert into tb_tmp values (1,'jack'),(2,'robin'),(3,'mark'); 
select * from tb_tmp; 
notee 
 
[root@SZDB ~]# mysql -uroot -p123456 -e "source /root/temp.sql" 
Logging to file '/tmp/temp.log' 
+------+-------+ 
| id   | val   | 
+------+-------+ 
|    1 | jack  |