There are set of commands that MySQL itself interprets. You may use “help” or “\h” at the mysql> prompt to list them.
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.
Kedar