修改bash脚本，包括MySQL查询，以确定查询是否返回内容或没有结果

Question

希望有人能帮忙。

我有一个现有的bash脚本，它每周发送一封电子邮件，列出添加到我的CMS中的任何新文档:

#!/bin/bash
DATE=$(date +"%d-%b-%Y")
RECIPIENTS=***@***.com
MAILBODY=$(</root/bin/mailbody.txt)
BCC="***@***.com"
SENDER="***@***.com"
SUBJECT="New Documents Released Week Ending "
GREETING="Good morning,"
MAILBODY=$MAILBODY
#MAILBODY="The following content has been added or updated in the Content Management System in the last 7 days (if no files are listed, it means there have been no updates):"
CLOSEBODY="This is an automated email sent by the **** Content Management System (https://***.****.***:****). If you have any problems accessing the content listed in this email, please email ***@****.com"
echo "FROM:" $SENDER > /root/bin/mailtext
echo "TO:" $RECIPIENTS >> /root/bin/mailtext
echo "BCC:" $BCC >> /root/bin/mailtext
echo "SUBJECT:" $SUBJECT $DATE >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo $GREETING >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo $MAILBODY >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo "SELECT name AS NAME,MIN(id) as ID,MIN(created_at) as CREATED,MIN(CONCAT('https://***.***.com:****/link/',id)) as URL from pages WHERE deleted_at IS NULL AND name NOT LIKE 'AO%' AND name NOT LIKE 'KB%' AND name NOT LIKE 'Odoo%' AND name NOT LIKE 'WK%' AND draft=0 AND created_at > DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY name G" | mysql -u root -D  bookstack >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo $CLOSEBODY >> /root/bin/mailtext
cat /root/bin/mailtext | /usr/sbin/sendmail -t

但是，我想对这个脚本进行更多的个性化，这样如果查询没有返回结果，它将更改电子邮件的正文文本(包含在$MAILBODY中)为类似的内容:

最近7天CMS没有新增文档

我已经尝试了很多不同的事情来尝试和实现我想要的，但都失败了(我对bash知之甚少，对SQL更少!)我试图创建一个IF, THEN, ELSE结构，但我似乎找不到一种方法来检测查询何时返回没有结果。我的最后一次尝试是这样的，但显然它不起作用:

TEST=$(echo "SELECT IFNULL(SELECT name AS NAME,MIN(id) as ID,MIN(created_at) as CREATED,MIN(CONCAT('https://***.****.***:****/link/',id)) as URL from pages WHERE deleted_at IS NULL AND name NOT LIKE 'AO%' AND name NOT LIKE 'KB%' AND name NOT LIKE 'Odoo%' AND name NOT LIKE 'WK%' AND draft=0 AND created_at > DATE_SUB(NOW(), INTERVAL 1 DAY) GROUP BY name G")" | mysql -u root -D  bookstack) 
IF (echo "IS NULL $TEST") THEN
$MAILBODY = "There have been no new documents added to the CMS in the last 7 days."
fi

谁能帮助我修改我的脚本，以实现我需要什么?

感谢所有的帮助!

标记

Answer 1

将SQL查询的输出获取到一个变量v.gr中。SQLQUERY。测试该变量包含的字符串，并根据结果

执行命令。所以,尝试:

#!/bin/bash
DATE=$(date +"%d-%b-%Y")
RECIPIENTS=***@***.com
MAILBODY=$(</root/bin/mailbody.txt)
BCC="***@***.com"
SENDER="***@***.com"
SUBJECT="New Documents Released Week Ending "
GREETING="Good morning,"
MAILBODY=$MAILBODY
#MAILBODY="The following content has been added or updated in the Content Management System in the last 7 days (if no files are listed, it means there have been no updates):"
CLOSEBODY="This is an automated email sent by the **** Content Management System (https://***.****.***:****). If you have any problems accessing the content listed in this email, please email ***@****.com"
SQLQUERY=$(echo "SELECT name AS NAME,MIN(id) as ID,MIN(created_at) as CREATED,MIN(CONCAT('https://***.***.com:****/link/',id)) as URL from pages WHERE deleted_at IS NULL AND name NOT LIKE 'AO%' AND name NOT LIKE 'KB%' AND name NOT LIKE 'Odoo%' AND name NOT LIKE 'WK%' AND draft=0 AND created_at > DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY name G" | mysql -u root -D  bookstack)
echo "FROM:" $SENDER > /root/bin/mailtext
echo "TO:" $RECIPIENTS >> /root/bin/mailtext
echo "BCC:" $BCC >> /root/bin/mailtext
echo "SUBJECT:" $SUBJECT $DATE >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo $GREETING >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo $MAILBODY >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
if [ -z "$SQLQUERY" ] ; then # SQLQUERY contains an empty string.
echo "There have been no new documents added to the CMS in the last 7 days." >> /root/bin/mailtext
else
echo "$SQLQUERY" >> /root/bin/mailtext
fi 
echo "" >> /root/bin/mailtext
echo "" >> /root/bin/mailtext
echo $CLOSEBODY >> /root/bin/mailtext
cat /root/bin/mailtext | /usr/sbin/sendmail -t

因为你没有像往常一样提供最小的工作示例，所以我无法调试我提供给你的代码。有可能你将不得不适应"如果"。根据您的配置测试上述功能。