如何在 Bash 中保持 MySQL 连接打开
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3463106/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Keep a MySQL Connection Open in Bash
提问by User1
I have a bash script that calls MySQL several times. Instead of having to reconnect to MySQL, is there a way to keep the connection open? Ideally, the connection would close if the script exits early. I'm thinking named pipes would work but they would stay open.
我有一个多次调用 MySQL 的 bash 脚本。有没有办法保持连接打开，而不是重新连接到 MySQL？理想情况下，如果脚本提前退出，连接将关闭。我认为命名管道会起作用，但它们会保持打开状态。
Here's a quick pseudo-example of what I hope to find:
这是我希望找到的快速伪示例：
openMySQL
executeMySQL "SELECT 1"
exit 1
executeMySQL "SELECT 2"
I'm looking for the openMySQLand executeMySQLfunctions where the MySQL connection will actually close during the exit 1.
我在寻找的openMySQL和executeMySQL功能在MySQL连接将在实际关闭exit 1。
回答by User1
I have part of what I was looking for.
我有我一直在寻找的一部分。
Keep the mysql connection open using fd=3 for writing:
使用 fd=3 保持 mysql 连接打开以进行写入：
exec 3> >(mysql)
echo "SELECT 1;" >&3
echo "SELECT 2;" >&3
exec 3>&-
Keep the mysql connection open using fd=3 for reading:
使用 fd=3 保持 mysql 连接打开以进行读取：
exec 3< <(echo "SELECT 1;SELECT 2;"|mysql|sed '1d')
while read <&3
do
echo $REPLY
done
Note: sed '1d' removes the header.
注意： sed '1d' 删除标题。
Is there any way to merge these so you can write to one fd and read from another?
有什么方法可以合并这些，以便您可以写入一个 fd 并从另一个 fd 读取？
回答by conny
To the best of my understanding your question: coproc'savailable in zsh/ksh and also bash v4+ might be similar to what you have in mind, e.g.
据我所知，您的问题是：在 zsh/ksh 和 bash v4+ 中可用的coproc可能与您的想法相似，例如
bash4-4.1$ coproc MYSQL mysql -B -uroot
[1] 10603
bash4-4.1$ jobs
[1]+ Running coproc COPROC MYSQL mysql -B -uroot &
bash4-4.1$ echo 'show databases;' | MYSQL
Database
information_schema
...
The command is kept running in the background, its stdin/stdout can accessed, it will finish (as a result its standard input closing/EOFing) as soon as the current shell exists...
该命令在后台保持运行，可以访问它的 stdin/stdout，只要当前 shell 存在，它就会完成（因此它的标准输入关闭/ EOFing）......
回答by NORMAN GEIST
I know this thread is old, but I was also looking for a comfortable bash mysql session implementation and didn't found something good enough for my needs, so I wrote my own one which I'd like to share with the world.
我知道这个线程很旧，但我也在寻找一个舒适的 bash mysql 会话实现，但没有找到足以满足我需求的东西，所以我写了我自己的一个，我想与世界分享。
############### BASIC MYSQL SESSION IMPLEMENTATION FOR BASH (by Norman
Geist 2015) #############
# requires coproc, stdbuf, mysql
#args: handle query
function mysql_check {
local handle
handle=(${1//_/ })
#has right structure && is still running && we opened it?
if [[ ${#handle[*]} == 3 ]] && ps -p ${handle[2]} 2>> /dev/null >> /dev/null && { echo "" >&${handle[1]}; } 2> /dev/null; then
return 0
fi
return 1
}
# open mysql connection
#args: -u user [-H host] [-p passwd] -d db
#returns $HANDLE
function mysql_connect {
local argv argc user pass host db HANDLEID i
#prepare args
argv=($*)
argc=${#argv[*]}
#get options
user=""
pass=""
host="localhost"
db=""
for ((i=0; $i < $argc; i++))
do
if [[ ${argv[$i]} == "-h" ]]; then
echo "Usage: -u user [-H host] [-p passwd] -d db"
return 0
elif [[ ${argv[$i]} == "-u" ]]; then
i=$[$i+1]
if [[ ${#argv[$i]} -gt 0 ]]; then
user=${argv[$i]}
else
echo "ERROR: -u expects argument!"
return 1
fi
elif [[ ${argv[$i]} == "-p" ]]; then
i=$[$i+1]
if [[ ${#argv[$i]} -gt 0 ]]; then
pass="-p"${argv[$i]}
else
echo "ERROR: -p expects argument!"
return 1
fi
elif [[ ${argv[$i]} == "-H" ]]; then
i=$[$i+1]
if [[ ${#argv[$i]} -gt 0 ]]; then
host=${argv[$i]}
else
echo "ERROR: -H expects argument!"
return 1
fi
elif [[ ${argv[$i]} == "-d" ]]; then
i=$[$i+1]
if [[ ${#argv[$i]} -gt 0 ]]; then
db=${argv[$i]}
else
echo "ERROR: -d expects argument!"
return 1
fi
fi
done
if [[ ${#user} -lt 1 || ${#db} -lt 1 ]]; then
echo "ERROR: Options -u user and -d db are required!"
return 1;
fi
#init connection and channels
#we do it in XML cause otherwise we can't detect the end of data and so would need a read timeout O_o
HANDLEID="MYSQL$RANDOM"
eval "coproc $HANDLEID { stdbuf -oL mysql -u $user $pass -h $host -D $db --force --unbuffered --xml -vvv 2>&1; }" 2> /dev/null
HANDLE=$(eval 'echo ${'${HANDLEID}'[0]}_${'${HANDLEID}'[1]}_${'${HANDLEID}'_PID}')
if mysql_check $HANDLE; then
export HANDLE
return 0
else
echo "ERROR: Connection failed to $user@$host->DB:$db!"
return 1
fi
}
#args: handle query
#return: $DATA[0] = affected rows/number of sets;
# $DATA[1] = key=>values pairs following
# $DATA[2]key; DATA[3]=val ...
function mysql_query {
local handle query affected line results_open row_open cols key val
if ! mysql_check ; then
echo "ERROR: Connection not open!"
return 1
fi
handle=(${1//_/ })
#delimit query; otherwise we block forever/timeout
query=
if [[ ! "$query" =~ \;$ ]]; then
query="$query;"
fi
#send query
echo "$query" >&${handle[1]}
#get output
DATA=();
DATA[0]=0
DATA[1]=0
results_open=0
row_open=0
cols=0
while read -t $MYSQL_READ_TIMEOUT -ru ${handle[0]} line
do
#WAS ERROR?
if [[ "$line" == *"ERROR"* ]]; then
echo "$line"
return 1
#WAS INSERT/UPDATE?
elif [[ "$line" == *"Query OK"* ]]; then
affected=$([[ "$line" =~ Query\ OK\,\ ([0-9]+)\ rows?\ affected ]] && echo ${BASH_REMATCH[1]})
DATA[0]=$affected
export DATA
return 0
fi
#BEGIN OF RESULTS
if [[ $line =~ \<resultset ]]; then
results_open=1
fi
#RESULTS
if [[ $results_open == 1 ]]; then
if [[ $line =~ \<row ]]; then
row_open=1
cols=0
elif [[ $line =~ \<field && $row_open == 1 ]]; then
key=$([[ "$line" =~ name\=\"([^\"]+)\" ]] && echo ${BASH_REMATCH[1]})
val=$([[ "$line" =~ \>(.*)\<\/ ]] && echo ${BASH_REMATCH[1]} || echo "NULL")
DATA[${#DATA[*]}]=$key
DATA[${#DATA[*]}]=$val
cols=$[$cols+1]
elif [[ $line =~ \<\/row ]]; then
row_open=0
DATA[0]=$[${DATA[0]}+1]
DATA[1]=$cols
fi
fi
#END OF RESULTS
if [[ $line =~ \<\/resultset ]]; then
export DATA
return 0
fi
done
#we can only get here
#if read times out O_o
echo "$FUNCNAME: Read timed out!"
return 1
}
#args: handle
function mysql_close {
local handle
if ! mysql_check ; then
echo "ERROR: Connection not open!"
return 1
fi
handle=(${1//_/ })
echo "exit;" >&${handle[1]}
if ! mysql_check ; then
return 0
else
echo "ERROR: Couldn't close connection!"
return 1
fi
}
############### END BASIC MYSQL SESSION IMPLEMENTATION FOR BASH ################################
# Example usage
#define timeout for read command, in case of server error etc.
export MYSQL_READ_TIMEOUT=10
# Connect to db and get $HANDLE
mysql_connect -u mydbuser -d mydb -H mydbserver
#query db and get $DATA
mysql_query $HANDLE "SELECT dt_whatever from tbl_lol WHERE dt_rofl=10"
#close connection
mysql_close $HANDLE
NOTES:
笔记：
- Save $HANDLE to a new variable after connection to open as many connections as you like
- You can't exchange $HANDLE between bash sessions
- You need linux packages "coproc" "stdbuf" "mysql"
- Return DATA is of a bash array
- 连接后将 $HANDLE 保存到新变量以打开任意数量的连接
- 你不能在 bash 会话之间交换 $HANDLE
- 你需要 linux 包“coproc”“stdbuf”“mysql”
- 返回数据是一个 bash 数组
$DATA[0] = affected rows/number of sets; $DATA[1] = number of key=>values pairs following; $DATA[2] = key1; $DATA[3] = value1; [...] $DATA[n-1] = keyn; $DATA[n] = valuen;
$DATA[0] = affected rows/number of sets; $DATA[1] = number of key=>values pairs following; $DATA[2] = key1; $DATA[3] = value1; [...] $DATA[n-1] = keyn; $DATA[n] = valuen;
- Generally all queries should work, even "SELECT count(*)"
- 通常所有查询都应该有效，即使是“SELECT count(*)”
Example how to loop the returned data of a two column query
示例如何循环两列查询的返回数据
eg. "SELECT dt_id, dt_name FROM ..."
例如。“选择 dt_id，dt_name 来自 ...”
fields=2
for ((i=2; $i<$((${DATA[0]}*${DATA[1]}*$fields)); i+=$((${DATA[1]}*$fields))))
do
field1key = ${DATA[$i]}; #this is "dt_id"
field1value = ${DATA[$i+1]}; #this is the value for dt_id
field2key = ${DATA[$i+2]}; #this is "dt_name"
field2value = ${DATA[$i+3]}; #this is the value for dt_name
done
