Wednesday, 11 June 2014

How to execute mysql query from a file in your mysql client terminal?

Being a terminal fan myself, I usually find myself running queries in the mysql client instead of a UI interface as it is much faster. You get to see the results instantaneously.

One thing which is pretty tedious is editing a big query again after once running it as the whole multi-line formatted query now appears on a single line, thus reducing its readability.

But no problems, you can edit your query from a file and run the file from your mysql client terminal as many times as you want with as many edits.

To do so, follow the below steps:

1. Open your terminal and cd into the folder you want to store our sample mysql file. Then save your query in a sample file called my_query.sql

$ cd /path/to/folder$ vim my_query.sql

Save a sample query like:

SELECT * FROM employeesWHERE type LIKE 'hard_working'OR type LIKE 'cool'OR type LIKE 'crazy';

2. Then open mysql client from terminal and execute the query file using SOURCE command.

$ mysql -u username -p> SOURCE my_query.sql;
We get the results here.

3. Edit your source file as many times and execute the SOURCE command again from the terminal. Be sure you are able to help SOURCE command in locating the source file location. Since we have opened the mysql client in the same folder which contains the source file, so there was no need for any path. If path varies, please do provide it.