MySQL Command Line Tips

Working with MySQL’s command line shell is sometimes an exercise in patience. Here are some tips to make your life easier:

Display results vertically to avoid line-wrapping

If you do a select * from a table with a lot of columns, you will often end up with the results being wrapped across your terminal, making them just about impossible to read. To display the results vertically (i.e. <column>: <value>), end your query with \G instead of ;

select * from users \G

Clear the screen

If you type help it says that \c is the clear command - this is actually something else (I will cover it later). To clear your screen of output and put a fresh mysql> at the top, type: \! clear (or on Windows, \! cls).

Cancel the current command and clear the input

How often do you type select * fom uesrs or similar? If you are used to Linux, your instinctive response is to press Ctrl-C - unfortunately in older versions of MySQL this will quit the shell and dump you back to bash (or equivalent). Try this instead:

slect * fom typo\c

The input will be cleared and MySQL won’t display any errors or warnings.

Edit your last query

Often you will select * when you don’t need to, and if it’s a multi-line query that you have pasted into the shell, it is laborious to fix. Try \e;.

This will open your last run query in whatever text editor you have defined in $EDITOR (vim/nano, etc.). You can then edit your query and then save and exit, and it will execute with the changes.