学步园

 

Session System Variables

Several system variables exist only as session variables. These cannot be set
at server startup but can be assigned values at runtime using the SET statement (except for those that are read only).
Most of them are not displayed by SHOW
VARIABLES, but you can obtain their values using SELECT. This section describes the session system
variables. For information about setting or displaying their values, see Section 5.1.5, “Using System
Variables”. For example:

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

The lettercase of these variables does not matter.

The following table lists the system variables that have only session scope:

Table 5.3. mysqld Session System Variable
Summary

Name	Cmd-Line	Option file	System Var	Dynamic
autocommit			Yes	Yes
big-tables	Yes	Yes
- Variable: big_tables			Yes	Yes
error_count			Yes	No
foreign_key_checks			Yes	Yes
identity			Yes	Yes
insert_id			Yes	Yes
last_insert_id			Yes	Yes
ndb_table_no_logging			Yes	Yes
ndb_table_temporary			Yes	Yes
profiling			Yes	Yes
rand_seed1			Yes	Yes
rand_seed2			Yes	Yes
sql_auto_is_null			Yes	Yes
sql_big_selects			Yes	Yes
sql_big_tables			Yes	Yes
sql_buffer_result			Yes	Yes
sql_log_bin			Yes	Yes
sql_log_off			Yes	Yes
sql_log_update			Yes	Yes
sql_notes			Yes	Yes
sql_quote_show_create			Yes	Yes
sql_safe_updates			Yes	Yes
sql_warnings			Yes	Yes
timestamp			Yes	Yes
transaction_allow_batching			Yes	Yes
unique_checks			Yes	Yes
warning_count			Yes	No

• autocommit

  The autocommit mode. If set to 1, all changes to a table take effect
  immediately. If set to 0, you must use COMMIT to accept a
  transaction or ROLLBACK to cancel it.
  By default, client connections begin with autocommit set
  to 1. If you change autocommit mode from 0 to 1, MySQL performs an
  automatic COMMIT of any open
  transaction. Another way to begin a transaction is to use a START TRANSACTION or
  BEGIN statement. See
  Section 12.4.1, “START
TRANSACTION, COMMIT, and ROLLBACK Syntax”.

• big_tables

  If set to 1, all temporary tables are stored on disk
  rather than in memory. This is a little slower, but the error The table tbl_name is
full does not occur for SELECT operations that
  require a large temporary table. The default value for a new connection is 0
  (use in-memory temporary tables). Normally, you should never need to set this
  variable, because in-memory tables are automatically converted to disk-based
  tables as required.

  Note

  This variable was formerly named sql_big_tables.

• error_count

  The number of errors that resulted from the last statement that generated
  messages. This variable is read only. See Section 12.5.5.18, “SHOW
ERRORS Syntax”.

• foreign_key_checks

  If set to 1 (the default), foreign key constraints for InnoDB tables are checked. If set to 0, they are ignored.
  Disabling foreign key checking can be useful for reloading InnoDB tables in an order different from that required by
  their parent/child relationships. See Section 13.6.4.4, “FOREIGN KEY Constraints”.

  Setting foreign_key_checks to 0 also affects data definition
  statements: DROP SCHEMA drops
  a schema even if it contains tables that have foreign keys that are referred to
  by tables outside the schema, and DROP TABLE drops
  tables that have foreign keys that are referred to by other tables.

  Note

  Setting foreign_key_checks to 1 does not trigger a scan of the
  existing table data. Therefore, rows added to the table while foreign_key_checks = 0 will not be verified for
  consistency.

• identity

  This variable is a synonym for the last_insert_id variable. It exists for compatibility
  with other database systems. You can read its value with SELECT @@identity, and set it using SET
identity.

• insert_id

  The value to be used by the following INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT value. This is mainly used with the binary
  log.

• last_insert_id

  The value to be returned from LAST_INSERT_ID(). This is stored in the binary log when
  you use LAST_INSERT_ID() in a statement that updates a table.
  Setting this variable does not update the value returned by the mysql_insert_id() C API function.

• profiling

  If set to 0 (the default), statement profiling is disabled. If set to 1,
  statement profiling is enabled and the SHOW PROFILES and SHOW PROFILE statements provide access to profiling
  information. See Section 12.5.5.33, “SHOW
PROFILES Syntax”. This variable was added in MySQL 5.1.24.

