Mysql client usage tips

Performing your SQL tasks using mysql client might not be as easy as it is in any web or graphical user interface (like phpmyadmin for instance), but there are few tips that could definitely help easing it a bit.

Commands

The mysql client supports commands. These have long and short form, you can use both ways though the examples bellow will be only in the short form. The commands are usually added at the end of the line, after the SQL statement (but not all of them. e.g. \u)

  • \c (or clear)- this command clears an currently written statement. That’s particularly useful if for instance you have been writing multiline query and noticed that it has mistakes in the previous lines – then just add \c at the place you are typing, hit enter and the whole statement will be cleared (and won’t be executed of course). E.g.:
mysql> SELECT * FROM table\c
  • \G (or ego) – this command displays the result of the query vertically. E.g.:
mysql> SELECT * FROM pages LIMIT 1\G
*************************** 1. row ***************************
  id: 1
name: home
  • \u (or use) – use it to select database., e.g. if the database is called mysite:
mysql> \u mysite
  • \e (or edit) – edit the query in external editor (that is set in the $EDITOR system variable)
  • \s (or status) – print the server status information
  • \! (or system) – execute system shell command, e.g. to set vim as the default editor:
mysql> \! export EDITOR=vim
  • \P (or pager) – set the mysql client pager to the  given command. E.g. to set less as pager:
mysql> \P less

Setting the pager is quite useful when a query returns many rows – then browsing the result using system commands like less is a lot easier.

  • \T (or tee) – sets an out file and then append everything (all you type and all results/errors/warnings you see) into it, e.g.:
mysql > \T full_log.txt
  • \. (or source) – executes sql from file, e.g.:
mysql > \. mysite_backup.sql

Remember that if you ever wonder about the exact command or syntax, just use the help command (\h) in the mysql client to get the full commands list ;)

Which is your favorite command? Mine is pager.