Can I change the mysql prompt with selected database as we do in hive?

Question

In hive, I can set the prompt to be the currently selected database. Is it possible to do the same in MySQL?

I want to replace mysql with kundoor in the image below.

Poulter

Answer 1

Type this in your terminal

export MYSQL_PS1="\u@\h [\d]> "

This will create the following

user@host [databasename]>

Manofwar

username/host = root@shekh-Latitude-3550 as per above syntax export MYSQL_PS1="\root@\shekh-Latitude-3550 [kundoor]" shall it fire this command from terminal or from mysql promt. – Poulter

@ShekhFirozAlam no only type export MYSQL_PS1="\u@\h [\d]> " in your terminal. – Manofwar

On Windows (when you are getting "The syntax of the command is incorrect.") you should do: set MYSQL_PS1=\u@\h [\d]^> (Note the ^ before the >) – Fluidize

Answer 2

In addition to using the MYSQL_PS1 environment variable @Noob describes, you can use the prompt command within the mysql client:

mysql> prompt \u@\h [\d] 

(type a space at the end of the line to get a space after the prompt)

See https://dev.mysql.com/doc/refman/5.7/en/mysql-commands.html for more on mysql client commands.

You can make this default by editing your MySQL options file:

[mysql]
prompt \u@\h [\d] 

Massive

Answer 3

For example, using --prompt=(--prompt), you can change the default prompt mysql> to apple> of apple database with login as shown below. *\d is the current database but if the current database is not selected, \d is (none) and \_ is a space according to the doc:

mysql -u john -p --prompt='\d> ' apple
...
apple> 

Or:

mysql -u john -p --prompt='\d>\_' apple

Or:

mysql -u john -p --prompt=\\d\>\\_ apple

Or, using prompt(PROMPT) or \R, you can change the default prompt mysql> to apple> of apple database after login as shown below. *Don't forget to put a space just after \d>:

mysql -u john -p apple
...              ↓ A space
mysql> prompt \d> 
PROMPT set to '\d>\_'
apple>

Or:

mysql -u john -p apple
...          ↓ A space
mysql> \R \d> 
PROMPT set to '\d> '
apple>

Or:

mysql -u john -p apple
...
mysql> prompt \d>\_
PROMPT set to '\d>\_'
apple>

Or:

mysql -u john -p apple
...
mysql> \R \d>\_
PROMPT set to '\d>\_'
apple>

Or on Windows, you can set the prompt under [mysql] in my.ini as shown below. *My answer explains [mysql] and my answer explains where my.ini is located on Windows:

# "my.ini"

[mysql]
...
prompt='\d> '

Or:

# "my.ini"

[mysql]
...
prompt='\d>\_'

Or:

# "my.ini"

[mysql]
...
prompt=\d>\_

Then, you can change the default prompt mysql> to apple> of apple database with login by setting my.ini's location to --defaults-file= or --defaults-extra-file= as shown below. *--defaults-file= or --defaults-extra-file= must be the 1st option otherwise there is the error:

mysql --defaults-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini' -u john -p apple
...
apple> 

Or:

mysql --defaults-extra-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini' -u john -p apple
...
apple> 

Harts