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

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-300×250]

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.