• profiling_history_size

  The number of statements for which to maintain profiling information if profiling is
  enabled. The default value is 15. The maximum value is 100. Setting the value to
  0 effectively disables profiling. See Section 12.5.5.33, “SHOW
PROFILES Syntax”. This variable was added in MySQL 5.1.24.

• rand_seed1

  The rand_seed1 and rand_seed2
  variables exist as session variables only, and can be set but not read.
  Beginning with MySQL 5.1.18, the variables — but not their values — are shown in
  the output of SHOW
VARIABLES.

  The purpose of these variables is to support replication of the RAND()
  function. For statements that invoke RAND(), the master passes two values to the slave,
  where they are used to seed the random number generator. The slave uses these
  values to set the session variables rand_seed1 and rand_seed2 so
  that RAND()
  on the slave generates the same value as on the master.

• rand_seed2

  See the description for rand_seed1.

• sql_auto_is_null

  If set to 1 (the default), you can find the last inserted row for a table
  that contains an AUTO_INCREMENT column by using the
  following construct:

  WHERE auto_increment_column IS NULL
  

  This behavior is used by some ODBC programs, such as Access.

• sql_big_selects

  If set to 0, MySQL aborts SELECT statements that
  are likely to take a very long time to execute (that is, statements for which
  the optimizer estimates that the number of examined rows exceeds the value of max_join_size). This is useful when an inadvisable
  WHERE statement has been issued. The default value
  for a new connection is 1, which allows all SELECT statements.

  If you set the max_join_size system variable to a value other than
  DEFAULT, sql_big_selects is set to 0.

• sql_buffer_result

  If set to 1, sql_buffer_result forces results from SELECT statements to be put into temporary tables. This
  helps MySQL free the table locks early and can be beneficial in cases where it
  takes a long time to send results to the client. The default value is 0.

• sql_log_bin

  If set to 0, no logging is done to the binary log for the client. The client
  must have the SUPER privilege to set this option. The default value
  is 1.

• sql_log_off

  If set to 1, no logging is done to the general query log for this client. The
  client must have the SUPER privilege to set this option. The default value
  is 0.

• sql_log_update

  This variable is deprecated, and is mapped to sql_log_bin.

• sql_notes

  If set to 1 (the default), warnings of Note level
  are recorded. If set to 0, Note warnings are
  suppressed. mysqldump includes output to set this variable
  to 0 so that reloading the dump file does not produce warnings for events that
  do not affect the integrity of the reload operation.

• sql_quote_show_create

  If set to 1 (the default), the server quotes identifiers for SHOW CREATE
TABLE and SHOW CREATE
DATABASE statements. If set to 0, quoting is disabled. This option is
  enabled by default so that replication works for identifiers that require
  quoting. See Section 12.5.5.12, “SHOW CREATE TABLE Syntax”, and Section 12.5.5.8, “SHOW CREATE DATABASE Syntax”.

• sql_safe_updates

  If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause.
  This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and
  that would probably change or delete a large number of rows. The default value
  is 0.

• sql_warnings

  This variable controls whether single-row INSERT statements
  produce an information string if warnings occur. The default is 0. Set the value
  to 1 to produce an information string.

• timestamp = {timestamp_value | DEFAULT}

  Set the time for this client. This is used to get the original timestamp if
  you use the binary log to restore rows. timestamp_value should be a Unix epoch
  timestamp, not a MySQL timestamp.

  SET timestamp affects the value returned by NOW() but not by
  SYSDATE(). This means that timestamp settings in the
  binary log have no effect on invocations of SYSDATE().
  The server can be started with the --sysdate-is-now option to cause SYSDATE() to
  be an alias for NOW(), in which case SET
timestamp affects both functions.

• unique_checks

  If set to 1 (the default), uniqueness checks for secondary indexes in InnoDB tables are performed. If set to 0, storage engines
  are allowed to assume that duplicate keys are not present in input data. If you
  know for certain that your data does not contain uniqueness violations, you can
  set this to 0 to speed up large table imports to InnoDB.

  Note that setting this variable to 0 does not require storage engines to ignore duplicate keys.
  An engine is still allowed to check for them and issue duplicate-key errors if
  it detects them.

• warning_count

  The number of errors, warnings, and notes that resulted from the last
  statement that generated messages. This variable is read only. See Section 12.5.5.42, “SHOW
WARNINGS Syntax”.