5 useful MySQL Command Options-pager-prompt-rehash-tee-system

There are set of commands that MySQL itself interprets. You may use “help” or “\h” at the mysql> prompt to list them.

Below are the 5 most useful MySQL Command Options.

1. \# OR rehash: Enable automatic rehashing.

Do you have long table names, you find it difficult to remember tablenames or you’re just as lazy as I am, rehashing is good for you.
Enter:
mysql> \#
or
mysql> rehash
This enables database, table, and column name completion.
To complete a name, enter the initial part of name and press Tab. If the name is unambiguous, mysql will complete it for you.

For eg. below will fill information_schema if there is no ambiguity in inform* names:
mysql> use inform

By default this feature is enabled and you can disable it by option –disable-auto-rehash.

2. \! OR system : Execute system command

System or \! will allow you to execute system commands without exiting from mysql prompt.
A very simple example of the use of this command will be of searching a path to sql!!

mysql> system locate tobe_loaded.sql
/path/to/tobe_loaded.sql

And that path you may use to load data:
mysql>source /path/to/tobe_loaded.sql

3. \T OR tee : Log commands and outputs to a (log) file

\T filename will log (append) your sql commands and it’s output to ‘filename’ file. This command is usefule while debugging through.

To stop logging you may use notee command.

[ad#ad-2-300x250]

4. \R OR prompt : Set mysql prompt

Command prompt [PROMPT-STRING] will set the mysql prompt as per specified string. Using prompt without parameter will set it to default “mysql>”.

It comes very handy when you’re working simultaneously with multiple MySQL prompts with different databases or servers.

For eg:

mysql> prompt \u@\h [\d]>
PROMPT set to ‘\u@\h [\d]>

username@hostname [Current-Default-Database]>

Setting prompt from my.cnf: In my.cnf you may specify the default prompt string under [mysql] section.

[mysql]
prompt=\\u@\\h [\\d]>

*Note the escaped slashes(\).

We do have many other prompt options available.

  • \S – semicolon
  • \’ – single quote
  • \” – double quote
  • \v – server version
  • \p – port
  • \\ – backslash
  • \n – newline
  • \t – tab
  • \ – space (Not a space after \ )
  • \d – default database
  • \h – default host
  • \_ – space
  • \c – a mysql statement counter. keeps increasing as you type commands.
  • \u – username
  • \U – username@hostname accountname

For Date time related settings:

  • \D – full current date (as shown in the above example)
  • \w – 3 letter day of the week (e.g. Mon)
  • \y – the two digit year
  • \Y – the four digit year
  • \o – month in number
  • \O – 3 letter month (e.g. Jan)
  • \R – current time in 24 HR format
  • \r – current time in 12 hour format
  • \m – the minutes
  • \s – the seconds
  • \P – AM or PM

I wish MySQL’d have provided short-date format in prompts.

5. Pager : Uses the specified command for paging query output.

Pager command will handle the query output paging as per specified command.
mysql>pager cat > /path/to/file.log

Will output every query output to /path/to/file.log

If you’re expecting a long query result you may use more / less linux command as a pager which will help reading.
mysql>pager less
This will allow to scroll up / down with your MySQL query result just like the less command.

If you have large number of columns and have readability problems you may use:
mysql>pager less -S

OR

mysql>pager less -S -X

This will allow you to scroll query result horizontally using the left-arrow and right-arrow keys.


You can also search through the result set with /search-term in result set.
Pager will work only in Linux/Unix systems.