There are several good GUI options out there for working with MySQL. For the CLI fans or those forced to use a machine with no GUI it’s possible to have a good experience using the default MySQL1 client.
mysql command accepts a variety of options.
They can be passed in the initial call, set on the
mysql terminal or permanently added in a
There are two in particular that are extremely useful.
As you can see, the default prompt lacks any useful information.
The prompt can show time, user and server info. You can see the full list of options on MySQL’s commands page. I prefer the current time, the name of the server and the current database I’m working with. Adding a newline provides a nice divide between queries and ensures that they all start at the same place on the screen.
Note: The pipe below indicates the cursor.
mysql> prompt \R:\m\_\h:\d\n)\_ PROMPT set to '\R:\m\_\h:\d\n)\_' 10:32 localhost:companyco_test ) |
Knowing that you’re running SQL commands on the correct server and database can help prevent painful mistakes.
mysql doesn’t do any paging.
Query data flies by as fast as it can and you’re forced to scroll back if you missed something.
Most people I’ve come accross use
less(418) as their default pager.
less with the
inSFX options passed in.
-iAdd smart case matching to your searches. This means that searches will only be case sensitive if you use an upper cased letter in the search.
-nRemove line numbers from the output. We don’t need line numbers and on larger outputs
lesswill run faster without them.
-STurn off line wrapping. The query output will show one row per line (newlines in the data will still cause a line break). You can scroll horizontally through the data with the left and right arrow keys.
-FIf the output fits on the screen
lesswill automatically exit. This stops you from having to quit the pager for every little query. In
mysqlit must be used in combination with
-Xor you’ll never see the output.
-XDo not clear output on exit. By default
lesswill clear the screen and restore it back to the way it was before the pager was called. In
mysqlyou probably want the output to stay on the screen so it can be easily referenced.
mysql> pager less -inSFX PAGER set to 'less -inSFX'
Working in the
mysql terminal is a great way to find settings that work for you.
Once that’s done the settings can be added to a
Put them under
[mysql] and any time
mysql runs it will use your preferred options.
[mysql] pager=less -inSFX prompt=\R:\m\_\h:\d\n)\_
Version 5.5.25a ↩