Tuesday, March 10, 2009

Mysql: Change Default Prompt

Not so long ago, I used to use mysql command line client in a very traditional way.
you know, like a simple login using
mysql -u[username] -p[password] -h [hostname] -D [database]

Recently just out of curiousity  I typed "help" at the  mysql prompt. It gave me a whole list of commands. Then I realized that I can do a lot more with the mysql command line.

mysql> help

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?           (\?) Synonym for `help'.
clear      (\c) Clear command.
connect  (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
edit        (\e) Edit command with $EDITOR.
ego        (\G) Send command to mysql server, display result vertically.
exit        (\q) Exit mysql. Same as quit.
go         (\g) Send command to mysql server.
help       (\h) Display this help.
nopager  (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print       (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit         (\q) Quit mysql.
rehash     (\#) Rebuild completion hash.
source     (\.) Execute an SQL script file. Takes a file name as an argument.
status      (\s) Get status information from the server.
system     (\!) Execute a system shell command.
tee          (\T) Set outfile [to_outfile]. Append everything into given outfile.
use          (\u) Use another database. Takes database name as argument.
charset     (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings   (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

Looking at the list I realize I was using "\q", "\c" and "\G" for quiet sometime now. But wasnt aware of other commands. While I am investigating other commands. Let me tell you the most interesting command, which caught my attention immediately.

It is  PROMPT. It helps you in customizing the default mysql prompt i.e. "mysql >"

To change the prompt you just need to enter prompt command with what value  you want
e.g. prompt "hello world"
and your prompt will be set to "hello world". But I guess you would like to change it to something meaningful. Here comes the special sequences, to help you with this.
mysql> prompt [mysql:\u@\d][\R:\m:\s \P] $
PROMPT set to '[mysql:\u@\d][\R:\m:\s \P] $'

and now your prompt will look like:
[mysql:root@test][10:55:58 am] $
You see I have used "\u" "\d" "\R" \m \s, \P .. They mean, in that order, 'current user name', 'current database', 'hrs of current time', 'minutes for current time', 'seconds for current time', 'AM/PM indicator'. These are the special sequences I was talking about.

There is a whole list of special sequences, you can find it at mysql documentation page.  So what are you waiting now. Go try out and have your very own customized mysql prompt :D

Update: To make sure that you get the same prompt everytime, you can modify mysql config file (typically /etc/my.cnf) or create an file name .my.cnf in your home directory with a content similar to this:
prompt=mysql:\\u@\\d [\\R:\\m:\\s \\P]$\\_

1 comment:

  1. BlueHost is the best hosting company with plans for all of your hosting requirements.