The following list describes system variables that exist in session-only form. That is, each client gets its own set of these variables when it connects to the server, but there are no corresponding global variables. Setting any of these variables affects server operation only for the current client.
Session variable names are not case sensitive.
autocommit
The autocommit level for transaction processing. By default, this variable is set to 1, so autocommit mode is enabled and statements take effect immediately; essentially, each statement is its own transaction. Setting the value to 0 disables autocommit mode so that subsequent statements do not take effect until a commit is performed (either with a COMMIT statement, or by setting autocommit to 1). Statements in the transaction may be canceled with ROLLBACK if a commit has not occurred. Setting autocommit to 1 re-enables autocommit mode (and implicitly commits the pending transaction, if any).
big_tables
All internal temporary tables are stored on disk rather than in memory if this is variable is set to 1. Performance is slower, but SELECT statements that require large temporary tables will not generate "table full" errors. The default is 0 (hold temporary tables in memory). Normally you need not set this variable for MySQL 3.23 and up.
sql_big_tables is a deprecated synonym for big_tables.
error_count
This is a read-only variable that indicates the number of errors generated by the last statement that can generate errors.
foreign_key_checks
Setting this variable to 0 or 1 disables or enables foreign key checking for InnoDB tables. The default is to perform checking. Disabling key checks can be useful, for example, when restoring a dump file that creates and loads tables in a different order than that required by their foreign key relationships. You can re-enable key checking after loading the tables.
identity
This is a synonym for the last_insert_id session variable.
insert_id
Setting this variable specifies the value to be used by the next INSERT statement when inserting an AUTO_INCREMENT column. This is used for binary log processing.
last_insert_id
Setting this variable specifies the value to be returned by LAST_INSERT_ID(). This is used for binary log processing.
sql_auto_is_null
If this is set to 1, the most recently generated AUTO_INCREMENT value can be selected using a WHERE clause of the form WHERE col_name IS NULL, where col_name is the name of the AUTO_INCREMENT column. This feature is used by some ODBC programs such as Access. To disable it, set the variable to 0. The default is 1.
sql_big_selects
The server uses this variable in conjunction with the max_join_size system variable as follows:
If sql_big_selects is set to 1 (the default), the server allows queries that return result sets of any size.
If sql_big_selects is set to 0, the server disallows queries that are likely to return a large number of rows. In this case, the value of max_join_size is used when executing a join: The server makes an estimate of the number of row combinations it will need to examine, and if that value exceeds the value of max_join_size, the server returns an error rather than executing the query.
Setting max_join_size to a value other than DEFAULT automatically sets sql_big_selects to 0.
sql_buffer_result
Setting this variable to 1 causes the server to use internal temporary tables to hold results from SELECT statements. The effect is that the server can more quickly release locks held on the tables from which the results are produced. The default is 0.
sql_log_bin
Setting this variable to 0 or 1 disables or enables binary logging for the current client connection. The client must have the SUPER privilege for this statement to have any effect. This variable has no effect if the server's binary log is not enabled.
sql_log_off
Setting this variable to 0 or 1 enables or disables statement logging to the general query log for the current client connection. The client must have the SUPER privilege for this statement to have any effect. This variable has no effect if the server's general log is not enabled.
sql_log_update
Before MySQL 5.0, if this variable is set to 1, the current client's statements are logged in the update log file. If set to 0, logging for the client is enabled. The client must have the SUPER privilege for this statement to have any effect.
As of MySQL 5.0, this variable is obsolete because the update log is removed.
sql_quote_show_create
This variable controls whether to quote identifiers (database, table, column, and index names) in the output from SHOW CREATE TABLE statements, and also in SHOW CREATE DATABASE statements as of MySQL 4.1.2. The default is 1 (use quoting). Turning quoting off by setting the variable to 0 may be useful when producing CREATE TABLE statements for use with other database servers, or with MySQL servers older than version 3.23.6 that do not understand backtick quoting. However, if you turn quoting off, you should make sure that your tables do not use names that are reserved words or that contain special characters.
Identifiers are quoted with backtick ('`') characters if the ANSI_QUOTES SQL mode is disabled, and with double quote ('"') characters if it is enabled.
sql_safe_updates
If this variable is set to 1, the server allows UPDATE and DELETE statements only if the records to be modified are identified by key values or if a LIMIT clause is used. The default of 0 places no such restriction.
sql_select_limit
Specifies the maximum number of records to return from a SELECT statement. The presence of an explicit LIMIT clause in a statement takes precedence over this variable. The default value is "no limit." A value of DEFAULT restores this default if you have changed it.
This variable has no effect within stored routines, or for SELECT operations that do not return rows to the client (such as subqueries, INSERT INTO … SELECT, and CREATE TABLE … SELECT).
sql_warnings
If set to 1, MySQL reports warning counts even for single-row inserts. The default is 0, so warning counts normally are reported only for INSERT statements that insert multiple rows.
timestamp
Setting this variable specifies a TIMESTAMP value for the current connection. This is used for binary log processing.
unique_checks
Setting this variable to 0 or 1 disables or enables uniqueness checks for secondary indexes in InnoDB tables. Disabling these checks can increase performance when importing data into InnoDB tables, but this should not be done unless you know that data values do not violate uniqueness requirements.
warning_count
This is a read-only variable that indicates the number of errors, warnings, and notes generated by the last statement that can generate such messages.
