Shell脚本中执行sql语句操作MySQL数据库的几个办法

2024-07-24 12:32:25

字体：大中小

来源：转载

供稿：网友

实验如下:
[root@idb1 ~]# more /etc/issue
CentOS release 6.5 (Final)
Kernel /r on an /m

mysql> show variables like 'version';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version       | 5.6.27-log |
+---------------+------------+
1 row in set (0.00 sec)

1、将SQL语句直接嵌入到shell脚本文件中
[root@idb1 ~]# cat shell_example01.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}

[root@idb1 ~]# chmod +x shell_example01.sh

[root@idb1 ~]# sh shell_example01.sh
Warning: Using a password on the command line interface can be insecure.
Logging to file '/tmp/temp.log'
+------+-------+
| id   | val   |
+------+-------+
|    1 | jack |
|    2 | robin |
|    3 | mark |
+------+-------+
Outfile disabled.

2、 shell脚本中MySQL提示符下调用SQL命令例子:
[root@idb1 ~]# cat shell_example02.sh
#!/bin/bash
mysql -umdba -pdsf0723 <<EOF
source /root/temp.sql;
select current_date();
delete from tempdb.tb_tmp where id=3;
select * from tempdb.tb_tmp where id=2;
EOF
exit

[root@idb1 ~]# sh shell_example02.sh
Warning: Using a password on the command line interface can be insecure.
Logging to file '/tmp/temp.log'
id      val
1       duansf
2       liuyb
3       jack
Outfile disabled.
current_date()
2017-03-14
id      val
2       liuyb

（编辑：武林网）

上一篇：MySQL集群创建实现高可用

下一篇：MySQL 5.5 创建存储步骤和函数