March 10th, 2010 
admin Author: kedar
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/
Article Source: http://www.articlesbase.com/databases-articles/5-useful-mysql-command-optionspagerpromptrehashteesystem-1955265.html
About the Author
Posted in 
Pakistan Rupee Exchange Rate