sql - Bash 清除脚本

Question

我正在尝试创建一个脚本来删除不在数据库中的图像

这是我的代码(更新):我有 1 个问题:

• like 语法问题 like '%$f%'

#!/bin/bash

db="intranet_carc_development"
user="benjamin"

for f in public/uploads/files/*
do
  if [[ -f "$f" ]]
  then
    psql $db $user -t -v "ON_ERROR_STOP=1" \
    -c 'select * from public.articles where content like "%'"$(basename "$f")"'%"' | grep . \
    && echo "exist" \
    || echo "doesn't exist"
  fi
done

我有以下错误:

ERROR:  column "%1YOLV3M4-VFb2Hydb0VFMw.png%" does not exist
LINE 1: select * from public.articles where content like "%1YOLV3M4-...
                                                         ^
doesn't exist
ERROR:  column "%wnj8EEd8wuJp4TdUwqrJtA.png%" does not exist
LINE 1: select * from public.articles where content like "%wnj8EEd8w...

编辑:如果我使用 \'%$f%\' 作为 like :

/purge_files.sh: line 12: unexpected EOF while looking for matching `"'
./purge_files.sh: line 16: syntax error: unexpected end of file

Answer 1

您的代码有几个问题:

• $f 是 public/uploads/files/FILENAME，我只想要 FILENAME

您可以使用 basename 来规避它，方法是:

f="$(basename "$f")"
psql $db $user -c "select * from public.articles where content like '%$f%'"...

(如果您的文件名中有空格和特殊字符，额外的引号会在这里防止出现问题)

• 您的 psql 请求将始终返回 true even if no rows are found

• 即使请求失败，你的 psql 命令也会返回真，除非你 set the variable 'ON_ERROR_STOP' to 1

如链接问题所示，您可以使用以下语法:

#!/bin/bash

set -o pipefail #needed because of the pipe to grep later on

db="intranet_carc_development"
user="benjamin"

for f in public/uploads/files/*
do
  if [[ -f "$f" ]]
  then
    f="$(basename "$f")"
    psql $db $user -t -v "ON_ERROR_STOP=1" \
    -c "select * from public.articles where content like '%$f%'" | grep . \
    && echo "exist" \
    || echo "doesn't exist"
  fi
done