MySQL Reference Manual :: 13.5.3 SET Syntax

Search the MySQL manual:

MySQL Reference Manual

Additional languages

Additional formats

Previous / Next / Up / Table of Contents

13.5.3. `SET` Syntax

SET variable_assignment [, variable_assignment] ...

variable_assignment:
      user_var_name = expr
    | [GLOBAL | SESSION] system_var_name = expr
    | @@[global. | session.]system_var_name = expr

SET sets different types of variables that affect the operation of the server or your client. It can be used to assign values to user variables or system variables.

The SET PASSWORD statement for assigning account passwords is described in See Section 13.5.1.5, “SET PASSWORD Syntax”.

In MySQL 4.0.3, we added the GLOBAL and SESSION options and allowed most important system variables to be changed dynamically at runtime. The system variables that you can set at runtime are described in Section 5.3.3.1, “Dynamic System Variables”.

In older versions of MySQL, SET OPTION is used instead of SET, but this is deprecated; just leave out the word OPTION.

The following example show the different syntaxes you can use to set variables.

A user variable is written as @var_name and can be set as follows:

SET @var_name = expr;

Further information about user variables is given in Section 9.3, “User Variables”.

System variables can be referred to in SET statements as var_name. The name optionally can be preceded by GLOBAL or @@global. to indicate explicitly that the variable is a global variable, or by SESSION, @@session., or @@ to indicate that it is a session variable. LOCAL and @@local. are synonyms for SESSION and @@session.. If no modifier is present, SET sets the session variable.

The @@var_name syntax for system variables is supported to make MySQL syntax compatible with some other database systems.

If you set several system variables in the same statement, the last used GLOBAL or SESSION option is used for variables that have no mode specified.

SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;

If you set a system variable using SESSION (the default), the value remains in effect until the current session ends or until you set the variable to a different value. If you set a system variable using GLOBAL, which requires the SUPER privilege, the value is remembered and used for new connections until the server restarts. If you want to make a variable setting permanent, you should put it in an option file. See Section 4.3.2, “Using Option Files”.

To prevent incorrect usage, MySQL produces an error if you use SET GLOBAL with a variable that can only be used with SET SESSION or if you do not specify GLOBAL when setting a global variable.

If you want to set a SESSION variable to the GLOBAL value or a GLOBAL value to the compiled-in MySQL default value, you can set it to DEFAULT. For example, the following two statements are identical in setting the session value of max_join_size to the global value:

SET max_join_size=DEFAULT;
SET @@session.max_join_size=@@global.max_join_size;

You can get a list of most system variables with SHOW VARIABLES. See Section 13.5.4.19, “SHOW VARIABLES Syntax”. To get a specific variable name or list of names that match a pattern, use a LIKE clause:

SHOW VARIABLES LIKE 'max_join_size';
SHOW GLOBAL VARIABLES LIKE 'max_join_size';

You can also get the value for a specific value by using the @@[global.|local.]var_name syntax with SELECT:

SELECT @@max_join_size, @@global.max_join_size;

When you retrieve a variable with SELECT @@var_name (that is, you do not specify global., session., or local.), MySQL returns the SESSION value if it exists and the GLOBAL value otherwise.

The following list describes variables that have non-standard syntax or that are not described in the list of system variables that is found in Section 5.3.3, “Server System Variables”. Although these variables are not displayed by SHOW VARIABLES, you can obtain their values with SELECT (with the exception of CHARACTER SET and SET NAMES). For example:

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

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. 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). As of MySQL 4.0, you should normally never need to set this variable, because MySQL automatically converts in-memory tables to disk-based tables as necessary. This variable previously was named SQL_BIG_TABLES.
CHARACTER SET {charset_name | DEFAULT}

This maps all strings from and to the client with the given mapping. Before MySQL 4.1, the only allowable value for charset_name is cp1251_koi8, but you can add new mappings by editing the sql/convert.cc file in the MySQL source distribution. As of MySQL 4.1.1, 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.

The default mapping can be restored by using a value of DEFAULT.

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 than that required by their parent/child relationships. This variable was added in MySQL 3.23.52. See Section 15.7.4, “FOREIGN KEY Constraints”.
IDENTITY = value

The variable is a synonym for the LAST_INSERT_ID variable. It exists for compatibility with other databases. As of MySQL 3.23.25, you can read its value with SELECT @@IDENTITY. As of MySQL 4.0.3, you can also set its value with 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' | 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 default mapping can be restored by using a value of DEFAULT.

Note that the syntax for SET NAMES differs from that for setting most other options. This statement is available as of MySQL 4.1.0.
SQL_NOTES = {0 | 1}

When set to 1 (the default), warnings of Note level are recorded. When 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 4.1.11/5.0.3.
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_AUTO_IS_NULL was added in MySQL 3.23.52.
SQL_BIG_SELECTS = {0 | 1}

If set to 0, MySQL aborts SELECT statements that probably take a very long time (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}

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. This variable was added in MySQL 3.23.13.
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. This variable was added in MySQL 3.23.16.
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.
SQL_LOG_UPDATE = {0 | 1}

If set to 0, no logging is done to the update log for the client. The client must have the SUPER privilege to set this option. This variable was added in MySQL 3.22.5. Starting from MySQL 5.0.0, it is deprecated and is mapped to SQL_LOG_BIN (see Section D.1.11, “Changes in release 5.0.0 (22 Dec 2003: Alpha)”).
SQL_QUOTE_SHOW_CREATE = {0 | 1}

If set to 1, SHOW CREATE TABLE quotes table and column names. If set to 0, quoting is disabled. This option is enabled by default so that replication works for tables with table and column names that require quoting. This variable was added in MySQL 3.23.26. Section 13.5.4.5, “SHOW CREATE TABLE 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. This variable was added in MySQL 3.22.32.
SQL_SELECT_LIMIT = {value | DEFAULT}

The maximum number of records 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. This variable was added in MySQL 3.22.11.
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.
UNIQUE_CHECKS = {0 | 1}

If set to 1 (the default), uniqueness checks for secondary indexes in InnoDB tables are performed. If set to 0, uniqueness checks are not done for index entries inserted into InnoDB's insert buffer. 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. This variable was added in MySQL 3.23.52.

Previous / Next / Up / Table of Contents

User Comments

Warning: query failed: Unknown column 'user.firstname' in 'field list' in /data0/sites/live/web-main/lib/mysql-cxn.php on line 69

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /data0/sites/live/web-main/lib/docbook.php on line 245

Add your own comment.

Top / Previous / Next / Up / Table of Contents