MySQL: Command Options, configuration options, (global, session) system variables, status variables: (session only) System Variables

 

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 usingSetStatement (Statement t for those that are read only ).
Most of them are not displayedShow
Variables, But you can obtain their values usingSelect. 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.MysqldSession 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, and
  Rollback Syntax "href =" ch12s04.html # commit "> Commit To accept
  Transaction or commit, and
  Rollback Syntax "href =" ch12s04.html # commit "> Rollback To cancel it.
  By default, client connections begin Autocommit Set
  To 1. If you change Autocommit Mode from 0 to 1, MySQL performs
  Automatic commit, and
  Rollback Syntax "href =" ch12s04.html # commit "> Commit Of any open
  Transaction. Another way to begin a transaction is to use a commit, and
  Rollback Syntax "href =" ch12s04.html # commit "> Start transaction Or
  Commit, and
  Rollback Syntax "href =" ch12s04.html # commit "> Begin Statement. See
  Commit, and
  Rollback Syntax "href =" ch12s04.html # commit "> 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 errorThe tableTbl_nameIs
FullDoes not occurSelectOperations that
  Require a large temporary table. The default value for a new connection is 0
  (Use in-memory temporary tables). Normally, you shoshould never need to set this
  Variable, because in-memory tables are automatically converted to disk-based
  Tables as required.

  Note

  This variable was formerly namedSQL _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
ErrorsSyntax ".

• Foreign_key_checks

  If set to 1 (the default), foreign key constraintsInnoDBTables are checked. If set to 0, they are ignored.
  Disabling foreign key checking can be useful for reloadingInnoDBTables in an order different from that required
  Their parent/child relationships. See section 13.6.4.4,"Foreign keyConstraints ".

  SettingForeign_key_checksTo 0 also affects data definition
  Statements:Drop SchemaDrops
  A schema even if it contains tables that have foreign keys that are referred
  By tables outside the schema, andDrop tableDrops
  Tables that have foreign keys that are referred to by other tables.

  Note

  SettingForeign_key_checksTo 1 does not trigger a scan of
  Existing table data. Therefore, rows added to the table whileForeign_key_checks = 0Will not be verified
  Consistency.

• Identity

  This variable is a synonym forLast_insert_idVariable. It exists for compatibility
  With other database systems. You can read its valueSelect @ identity, And set it usingSet
Identity.

• Insert_id

  The value to be used by the followingInsertOrALTER TABLEStatement when insertingAuto_incrementValue. This is mainly used with the binary
  Log.

• Last_insert_id

  The value to be returned fromLast_insert_id (). This is stored in the binary log when
  You useLast_insert_id ()In a statement that updates a table.
  Setting this variable does not update the value returned byMysql_insert_id ()C API function.

• Profiling

  If set to 0 (the default), statement profiling is disabled. If set to 1,
  Statement profiling is enabled andShow profilesAndShow ProfileStatements provide access to profiling
  Information. See section 12.5.5.33,"Show
ProfilesSyntax ". 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 partition tively 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 descriptionRand_seed1.

• SQL _auto_is_null

  If set to 1 (the default), you can find the last inserted row for a table
  That containsAuto_incrementColumn by using
  Following construct:

  WhereAuto_increment_columnIs 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_resultForces results fromSelectStatements 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 haveSuperPrivilege 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.
  Client must haveSuperPrivilege to set this option. The default value
  Is 0.

• SQL _log_update

  This variable is deprecated, and is mappedSQL _log_bin.

• SQL _notes

  If set to 1 (the default), warningsNoteLevel
  Are recorded. If set to 0,NoteWarnings are
  Suppressed.MysqldumpExcept des 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 abortsUpdateOrDeleteStatements that do not use a key inWhereClause orLimitClause.
  This makes it possible to catchUpdateOrDeleteStatements where keys are not used properly and
  That wowould probably change or delete a large number of rows. The default value
  Is 0.

• SQL _warnings

  This variable controls whether single-rowInsertStatements
  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 shocould 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 saved med. 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 ".