Keep MySQL connection open to submit multiple quer

Question

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

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"

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.

Answer 1

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.

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

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

Do a

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.