- 更改运行时参数

句法

放  SYSTEM PROPERTY name=value
SET AUTOCOMMIT { ON | OFF }
SET DELIMITER value
SET ISOLATION LEVEL transaction_isolation_level
SET OUTPUT { VERTICAL | HORIZONTAL }
SET PAGER command
SET QUERY TIMEOUT sec
SET ROLE rolename [ ACTIVE | INACTIVE ]
SET SEMICOLON { ON | OFF }
SET TIMING { ON | OFF | FULL }

描述

Invoke the command to change parameters that affect runtime behavior.

参数

A parameter that you specify with the statement affects the runtime behavior of a database or the current connection to a database. Some parameters apply only when you are using the nuosql command line utility.

设置数据库的参数

以下参数允许您指定适用于数据库所有连接的系统属性:

SYSTEM PROPERTY name=value

Use the command to set system properties defined by NuoDB. See SQL系统属性 for a list of all NuoDB system properties and a description of each. Only users that have been granted the "ADMINISTRATOR" ROLE can modify system property values.

对数据库属性的更改立即对群集产生影响,并且可以’t be rolled back.

设置连接参数

以下参数适用于与数据库的当前连接:

AUTOCOMMIT { ON | OFF }

Toggles AUTOCOMMIT on or off. This controls whether NuoDB automatically commits after every statement execution.

The default behavior is that autocommit is enabled (ON). When autocommit is enabled then each statement causes NuoDB to start a new transaction and automatically commit that transaction after executing that statement. There is no ability to roll back database changes.

如果您的应用程序要求几种更改成功或失败作为组,请执行以下操作之一:

  • 指定 开始交易. NuoDB suspends autocommit behavior during the transaction. The 开始交易 statement lets you override 关于默认事务行为. After you specify a 犯罪 or 回滚 statement, the autocommit setting is again in effect.

  • 指定 放 AUTOCOMMIT OFF. Execution of the next statement implicitly starts a transaction. Such a transaction always uses the default transaction behaviors. Specify a 犯罪 or 回滚 statement to end the transaction.

    In a transaction that you open with 开始交易, you can specify a 放 AUTOCOMMIT statement but it does not affect the current transaction. It takes effect after you end the transaction.

    For example, suppose an application requires that a deposit in one account matches a withdrawal from another account. The application specifies 开始交易 to suspend autocommit behavior, which is enabled. The database changes that occur as a result of these DML statements are committed or rolled back as a group.

    Specification of 放 AUTOCOMMIT OFF can improve performance because there is a cost to starting and committing transactions.

DELIMITER character_string

