保持MySQL连接开放提交壳牌多个查询 [英] Keep MySQL connection open to submit multiple queries from shell

问题描述

我想了解更多关于MySQL命令行工具是如何工作的。

I'd like to learn more about how the MySQL command line tool works.

假设我希望能够脚本多个SQL语句和使用shell脚本输出语句的说明，因为它们被处理。这里有一个通用的例子del_sql，ins_sql和qry_sql会提到任何删除，插入和选择相同的数据集查询。

Suppose I would like to be able to script multiple SQL statements and use shell scripting to output a description of the statements as they are processed. Here's a generic example where del_sql, ins_sql, and qry_sql would refer to any delete, insert, and select query on the same data set.

echo "Deleting data in the range."
echo $del_sql | mysql -h "$mysql_host" -u "$mysql_user" -p"$mysql_pass" -D "$mysql_db"

echo "Inserting data into the range."
echo $ins_sql | mysql -h "$mysql_host" -u "$mysql_user" -p"$mysql_pass" -D "$mysql_db"

echo "Querying data from the range."
echo $qry_sql | mysql -h "$mysql_host" -u "$mysql_user" -p"$mysql_pass" -D "$mysql_db"

什么，我关心的是 - 这是否打开和关闭三个单独的连接到数据库，这是不是一次提交所有报表更贵？在这个例子中假设all_sql是包含用分号分隔的三个语句的字符串。

What I'm concerned about is - does this open and close three separate connections to the database, and is this more expensive than submitting all statements at once? In this example suppose that all_sql is a string containing all three statements separated by semicolons.

echo "Deleting, inserting, and querying data in the range."
echo $all_sql | mysql -h "$mysql_host" -u "$mysql_user" -p"$mysql_pass" -D "$mysql_db"

我试过以下只是它瞎搞，认为它将使感。

I tried the following just messing around with it, thinking it would make sense.

mysql -h "$mysql_host" -u "$mysql_user" -p"$mysql_pass" -D "$mysql_db"
echo $del_sql | mysql -e
echo $ins_sql | mysql -e
echo $qry_sql | mysql -e

但是，这显然是不正确的语法。

But this is clearly incorrect syntax.

推荐答案

我不是程序员，但在数据库方面我可以告诉你，是，MySQL非常有效地为您处理此。它使用一个线程缓存/池。每个线程基本上是一个连接。

I'm no programmer, but on database side I can tell you, that MySQL handles this quite efficiently for you. It uses a thread cache / pool. Each thread is basically a connection.

您可以阅读更多关于它在这里： MySQL如何使用线程对于客户端连接。

You can read more about it here: How MySQL Uses Threads for Client Connections.

你甚至可以观察到这一点，当您登录到通过命令行客户端的数据库，然后发出在另一个shell脚本。

And you can even observe this, when you log into your database via command line client and then issue your script in another shell.

做一个

SHOW PROCESSLIST;

在你的脚本跑过，你会看到你的连接仍然存在状态为睡觉。它会被重用，当您再次执行你的脚本。

After your script ran through, you will see that your connection is still there with the status SLEEPING. It will be reused when you execute your script again.

这篇关于保持MySQL连接开放提交壳牌多个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！