SET PASSWORD assigns account passwords. See Section 13.5.1.6, “SET PASSWORD Syntax”.

SET TRANSACTION ISOLATION LEVEL sets the isolation level for transaction processing. See Section 13.4.6, “SET TRANSACTION Syntax”.

SET is used within stored routines to assign values to local routine variables. See Section 17.2.7.2, “Variable SET Statement”.

To indicate explicitly that a variable is a global variable, precede its name by GLOBAL or @@global.. The SUPER privilege is required to set global variables.

To indicate explicitly that a variable is a session variable, precede its name by SESSION, @@session., or @@. Setting a session variable requires no special privilege, but a client can change only its own session variables, not those of any other client.

LOCAL and @@local. are synonyms for SESSION and @@session..

If no modifier is present, SET changes the session variable.

AUTOCOMMIT = {0 | 1}

Set the autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0 you have to 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 13.4.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.

BIG_TABLES = {0 | 1}

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

CHARACTER SET {charset_name | DEFAULT}

This maps all strings from and to the client with the given mapping. You can add new mappings by editing sql/convert.cc in the MySQL source distribution. SET CHARACTER SET sets three session system variables: character_set_client and character_set_results are set to the given character set, and character_set_connection to the value of character_set_database. See Section 10.4, “Connection Character Sets and Collations”.

The default mapping can be restored by using the value DEFAULT. The default depends on the server configuration.

Note that the syntax for SET CHARACTER SET differs from that for setting most other options.

FOREIGN_KEY_CHECKS = {0 | 1}

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 14.2.6.4, “FOREIGN KEY Constraints”.

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

IDENTITY = value

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 = value

Set 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 = value

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

NAMES {'charset_name' [COLLATE 'collation_name'} | DEFAULT}

SET NAMES sets the three session system variables character_set_client, character_set_connection, and character_set_results to the given character set. Setting character_set_connection to charset_name also sets collation_connection to the default collation for charset_name. The optional COLLATE clause may be used to specify a collation explicitly. See Section 10.4, “Connection Character Sets and Collations”.

The default mapping can be restored by using a value of DEFAULT. The default depends on the server configuration.

Note that the syntax for SET NAMES differs from that for setting most other options.

ONE_SHOT

This option is a modifier, not a variable. It can be used to influence the effect of variables that set the character set, the collation, and the time zone. ONE_SHOT is primarily used for replication purposes: mysqlbinlog uses SET ONE_SHOT to modify temporarily the values of character set, collation, and time zone variables to reflect at rollforward what they were originally. ONE_SHOT is for internal use only and is deprecated for MySQL 5.0 and up.

You cannot use ONE_SHOT with other than the allowed set of variables; if you try, you get an error like this:

mysql> SET ONE_SHOT max_allowed_packet = 1;
ERROR 1382 (HY000): The 'SET ONE_SHOT' syntax is reserved for purposes
internal to the MySQL server

If ONE_SHOT is used with the allowed variables, it changes the variables as requested, but only for the next non-SET statement. After that, the server resets all character set, collation, and time zone-related system variables to their previous values. Example:

mysql> SET ONE_SHOT character_set_connection = latin5;

mysql> SET ONE_SHOT collation_connection = latin5_turkish_ci;

mysql> SHOW VARIABLES LIKE '%_connection';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_connection | latin5            |
| collation_connection     | latin5_turkish_ci |
+--------------------------+-------------------+

mysql> SHOW VARIABLES LIKE '%_connection';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_connection | latin1            |
| collation_connection     | latin1_swedish_ci |
+--------------------------+-------------------+

PROFILING = {0 | 1}

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 13.5.4.22, “SHOW PROFILES and SHOW PROFILE Syntax”. This variable was added in MySQL 5.0.37.

PROFILING_HISTORY_SIZE = value

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 13.5.4.22, “SHOW PROFILES and SHOW PROFILE Syntax”. This variable was added in MySQL 5.0.37.

SQL_AUTO_IS_NULL = {0 | 1}

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 = {0 | 1}

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 = {0 | 1}

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 = {0 | 1}

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 = {0 | 1}

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 = {0 | 1}

This variable is deprecated, and is mapped to SQL_LOG_BIN.

SQL_NOTES = {0 | 1}

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_NOTES was added in MySQL 5.0.3.

SQL_QUOTE_SHOW_CREATE = {0 | 1}

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 13.5.4.6, “SHOW CREATE TABLE Syntax”, and Section 13.5.4.4, “SHOW CREATE DATABASE Syntax”.

SQL_SAFE_UPDATES = {0 | 1}

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_SELECT_LIMIT = {value | DEFAULT}

The maximum number of rows to return from SELECT statements. The default value for a new connection is “unlimited.” If you have changed the limit, the default value can be restored by using a SQL_SELECT_LIMIT value of DEFAULT.

If a SELECT has a LIMIT clause, the LIMIT takes precedence over the value of SQL_SELECT_LIMIT.

SQL_SELECT_LIMIT does not apply to SELECT statements executed within stored routines. It also does not apply to SELECT statements that do not produce a result set to be returned to the client. These include SELECT statements in subqueries, CREATE TABLE ... SELECT, and INSERT INTO ... SELECT.

SQL_WARNINGS = {0 | 1}

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 = {0 | 1}

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.