在一段时间间隔后从shell脚本调用MySQL查询

Question

一段时间后，我想从一个shell脚本中执行一个MySQL查询。我已经编写了脚本，但是输出并不像预期的那样。它将再次从数据库中打印相同的值。有人能帮忙吗。

脚本- demo.sh

#!/bin/bash
echo "Shell Script to Query a Database!"
echo "--Querying from the Database starts--"
cd "C:\Program Files\MySQL\MySQL Server 5.7\bin"
for i in {1..5};
do 
./mysql -u root -proot@123 << EOF
use catalyst_latest;
select id from ci_master limit 1;
EOF
echo "Wait for 2 seconds for the next ID." 
sleep 2; 
done
echo "--Query Stopped!--"

输出

$ ./demo.sh
Shell Script to Query a Database!
--Querying from the Database starts--
mysql: [Warning] Using a password on the command line interface can be   insecure.
id
282
Wait for 2 seconds for the next ID.
mysql: [Warning] Using a password on the command line interface can be insecure.
id
282
Wait for 2 seconds for the next ID.
mysql: [Warning] Using a password on the command line interface can be insecure.
id
282
Wait for 2 seconds for the next ID.
mysql: [Warning] Using a password on the command line interface can be insecure.
id
282
Wait for 2 seconds for the next ID.
mysql: [Warning] Using a password on the command line interface can be insecure.
id
282
Wait for 2 seconds for the next ID.
--Query Stopped!--

你能看到每一次都会有282人退货吗？所以我想在2秒后从数据库中得到下一个ID。请告诉我怎么做。提前谢谢你。

Answer 1

您需要在bash变量中记住返回的id值，然后在查询的WHERE子句中重用该变量，如下所示：

select id from ci_master where id > $id limit 1

要将命令的输出写入变量，请使用以下内容：

id=$(mysql <options> -Nsqe "<query>")

选项N跳过列名(如id)、选项s用于更安静的输出、选项q用于不缓存结果、选项e用于执行命令。

最初，应该将$id变量设置为一些值，如1或表中现有值的最小值。

Answer 2

#!/bin/bash

echo "Shell Script to Query a Database!"
echo "--Querying from the Database starts--"
cd "C:\Program Files\MySQL\MySQL Server 5.7\bin"
for i in {1..10};
do 
temp=`./mysql -u root -proot@123 << EOF
use catalyst_latest;
select id from ci_master limit $i;
EOF`
result=''
for j in $temp 
do 
result=$j
done
#date;
echo "$result"
echo "Wait for 1 seconds for the next ID." 
sleep 1; 
done
echo "--Query Stopped!--"