Configures the character or characters that delimit SQL statements or blocks. The default is a semicolon (;). character_string can be one or more characters representing the new delimiter. For example, any of the following are valid: (/),(//),(\),(\\),(|),(||),(@), etc. A character value should be used that will not be contained in subsequent sql statements.

放 DELIMITER 将语句末尾的指示灯设置为默认分号以外的某些内容。更改分隔符可能对执行商店过程和复杂的触发器是有价值的。看 例4. below.

ISOLATION LEVEL transaction_isolation_level

ISOLATION LEVEL defines how this transaction can view updates performed by other, concurrent transactions. The default is CONSISTENT READ. The ISOLATION LEVEL setting is in effect with the start of the next transaction and stays in effect until the level is reset with another command or the connection closes. See 支持的交易隔离级别 有关Nuodb支持的每个事务隔离级别的描述。

QUERY TIMEOUT sec

Invoke 放 QUERY TIMEOUT to specify the maximum length of time (in seconds) before a query is terminated for taking too long. QUERY TIMEOUT is a per-connection property which is disabled by default. It can be enabled by running the 放 QUERY TIMEOUT command with a non-zero value. A value of 0 is used to disable the timeout. The specified value can be a decimal fraction for granularity smaller than a second.

ROLE rolename [ ACTIVE | INACTIVE ]

A role can be active or inactive. The default is that a role is inactive. See the 授予 statement for information about creating and assigning roles. If you specify 放 ROLE rolename without specifying ACTIVE or INACTIVE then the specified role becomes active.

A role that has been granted to a user is active when the privileges associated with that role are currently available to that user. An inactive role is a role granted to a user but whose privileges are not currently available to that user, but may be used in the future by issuing the 放 ROLE command. Each time the user starts a new session, the role will be inactive and the user must invoke 放 ROLE to make it active again.

虽然SQL标准需要在事务之外执行此语句,但在NOODB数据库中不适用于相同的限制

使用时设置参数 nuosql

When you are using the nuosql command line utility then you can set the following parameters:

OUTPUT { VERTICAL | HORIZONTAL }

配置输出显示方向(参见 例5.). The default is HORIZONTAL. Set the OUTPUT to VERTICAL when you want nuosql to print table rows as one column per line. This is useful for when your query returns many columns or when data in the column contains formatting such as new lines.

PAGER command

Pipes result from SQL queries in nuosql through the specified command. The default is that results go to stdout. The specified command can be one command, for example:

放 PAGER more

指定的命令可以是具有参数的命令字符串,例如:

放 PAGER "less --QUIT-AT-EOF --no-init"

If you specify less, it is recommended that you specify both --QUIT-AT-EOF and --no-init. This prevents the need to explicitly quit at the end of the results. Also, the results do not disappear, which they do when you explicitly quit.

指定的命令必须位于用户身上’s PATH.

The 放 PAGER command is not supported on Windows.

SEMICOLON [ ON | OFF ]

This is specific to the nuosql command line tool. The default is ON. Set the SEMICOLON variable to OFF when you do not want nuosql to require semicolons at the end of commands. If you specify 放 SEMICOLON without specifying ON or OFF then the setting is ON.

TIMING { ON | OFF | FULL }

Turn on or off reporting of elapsed time for each SQL statement to execute. The OFF mode is default. The ON mode reports the elapsed time taken by the nuosql client. The FULL mode reports the elapsed time taken by the nuosql client and additionally for non-DDL SQL statements, reports the elapsed time spent by the server (TE) to process the command.

例子

例1: 放 AUTOCOMMIT
# The first set of statements are committed because AUTOCOMMIT is enabled.

表演 AUTOCOMMIT;
    Autocommit is on

CREATE TABLE t (N int);
 INTO t VALUES (1);
选择 * FROM t;
 N
 --
 1
Example 2: Set AUTOCOMMIT OFF and 回滚.
# Setting AUTOCOMMIT OFF after the first set of statements means that the
# second set of statements are not committed. They can be rolled back.

 AUTOCOMMIT OFF;
 INTO t VALUES (2);
选择 * FROM t;
 N
 --
 1
 2

# 回滚 discards the second insertion.
# There is only one row in the table after ROLLBACK.

回滚;
选择 * FROM t;
 N
 --
 1
Example 3: Explicit 犯罪 with AUTOCOMMIT OFF
# You can explicitly commit execution of a statement when autocommit is
# not enabled. After the 犯罪 statement, there is nothing to roll back.

 INTO t VALUES (2);
犯罪;
选择 * FROM t;
 N
 --
 1
 2

回滚;
选择 * FROM t;
 N
 --
 1
 2
例4.: 放 DELIMITER temporarily to define a function.
DROP FUNCTION IF EXISTS func_is_date;
 DELIMITER @
CREATE FUNCTION func_is_date (i_date string, i_format string )
       RETURNS BOOLEAN
       DETERMINISTIC
       LANGUAGE SQL
       SECURITY INVOKER
AS
   VAR l_out BOOLEAN = 'TRUE';
   VAR l_timestamp TIMESTAMP;
   try
       l_timestamp = (选择 date_from_str(i_date, i_format) FROM DUAL);
   catch(error)
       l_out = 'FALSE';
   end_try;
   RETURN l_out;
END_FUNCTION;
@
 DELIMITER ;
例5.: 放 OUTPUT to change the display direction of the output.
CREATE TABLE tbl_output_display (column1 INTEGER, column2 STRING);
 INTO tbl_output_display VALUES (1,'This is row 1'),(2,'This is row 2');
 OUTPUT HORIZONTAL;
选择 * FROM tbl_output_display;

  COLUMN1     COLUMN2
 -------- -------------
    1     This is row 1
    2     This is row 2


 OUTPUT VERTICAL;
选择 * FROM tbl_output_display;

==================================== Row #1 ====================================
COLUMN1: 1
COLUMN2: This is row 1
==================================== Row #2 ====================================
COLUMN1: 2
COLUMN2: This is row 2
Example 7: 放 TIMING to FULL so that elapsed time will be reported for each statement executed.
 TIMING FULL;
选择 * FROM hockey.scoring LIMIT 3000;

 PLAYERID  YEAR  STINT  TEAMID  POSITION  GAMESPLAYED  GOALS  ASSISTS  PENALTYMINUTES
 --------- ----- ------ ------- --------- ------------ ------ -------- ---------------

 aaltoan01 1997    1      ANA      C            3         0       0            0
 aaltoan01 1998    1      ANA      C           73         3       5           24
...
 bladoto01 1972    1      PHI      D           78        11      31           26
 bladoto01 1973    1      PHI      D           70        12      22           37

Elapsed time 36ms
Server execution time 7ms