更新时间:2023-03-17 GMT+08:00

gsql概述

基本功能

  • 连接数据库: 通过gsql客户端远程连接GaussDB(DWS)数据库。

    gsql创建连接时,会有5分钟超时时间。如果在这个时间内,数据库未正确地接受连接并对身份进行认证,gsql将超时退出。

    针对此问题,可以参考常见问题处理

  • 执行SQL语句:支持交互式地键入并执行SQL语句,也可以执行一个文件中指定的SQL语句。
  • 执行元命令:元命令可以帮助管理员查看数据库对象的信息、查询缓存区信息、格式化SQL输出结果,以及连接到新的数据库等。元命令的详细说明请参见元命令参考

高级特性

gsql的高级特性如表1所示。

表1 gsql高级特性

特性名称

描述

变量

gsql提供类似于Linux的shell命令的变量特性,可以使用gsql的元命令\set设置一个变量,格式如下:

\set varname value

要删除一个变量请使用如下方式:

\unset varname
说明:
  • 变量只是简单的名称/值对,值的长度由特殊变量VAR_MAX_LENGTH决定,详细参见表2
  • 变量名称必须由字母(包括非拉丁字母)、数字和下划线组成,且对大小写敏感。
  • 如果使用\set varname的格式(不带第二个参数),则只是设置这个变量而没有给变量赋值。
  • 可以使用不带参数的\set来显示所有变量的值。

变量的示例和详细说明请参见变量

SQL代换

利用gsql的变量特性,可以将常用的SQL语句设置为变量,以简化操作。

SQL代换的示例和详细说明请参见SQL代换

自定义提示符

gsql使用的提示符支持用户自定义。可以通过修改gsql预留的三个变量PROMPT1、PROMPT2、PROMPT3来改变提示符。

这三个变量的值可以用户自定义,也可以使用gsql预定义的值。详细请参见提示符

客户端操作历史记录

gsql支持客户端操作历史记录,当客户端连接时指定“-r”参数,此功能被打开。可以通过\set设置记录历史的条数,例如,\set HISTSIZE 50,将记录历史的条数设置为50,\set HISTSIZE 0,不记录历史。

