yourfirstserver.com

5 Useful Mysql Command Options-Pager-Prompt-Rehash-Tee-System

Tweet

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.

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.

Original Article can be found here: http://kedar.nitty-witty.com/blog/5-useful-mysql-command-options-pager-prompt-rehash-tee-system/