(mysql.info.gz) SET OPTION
Info Catalog
(mysql.info.gz) Table maintenance SQL
(mysql.info.gz) Database Administration
(mysql.info.gz) SHOW
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 `SET PASSWORD' SET PASSWORD.
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
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 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.
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'.
`SHOW VARIABLES' SHOW VARIABLES. 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
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.
`COMMIT' COMMIT.
`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' will 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.
`InnoDB' foreign key constraints InnoDB 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 theh 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_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 will
take a very long time (that is, statements for which the optimizer
estimates that the number of examined rows will exceed 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' will be 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' (
News-5.0.0).
`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 will work for tables with table and
column names that require quoting. This variable was added in
MySQL 3.23.26. `SHOW CREATE TABLE' SHOW CREATE TABLE.
`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.
Info Catalog
(mysql.info.gz) Table maintenance SQL
(mysql.info.gz) Database Administration
(mysql.info.gz) SHOW
automatically generated byinfo2html