Additional languages
« 13.5.2.7 RESTORE TABLE Syntax
13.5.4 SHOW Syntax »
Section Navigation      [Toggle]

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

The SET statement assigns values to different types of variables that affect the operation of the server or your client. Older versions of MySQL employed SET OPTION, but this syntax is deprecated in favor of SET without OPTION.

This section describes use of SET for assigning values to system variables or user variables. For general information about these types of variables, see Section 9.4, “User-Defined Variables”. System variables also can be set at server startup, as described in Section 5.2.4, “Using System Variables”.

Some variants of SET syntax are used in other contexts:

The following discussion shows the different SET syntaxes that you can use to set variables. The examples use the = assignment operator, but the := operator also is allowable.

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

SET @var_name = expr;

As of MySQL 4.0.3, many system variables are dynamic and can be changed while the server runs by using the SET statement. For a list, see Section 5.2.4.2, “Dynamic System Variables”. To change a system variable with SET, refer to it as var_name, optionally preceded by a modifier:

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

A SET statement can contain multiple variable assignments, separated by commas. If you set several system variables, the most recent GLOBAL or SESSION modifier in the statement is used for following variables that have no modifier specified.

Examples: 

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;

When you assign a value to a system variable with SET, you cannot use suffix letters in the value (as can be done with startup options). However, the value can take the form of an expression: 

SET sort_buffer_size = 10 * 1024 * 1024;

The @@var_name syntax for system variables is supported for compatibility with some other database systems.

If you change a session system variable, the value remains in effect until your session ends or until you change the variable to a different value. The change is not visible to other clients.

If you change a global system variable, the value is remembered and used for new connections until the server restarts. (To make a global system variable setting permanent, you should set it in an option file.) The change is visible to any client that accesses that global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL statement).

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 (or @@global.) when setting a global variable.

To set a SESSION variable to the GLOBAL value or a GLOBAL value to the compiled-in MySQL default value, use the DEFAULT keyword. 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;

Not all system variables can be set to DEFAULT. In such cases, use of DEFAULT results in an error.

You can refer to the values of specific global or sesson system variables in expressions by using one of the @@-modifiers. For example, you can retrieve values in a SELECT statement like this: 

SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;

When you refer to a system variable in an expression as @@var_name (that is, when you do not specify @@global. or @@session.), MySQL returns the session value if it exists and the global value otherwise. (This differs from SET @@var_name = value, which always refers to the session value.)

To display system variables names and values, use the SHOW VARIABLES statement. (See Section 13.5.4.20, “SHOW VARIABLES Syntax”.)

The following list describes options that have non-standard syntax or that are not described in the list of system variables found in Section 5.2.3, “System Variables”. Although the options described here 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 |
+--------------+

The lettercase of thse options does not matter.

  • 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). 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. (Note: 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. 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. This variable was added in MySQL 3.23.52. See Section 14.2.7.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.

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

    This variable is a synonym for the LAST_INSERT_ID variable. It exists for compatibility with other database systems. 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' [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. This statement is available as of MySQL 4.1.0.

  • 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 available as of MySQL 4.1.3.

    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 |
+--------------------------+-------------------+

  • 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. 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. The default value is 1. 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. The default value is 0.

  • 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. The default value is 1. This variable was added in MySQL 3.22.5.

  • 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 4.1.11.

  • 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.5, “SHOW CREATE TABLE Syntax”, and Section 13.5.4.4, “SHOW CREATE DATABASE Syntax”. This variable was added in MySQL 3.23.26.

  • 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. This variable was added in MySQL 3.22.32.

  • 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. 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, 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. This variable was added in MySQL 3.23.52.

    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.


User Comments

Posted by Christian Hammers on July 29 2002 2:28am[Delete] [Edit]

Note, that the "SUPER_PRIV" was called
"PROCESS_PRIV" in 3.23.x.

Posted by [name withheld] on April 17 2003 3:35am[Delete] [Edit]

i got this error:
ERROR 1104: The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok

on this page it talk on how to set set the option SQL_BIG_SELECTS to 1, but i do not know exactly hoe to do that. should i add it to the .ini file, or put it in my query.

any help would greatly be appreciated.

Posted by [name withheld] on April 17 2003 3:37am[Delete] [Edit]

oh yeah,
when i use the command line client i get my records(it is only 3 of them), but when i use the gui (mqslcc) i get the error above.

Posted by Marco Tedone on April 20 2003 6:33am[Delete] [Edit]

I have the same with the GUI: how to set the SQL_BIG_SELECTS in the right way?

Regards,

Marco Tedone

Posted by Tim Sylvester on April 22 2003 1:43pm[Delete] [Edit]

When you create the connection in "MySQL Control Center" to ".", "localhost" or just "", make sure you set the values on the second page (MySQL Options).

"Automatically limit SELECT queries" and "Max join size" both defaulted to 1 when I set up the connection, which seems just plain silly. Set them to 1000 or something reasonable for you, and try again. Also, the "Max allowed packet" value may need to be increased for your particular application.

Posted by Harald Koschinski on May 26 2003 8:22pm[Delete] [Edit]

Statements like
set global query_cache_size=20m;

are not possible - must say 200000...

Posted by Mike McLaughlin on September 5 2003 5:53am[Delete] [Edit]

I know it is not right to promote asking questions on this board but this is more of a FYI.

If you get a "ERROR:1104 The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok"

and you are using the mySQL CC you can go to the option menu->Query Window Options (Ctrl+Q) at the top and go to the query tab. Set the SQL_BIG_SELECT=1. This should allow you to run a large query select.

Add your own comment.