shell脚本mysql_Shell脚本中执行sql语句操作MySQL数据库的几个方法

实验如下:

[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}

# execute sql stat

mysql -umdba -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@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、命令行调用单独的SQL文件例子:

[root@idb1 ~]# cat 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,'duansf'),(2,'liuyb'),(3,'jack');

select * from tb_tmp;

notee

quit

[root@idb1 ~]# mysql -umdba -p -e "source /root/temp.sql"

Enter password:

Logging to file '/tmp/temp.log'

+------+--------+

| id   | val    |

+------+--------+

|    1 | duansf |

|    2 | liuyb  |

|    3 | jack   |

+------+--------+

Outfile disabled.

#使用管道符调用SQL文件以及输出日志

[root@idb1 ~]# mysql -umdba -p

Enter password:

Logging to file '/tmp/temp.log'

id      val

1       duansf

2       liuyb

3       jack

Outfile disabled.

4、shell脚本中MySQL提示符下调用SQL命令例子:

[root@idb1 ~]# cat shell_example02.sh

#!/bin/bash

mysql -umdba -pdsf0723 <

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-2135352/，如需转载，请注明出处，否则将追究法律责任。