How to Change in SQL Prompt format in Oracle , MySQL, PostgreSQL(数据库比较系列七)

by

像UNIX的PS1环境变量可以改变shell操作提示符， 在日常工作环境中可以提升一些效率可以防止一些误操作， 很多年前在看tom关于在练习oracle操作前的一些环境配置像login.sql， 比起”SQL >”还可以显示当前的用户或数据库名很是欣喜， 下面记录Oracle , MySQL, PostgreSQL三个主流数据库改变命令行提示符的方法。

— Oracle
sqlplus中执行set sqlprompt或增加到logoin.sql/global.sql脚本中自动触发
e.g.

col ver  new_value ver
col host new_value host
col inst new_value inst
set termout off
select substr(version,1,instr(version,'.',1,4)) ver,
       upper(instance_name) inst,
       substr(host_name,1,instr(host_name,'.',1,1) - 1) host
 from v$instance;
set termout on
--set sqlprompt "&&ver&&_USER@&&_CONNECT_IDENTIFIER SQL> "
set sqlprompt "&&ver&&_USER@&&host/&&inst SQL> "
set linesize 500 pagesize 2000 timi on
alter session set max_dump_file_size = unlimited;

-- or --

col "_myprompt" new_value myprompt
set termout off
select
    chr(27)||'[31m'||
    substr('&_USER@&_CONNECT_IDENTIFIER'||rtrim(' &_PRIVILEGE')||'/'||
        trunc(&_O_RELEASE/100000000) || '.' ||
        mod(trunc(&_O_RELEASE/1000000),100) || '.' ||
        mod(trunc(&_O_RELEASE/10000),100) || '.' ||
        mod(trunc(&_O_RELEASE/100),100),
        1, 36)
    ||chr(27)||'[m'||chr(10)||'SQL> ' "_myprompt"
from dual;
set termout on
set sqlp "&myprompt"


SQL> @login

Session altered.

Elapsed: 00:00:00.00
19.0.0.0.SYS@/ANBOB19C SQL>

— MySQL
配置MYSQL_PS1 或 配置在/etc/my.cnf or .my.cnf 文件中

The MYSQL_PS1 in this example displays the following three information in the prompt:

  • Generic variables:
    • \S displays semicolon
    • \’ displays single quote
    • \” displays double quote
    • \v displays server version
    • \p displays port
    • \\ displays backslash
    • \n displays newline
    • \t displays tab
    • \ displays space (there is a space after \ )
    • \d displays default database
    • \h displays default host
    • \_ displays space (there is a underscore after \ )
    • \c displays a mysql statement counter. keeps increasing as you type commands.
    • \u displays username
    • \U displays username@hostname accountname

    Date related variables:

    • \D displays full current date (as shown in the above example)
    • \w displays 3 letter day of the week (e.g. Mon)
    • \y displays the two digit year
    • \Y displays the four digit year
    • \o displays month in number
    • \O displays 3 letter month (e.g. Jan)
    • \R displays current time in 24 HR format
    • \r displays current time in 12 hour format
    • \m displays the minutes
    • \s displays the seconds
    • \P displays AM or PM
$ export MYSQL_PS1="\u@\h [\d]> "

$ mysql -u root -pyour-password -D xxxCRM

root@dev-db [XXcrm]>
mysql> prompt (\u@\h) [\d] \R:\m:\s>\_

— postgresql

在gsql中配置set PROMPT1或配置到.psqlrc文件中

vi ~/.psqlrc

\set PROMPT1 '%M:%> %n@%/%R%#%x '
  • %M refers to the database server’s hostname – is “[local]” if the connection is over a Unix domain socket
  • %> refers to the listening port
  • %n refers to the session username
  • %/ refers the current database
  • %R refers to whether you’re in single-line mode (^) or disconnected (!) but is normally =
  • %# refers to whether you’re a superuser (#) or a regular user (>)
  • %x refers to the transaction status – usually blank unless in a transaction block (*)

Colors

The prompt color can be edited with the psqlrc. To make the port number red add the following.

\set PROMPT1 '%M:%[%033[1;31m%]%>%[%033[0m%] %n@%/%R%#%x '

There are various colors you can use – change the value 31 to:

  • 32 for green
  • 33 for yellow
  • 34 for blue
  • 35 for magenta
  • 36 for cyan
  • 37 for white

像UNIX PS1环境的配置参考Bash Shell PS1: 10 Examples to Make Your Linux Prompt like Angelina Jolie