That you could perchance rating your bash scripts stride otherwise based mostly on the contents of a MySQL database by connecting to it from the command line and passing a demand, which that you would be succesful to even employ in if blocks to branch based mostly on a mark.
Show: Our examples exhibit using the foundation person on MySQL, but that you would be succesful to have to replace a arresting person myth there.
Speed SQL Queries from a Bash Script
The syntax for running internal a bash script is the identical as having access to a database from the command line:
mysql -u root -pPassword -h hostname -D dbname -e 'demand'
In case your database is running within the community, you could perchance even omit the -h flag. In case your running a trend database, you could perchance even omit the -p flag if your database has no password.
This could perchance output the outcomes of the demand to STDOUT, which that you would be succesful to even pipe to a file:
mysql -u root -D dbname -e 'SELECT FROM desk' > file
…or retailer in a variable with the $( ) kind:
variable=$(mysql -u root -D dbname -e 'SELECT FROM desk')
Nonetheless, the output acquired’t be very gleaming by default, so that you could perchance even rating it less complicated to work with by utilizing the -B flag to print in Tab Separated Values (TSV) structure and the -N flag to omit column headers.
TSV files are the lesser-outmoded cousin of CSV files, which that you would be succesful to even convert from the command line.
Checking a Explicit Set and Branching
Must you prefer to branch based mostly on a mark to your database, you could perchance even demand for the actual row and column, and retailer the response in a variable. That you could perchance then employ a bash if block to branch based mostly on the contents of that variable.
area=$(mysql -u root -BNe 'USE test; SELECT mark FROM test WHERE id=1')
if [ $field == 'TEST' ]; then
//enact stuff
fi
This most consuming works will dangle to you’re soliciting for a particular worth and performing a straightforward comparison. Must you’ll need extra manipulation than bash affords, you could perchance even employ awk, which is appealing to bewitch out out person columns from textual teach material and assemble comparisons:
if [ $(echo $field | awk '{if ($1 == value && $2 == value2) print 1; else print 0}') == "1" ]; then
//enact stuff
fi
Right here, the awk command prints out legal or unfounded (1 or 0), which the bash if can employ to compare and branch.
Each of these examples bewitch your SQL demand is most consuming returning a single row, which that you would be succesful to even guarantee by selecting based mostly on ID. Nonetheless will dangle to you’re running a extra complex demand, you could perchance even just have to restrict the response to the first row with the SQL command LIMIT 1.
Error Checking: Take a look at If a Database or Desk Exists
Even as you’re presumably penning this script particularly to your server, a little of error checking by no manner hurt anybody. You’ll have to study that the database and tables you’ll be working with in fact exist sooner than you spin messing with them. Additionally, if the connection to the database isn’t working, this could perchance perchance also just error out here as properly.
The SQL command 'USE dbname' will rating an error if dbname doesn’t exist (return code > 0). That you could perchance employ this in an if block to study if the database is smartly configured:
if mysql -u root -e 'USE mydbname'; then
//database exists, enact stuff
fi
That you could perchance invert the if block by starting the boolean with an !, which will stride the code most consuming when the database isn’t configured, which is invaluable for doing first time setup.
Within the same trend, you could perchance even test if tables exist by seeking to rating entry to the first row:
if mysql -u root -e 'USE mydbname; SELECT FROM tablename LIMIT 1'; then //database and desk exist, enact extra stuff fi
Since the if block tests the return code of without reference to order you chuck into it, and MySQL instructions will return an error code within the event that they stride into considerations, you could perchance even employ any command in an if block to myth for errors.
