Home | About Chris Hope | Contact Chris Hope | Search:

Run a single MySQL query from the command line

Posted November 6th, 2008 in MySql

The MySQL command line utility allows you to run queries and view query results etc from an interactive command prompt. It is also possible to run a single query from the command line without actually going into the interactive prompt. This post looks at how to do this.

As an example, I needed to load some data into a new database the other day from a dump from another server. Normally I'd do something like this:

mysql -u [username] -p somedb < somedb.sql

The database didn't actually exist so I got this error:

ERROR 1049 (42000): Unknown database 'somedb'

The obvious solution is to create the database and then run the same command to load the data. I could either do this by adding the "create database somedb" to the start of the text file I was loading, fire up the MySQL command line, run the command and exit back to the bash shell, or use the -e flag to execute a single query.

So to run a single MySQL query from your regular shell instead of from MySQL's interactive command line you would do this:

mysql -u [username] -p [dbname] -e [query]

In my case I wanted to create the database so it looked like this (note I didn't need to specify a database because my query didn't affect a specific database):

mysql -u [username] -p -e "create database somedb"

You can run any valid queries against any databases you have permissions for, in the same way as running the query from the MySQL command line. Any output will appear on your shell's command line. For example:

 $ mysql -u root -p somedb -e "select * from mytable"
Enter password:
+------------+-------------+----------------------------+
| mytable_id | category_id | name                       |
+------------+-------------+----------------------------+
|          1 |           1 | Lorem ipsum dolor sit amet |
|          2 |           1 | Ut purus est               |
|          3 |           2 | Leo sed condimentum semper |
|          4 |           2 | Donec velit neque          |
|          5 |           3 | Maecenas ullamcorper       |
+------------+-------------+----------------------------+

Update and insert queries do not output anything if they are successful (displaying errors if not successful), but select queries do as shown in the above example.

Comments

blog comments powered by Disqus