Mysql：命令选项、配置选项、（全局、会话）系统变量、状态变量：（仅会话）系统变量

 

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”.