说明:
  • 客户端操作历史记录条数默认设置为32条,最多支持记录500条。当客户端交互式输入包含中文字符时,只支持UTF-8 的编码环境。
  • 出于安全考虑,将包含PASSWORD、IDENTIFIED敏感词的记录识别为敏感信息,不会记录到历史信息中,即不能通过上下翻回显。
  • 变量
    可以使用gsql元命令\set设置一个变量。例如把变量foo的值设置为bar:
    1
    \set foo bar
    
    要引用变量的值,在变量前面加冒号。例如查看变量的值:
    1
    2
    \echo :foo
    bar
    

    这种变量的引用方法适用于规则的SQL语句和元命令。

    在使用命令行参数--dynamic-param(详见表1),或设置特殊变量DYNAMIC_PARAM_ENABLE(详见表2)为true时,可通过执行SQL语句设置变量。变量名为SQL执行结果的列名,也可使用${}方式引用。例如:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    \set DYNAMIC_PARAM_ENABLE true
    SELECT 'Jack' AS "Name";
     Name
    ------
     Jack
    (1 row)
    
    \echo ${Name}
    Jack
    

    上述示例中,通过SELECT语句执行设置Name变量,并在后面使用${}的引用方式获得变量Name的值。示例中通过特殊变量DYNAMIC_PARAM_ENABLE控制这一功能,也可通过命令行参数--dynamic-param控制,如gsql -d postgres -p 25308 --dynamic-param -r。

    • SQL执行失败时,不设置变量。
    • SQL执行结果为空,以列名设置变量,赋值空字符串。
    • SQL执行结果为一条记录,以列名设置变量,赋值对应字符串。
    • SQL执行结果为多条记录,以列名设置变量,使用特定字符串拼接,然后赋值。特定字符串由特殊变量RESULT_DELIMITER(详见表2)控制,默认为“,”。

    执行SQL语句设置变量示例:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    \set DYNAMIC_PARAM_ENABLE true
    CREATE TABLE student (id INT, name VARCHAR(32)) DISTRIBUTE BY HASH(id);
    CREATE TABLE
    INSERT INTO student VALUES (1, 'Jack'), (2, 'Tom'), (3, 'Jerry');
    INSERT 0 3
    --  执行失败时,不设置变量
    SELECT id, name FROM student ORDER BY idi;
    ERROR:  column "idi" does not exist
    LINE 1: SELECT id, name FROM student ORDER BY idi;
                                                  ^
    \echo ${id} ${name}
    ${id} ${name}
    
    --  执行结果为多条记录时,使用特定字符串拼接
    SELECT id, name FROM student ORDER BY id;
     id | name
    ----+-------
      1 | Jack
      2 | Tom
      3 | Jerry
    (3 rows)
    
    \echo ${id} ${name}
    1,2,3 Jack,Tom,Jerry
    
    -- 执行结果为一条记录时
    SELECT id, name FROM student where id = 1;
     id | name
    ----+------
      1 | Jack
    (1 row)
    
    \echo ${id} ${name}
    1 Jack
    
    -- 执行结果为空时,赋值空字符串
    SELECT id, name FROM student where id = 4;
     id | name
    ----+------
    (0 rows)
    
    \echo ${id} ${name}
    
     
    

    gsql预定义了一些特殊变量,同时也规划了变量的取值。为了保证和后续版本最大限度地兼容,请避免以其他目的使用这些变量。所有特殊变量见表2

    • 所有特殊变量都由大写字母、数字和下划线组成。
    • 要查看特殊变量的默认值,请使用元命令\echo :varname(例如\echo :DBNAME)。
    表2 特殊变量设置

    变量

    设置方法

    变量说明

    DBNAME

    \set DBNAME dbname

    当前连接的数据库的名字。每次连接数据库时都会被重新设置。

    ECHO

    \set ECHO all | queries
    • 如果设置为all,只显示查询信息。设置为all等效于使用gsql连接数据库时指定-a参数。
    • 如果设置为queries,显示命令行和查询信息。等效于使用gsql连接数据库时指定-e参数。

    ECHO_HIDDEN

    \set ECHO_HIDDEN  on | off | noexec

    当使用元命令查询数据库信息(例如\dg)时,此变量的取值决定了查询的行为:

    • 设置为on,先显示元命令实际调用的查询语句,然后显示查询结果。等效于使用gsql连接数据库时指定-E参数。
    • 设置为off,则只显示查询结果。
    • 设置为noexec,则只显示查询信息,不执行查询操作。

    ENCODING

    \set ENCODING   encoding

    当前客户端的字符集编码。

    FETCH_COUNT

    \set FETCH_COUNT variable
    • 如果该变量的值为大于0的整数,假设为n,则执行SELECT语句时每次从结果集中取n行到缓存并显示到屏幕。
    • 如果不设置此变量,或设置的值小于等于0,则执行SELECT语句时一次性把结果都取到缓存。
    说明:

    设置合理的变量值,将减少内存使用量。一般来说,设为100到1000之间的值比较合理。

    HISTCONTROL

    \set HISTCONTROL  ignorespace | ignoredups | ignoreboth | none
    • ignorespace:以空格开始的行将不会写入历史列表。
    • ignoredups:与以前历史记录里匹配的行不会写入历史记录。
    • ignoreboth、none或者其他值:所有以交互模式读入的行都被保存到历史列表。
      说明:

      none表示不设置HISTCONTROL。

    HISTFILE

    \set HISTFILE filename

    此文件用于存储历史名列表。缺省值是~/.bash_history。

    HISTSIZE

    \set HISTSIZE size

    保存在历史命令里命令的个数。缺省值是500。

    HOST

    \set HOST hostname

    已连接的数据库主机名称。

    IGNOREEOF

    \set IGNOREEOF variable
    • 若设置此变量为数值,假设为10,则在gsql中输入的前9次EOF字符(通常是Ctrl+C组合键)都会被忽略,在第10次按Ctrl+C才能退出gsql程序。
    • 若设置此变量为非数值,则缺省为10。
    • 若删除此变量,则向交互的gsql会话发送一个EOF终止应用。

    LASTOID

    \set LASTOID oid

    最后影响的oid值,即为从一条INSERT或lo_import命令返回的值。此变量只保证在下一条SQL语句的结果显示之前有效。

    ON_ERROR_ROLLBACK

    \set  ON_ERROR_ROLLBACK  on | interactive | off
    • 如果是on,当一个事务块里的语句产生错误的时候,这个错误将被忽略而事务继续。
    • 如果是interactive,这样的错误只是在交互的会话里忽略。
    • 如果是off(缺省),事务块里一个语句生成的错误将会回滚整个事务。on_error_rollback-on模式是通过在一个事务块的每个命令前隐含地发出一个SAVEPOINT的方式工作的,在发生错误的时候回滚到该事务块。

    ON_ERROR_STOP

    \set ON_ERROR_STOP on | off
    • on:命令执行错误时会立即停止,在交互模式下,gsql会立即返回已执行命令的结果。
    • off(缺省):命令执行错误时将会跳过错误继续执行。

    PORT

    \set PORT port

    正连接数据库的端口号。

    USER

    \set USER username

    当前用于连接的数据库用户。

    VERBOSITY

    \set VERBOSITY   terse | default | verbose

    这个选项可以设置为值terse、default、verbose之一以控制错误报告的冗余行。

    • terse:仅返回严重且主要的错误文本以及文本位置(一般适合于单行错误信息)。
    • default:返回严重且主要的错误文本及其位置,还包括详细的错误细节、错误提示(可能会跨越多行)。
    • verbose:返回所有的错误信息。

    VAR_NOT_FOUND

    \set VAR_NOT_FOUND default | null | error

    可以设置为default、null、error之一以控制引用变量不存在时的处理方式。

    • default:不做变量替换,保持原有字符串。
    • null:将原有字符串替换为空字符串。
    • error:输出报错信息,保持原有字符串。

    VAR_MAX_LENGTH

    \set VAR_MAX_LENGTH variable

    用于控制变量值的长度,默认为4096。如果变量值的长度超过该值,变量值会被截断,并输出告警信息。

    ERROR_LEVEL

    \set ERROR_LEVEL transaction | statement

    表示ERROR标识成功或者失败类型,取值为transaction或statement,默认为transaction。

    • statement:ERROR记录上一条SQL语句是否执行成功。
    • transaction:ERROR记录上一条SQL语句是否执行成功,或上一个事务内部执行是否出错。

    ERROR

    \set ERROR true | false

    表示上一条SQL语句执行成功或失败,或上一个事务内部执行是否出错,成功取值false,失败取值true,默认为false。执行SQL语句更新,不建议手动设置。

    LAST_ERROR_SQLSTATE

    \set LAST_ERROR_SQLSTATE  state

    表示上一条执行失败的SQL语句的错误状态码,默认为‘00000’。执行SQL语句更新,不建议手动设置。

    LAST_ERROR_MESSAGE

    \set LAST_ERROR_MESSAGE message

    表示上一条执行失败的SQL语句的错误信息,默认为空字符串。执行SQL语句更新,不建议手动设置。

    ROW_COUNT

    \set ROW_COUNT count
    • ERROR_LEVEL为statement时,表示上一条SQL语句执行返回的行数或受影响的行数。
    • ERROR_LEVEL为transaction时,如果事务结束时内部有错,表示事务内最后一个SQL语句执行返回的行数或受影响的行数,否则表示上一条SQL语句执行返回的行数或受影响的行数。

    如果SQL语句执行失败设置为0,默认为0。执行SQL语句更新,不建议手动设置。

    SQLSTATE

    \set SQLSTATE state
    • ERROR_LEVEL为statement时,表示上一条SQL语句执行的状态码。
    • ERROR_LEVEL为transaction时,如果事务结束时内部有错,表示事务内最后一个SQL语句执行的状态码,否则表示上一条SQL语句执行的状态码。

    默认为‘00000’。执行SQL语句更新,不建议手动设置。

    LAST_SYS_CODE

    \set LAST_SYS_CODE code

    表示上一条系统命令执行的返回值,默认为0。使用元命令\!调用系统命令更新,不建议手动设置。

    DYNAMIC_PARAM_ENABLE

    \set DYNAMIC_PARAM_ENABLE true | false

    用于控制执行SQL语句生成变量和${}变量引用方式,默认为fasle。

    • true:执行SQL语句生成变量,支持${}变量引用方式。
    • false:执行SQL语句不生成变量,不支持${}变量引用方式。

    RESULT_DELIMITER

    \set RESULT_DELIMITER delimiter

    执行SQL语句生成变量时,多条记录之间的拼接使用该参数控制,默认为“,”。

    COMPARE_STRATEGY

    \set COMPARE_STRATEGY  default | natural | equal

    用于控制\if表达式中大小比较的策略,默认为default。

    • default:默认的比较策略,只支持字符串或数字比较,不支持混合比较。单引号内的按照字符串处理,单引号外的按照数字处理。
    • natural:在default的基础上,包含动态变量的按照字符串处理。当比较操作符有一侧是数字,尝试将另一侧转换为数字,然后比较。如果转换失败,报错且比较结果为假。
    • equal:只支持等值比较,所有情况按照字符串比较。

    详细说明和使用示例请参考\if条件块比较规则说明与示例

    COMMAND_ERROR_STOP

    \set COMMAND_ERROR_STOP on | off

    用于控制元命令执行错误时是否报错退出,默认不退出。

    使用示例请参考COMMAND_ERROR_STOP使用示例

    • 特殊变量ERROR_LEVEL和ERROR使用示例:
    当ERROR_LEVEL为statement时,ERROR只记录上一条SQL语句是否执行成功。示例如下,当事务中出现SQL执行报错,事务结束时,ERROR值为false。此时的ERROR只记录上一个SQL语句end是否执行成功。
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    \set ERROR_LEVEL statement
    begin;
    BEGIN
    select 1 as ;
    ERROR:  syntax error at or near ";"
    LINE 1: select 1 as ;
                        ^
    end;
    ROLLBACK
    \echo :ERROR
    false
    

    当ERROR_LEVEL为transaction时,ERROR可以捕获事务内的SQL执行错误。示例如下,事务中出现SQL执行报错,事务结束时,ERROR值为true。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    \set ERROR_LEVEL transaction
    begin;
    BEGIN
    select 1 as ;
    ERROR:  syntax error at or near ";"
    LINE 1: select 1 as ;
                        ^
    end;
    ROLLBACK
    \echo :ERROR
    true
    
    • 特殊变量COMMAND_ERROR_STOP使用示例:

    当COMMAND_ERROR_STOP为on时,元命令执行错误时,报错退出。开启时能有效的识别到元命令的执行错误。

    当COMMAND_ERROR_STOP为off时,元命令执行错误时,打印相关信息不退出,脚本继续执行。

    1
    2
    3
    4
    \set COMMAND_ERROR_STOP on
    \i /home/omm/copy_data.sql
    
    select id, name from student;
    

    如上脚本中COMMAND_ERROR_STOP设置为on,元命令报错之后输出错误信息,脚本不再执行。

    1
    gsql:test.sql:2: /home/omm/copy_data.sql: Not a directory
    

    如果COMMAND_ERROR_STOP设置为off,元命令报错之后输出错误信息,继续执行SELECT语句。

    1
    2
    3
    4
    5
    gsql:test.sql:2: /home/omm/copy_data.sql: Not a directory
     id | name
    ----+------
      1 | Jack
    (1 row)
    
  • SQL代换
    像元命令的参数一样,gsql变量的一个关键特性是可以把gsql变量替换成正规的SQL语句。此外,gsql还提供为变量更换新的别名或其他标识符等功能。使用SQL代换方式替换一个变量的值可在变量前加冒号。例如:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    \set foo 'HR.areaS'
    select * from :foo;
     area_id |       area_name        
    ---------+------------------------
           4 | Iron
           3 | Desert
           1 | Wood
           2 | Lake
    (4 rows)
    

    执行以上命令,将会查询HR.areaS表。

    变量的值是逐字复制的,甚至可以包含不对称的引号或反斜杠命令。所以必须保证输入的内容有意义。

  • 提示符

    通过表3的三个变量可以设置gsql的提示符,这些变量是由字符和特殊的转义字符所组成。

    表3 提示符变量

    变量

    描述

    示例

    PROMPT1

    gsql请求一个新命令时使用的正常提示符。

    PROMPT1的默认值为:

    %/%R%#

    使用变量PROMPT1切换提示符:

    • 提示符变为[local]:
      1
      2
      \set PROMPT1 %M
      [local:/tmp/gaussdba_mppdb]
      
    • 提示符变为name:
      1
      2
      \set PROMPT1 name
      name
      
    • 提示符变为=:
      1
      2
      \set PROMPT1 %R
      =
      

    PROMPT2

    在一个命令输入期待更多输入时(例如,查询没有用一个分号结束或者引号不完整)显示的提示符。

    使用变量PROMPT2显示提示符:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    \set PROMPT2 TEST
    select * from HR.areaS TEST;
     area_id |       area_name    
    ---------+--------------------
           1 | Wood
           2 | Lake
           4 | Iron
           3 | Desert
    (4 rows))
    

    PROMPT3

    当执行COPY命令,并期望在终端输入数据时(例如,COPY FROM STDIN),显示提示符。

    使用变量PROMPT3显示COPY提示符:

    1
    2
    3
    4
    5
    6
    7
    \set PROMPT3 '>>>>'
    copy HR.areaS from STDIN;
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >>>>1 aa
    >>>>2 bb
    >>>>\.
    

    提示符变量的值是按实际字符显示的,但是,当设置提示符的命令中出现“%”时,变量的值根据“%”后的字符,替换为已定义的内容,已定义的提示符请参见表4

    表4 已定义的替换

    符号

    符号说明

    %M

    主机的全名(包含域名),若连接是通过Unix域套接字进行的,则全名为[local],若Unix域套接字不是编译的缺省位置,就是[local:/dir/name]。

    %m

    主机名删去第一个点后面的部分。若通过Unix域套接字连接,则为[local]。

    %>

    主机正在侦听的端口号。

    %n

    数据库会话的用户名。

    %/

    当前数据库名称。

    %~

    类似 %/,如果数据库是缺省数据库时输出的是波浪线~。

    %#

    如果会话用户是数据库系统管理员,使用#,否则用>。

    %R

    • 对于PROMPT1通常是“=”,如果是单行模式则是“^”,如果会话与数据库断开(如果\connect失败可能发生)则是“!”。
    • 对于PROMPT2该序列被“ -”、“ *”、单引号、双引号或“$”(取决于gsql是否等待更多的输入:查询没有终止、正在一个 /* ... */ 注释里、正在引号或者美元符扩展里)代替。

    %x

    事务状态:

    • 如果不在事务块里,则是一个空字符串。
    • 如果在事务块里,则是“*”。
    • 如果在一个失败的事务块里则是“!”。
    • 如果无法判断事务状态时为“?”(比如没有连接)。

    %digits

    指定字节值的字符将被替换到该位置。

    %:name

    gsql变量“name”的值。

    %command

    command的输出,类似于使用“^”替换。

    %[ . . . %]

    提示可以包含终端控制字符,这些字符可以改变颜色、背景、提示文本的风格、终端窗口的标题。例如,

    potgres=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%#'

    这个句式的结果是在VT100兼容的可显示彩色的终端上的一个宽体(1;)黑底黄字(33;40)。

