☰

用于附加单引号和逗号的 Shell 脚本

tag-icon • tag-icon shell-script variable text-formatting sql
用于附加单引号和逗号的 Shell 脚本

我有一个名为input.txt：

$cat input.txt
This is sample
Input file
To execute sql statement

我需要将如下输出分配给变量：

X=('This is sample', 'Input file', 'To execute sql statement')

这样我就可以使用上面的字符串 X 作为条件中 SQL 查询的输入IN。

select * from table where columnname in X

请在这里帮助我。

答案1

和bash：

mapfile -t a < input.txt      # read input file into array a
printf -v x "'%s'," "${a[@]}" # add single quotes and commas, save result in variable x
printf -v query 'select * from table where columnname in (%s);' "${x:0:-1}" # strip the last comma from x
printf '%s\n' "$query"        # print query

输出：

select * from table where columnname in ('This is sample','Input file','To execute sql statement');

答案2

将文件逐行拆分为数组：

# Tell bash that "lines" is an array
declare -a lines

# Read each line of the file
while read line; do
    lines+=("$line")
done <input.txt

打印结果：

# Show the first line
echo "${lines[0]}"

# Show all lines
echo "${lines[@]}"

请记住，大多数数据库 CLI 工具都允许您运行文件作为输入。 postgres 的示例： psql -f input.txt

答案3

一班轮：

IFS=$'\n'; (read -r L && echo -n "X=('${L//\'/\"}'"; while read -r L; do echo -n ",'${L//\'/\"}'"; done; echo ")";) < input.txt; IFS=' '

（里面的所有单引号符号都'替换为双引号符号"）。

输出：

X=('This is sample','Input file','To execute sql statement')

或者分配给一个变量：

$ IFS=$'\n'; X=$( (read -r L && echo -n "('${L//\'/\"}'"; while read -r L; do echo -n ",'${L//\'/\"}'"; done; echo ")";) < input.txt); IFS=' '; 
$ echo $X
('This is sample','Input file','To execute sql statement')

更新。第一个班轮解释：

#
# redefining IFS to read line by line ignoring spaces and tabs
# you can read more about at
# https://unix.stackexchange.com/questions/184863/what-is-the-meaning-of-ifs-n-in-bash-scripting
#
IFS=$'\n';
#
# next actions united inside of round brackets
# to read data from input.txt file
#
# first line from the file read separately,
# because should be printed with opening brackets
#
# between read and echo we use && to make sure
# that data are printed only if reading succeeded
# (that means that the file is not empty and we have reading permissions)
#
# also we print not just "X=($L
# but variable modified in the way to replace ' with "
# more details about it you can find at
# https://devhints.io/bash in Substitution section
# also quotes symbols inside of quoted text are backslash'ed
#
(read -r L && echo -n "X=('${L//\'/\"}'";
#
# now we read lines one by one as long as reading returns data
# (which is until we reach end of file)
# and each time printing: ,'LINE-DATA'
# I mean coma and data in single quotes like requested
# where the data are the lines where single quotes replaced
# with double quotes, the same as we did for the first line
while read -r L; do
    echo -n ",'${L//\'/\"}'";
done;
#
# finally we print closing brackets for the data from file
#
# also we close round brackets uniting actions to read data from file
# and specifying file name from where we read data
echo ")";) < input.txt; 
#
# at the end we change IFS back to original.
# actually for 100% accuracy it should be IFS=$' \t\n'
IFS=' '

答案4

仅使用便携式 shell 功能：

oIFS=$IFS                         # Store the IFS for later;
IFS=                              # Empty the IFS, to ensure blanks at the
                                  # beginning/end of lines are preserved
while read -r line; do            # Read the file line by line in a loop,
    set -- "$@" "'""$line""'"     # adding each line to the array of positional
done < input                      # parameters, flanked by single quotes
IFS=,                             # Set "," as the first character of IFS, which
                                  # is used to separate elements of the array
                                  # of positional parameters when expanded as
                                  # "$*" (within double quotes)
X=\("$*"\)                        # Add parentheses while assigning to "X"
IFS=$oIFS                         # Restore the IFS - to avoid polluting later
                                  # commands' environment

请注意，使用用于处理文本的 shell 循环存在问题，但这在这里可能有意义，因为我们希望文件内容最终位于 shell 变量中。

输出：

$ printf "%s %s\n" 'select * from table where columnname in' "$X"
select * from table where columnname in ('This is sample','Input file','To execute sql statement')

或者，您可以使用诸如sed.此脚本向每个输入行添加侧翼单引号，将所有行附加到保留空间，并且在附加最后一行后，将保留空间的内容放入模式空间，用<newline>逗号替换任何内容，在以下位置添加括号文本的开头/结尾并打印整个内容：

X=$(sed -n '
  s/^/'"'"'/
  s/$/'"'"'/
  H
  1h
  ${
    g
    s/\n/,/g
    s/^/(/
    s/$/)/
    p
  }
  ' input)

当然，这些方法不会处理输入行中可能出现的单引号，这最终会导致 SQL 语句损坏，或者至少不会产生预期结果。

相关内容