环境变量

表5 与gsql相关的环境变量

名称

描述

COLUMNS

如果\set columns为0,则由此参数控制wrapped格式的宽度。这个宽度用于决定在自动扩展的模式下,是否要把宽输出模式变成竖线的格式。

PAGER

如果查询结果无法在一页显示,它们就会被重定向到这个命令。可以用\pset命令关闭分页器。典型的是用命令more或less来实现逐页查看。缺省值是平台相关的。

说明:

less的文本显示,受系统环境变量LC_CTYPE影响。

PSQL_EDITOR

\e和\ef命令使用环境变量指定的编辑器。变量是按照列出的先后顺序检查的。在Unix系统上默认的编辑工具是vi。

EDITOR

VISUAL

PSQL_EDITOR_LINENUMBER_ARG

当\e和\ef带上一行数字参数使用时,这个变量指定的命令行参数用于向编辑器传递起始行数。像Emacs或vi这样的编辑器,这只是个加号。如果选项和行号之间需要空白,在变量的值后加一个空格。例如:
PSQL_EDITOR_LINENUMBER_ARG = '+'  
PSQL_EDITOR_LINENUMBER_ARG='--line '

Unix系统默认的是+。

PSQLRC

用户的.gsqlrc文件的交互位置。

SHELL

使用\!命令跟shell执行的命令是一样的效果。

TMPDIR

存储临时文件的目录。缺省是/tmp。