(mysql.info) set-option
Info Catalog
(mysql.info) table-maintenance-sql
(mysql.info) database-administration-statements
(mysql.info) 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
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 server-system-variables, and
user-variables. System variables also can be set at server startup,
as described in using-system-variables.
Some variants of `SET' syntax are used in other contexts:
* `SET PASSWORD' assigns account passwords. See
set-password.
* `SET TRANSACTION ISOLATION LEVEL' sets the isolation level for
transaction processing. See set-transaction.
* `SET' is used within stored routines to assign values to local
routine variables. See set-statement.
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;
Many system variables are dynamic and can be changed while the server
runs by using the `SET' statement. For a list, see
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 show-variables.)
The following list describes options that have non-standard syntax or
that are not described in the list of system variables found in
server-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 `AUTOCOMMENT' 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 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' 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.
(* 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 charset-connection.
The default mapping can be restored by using the value `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 from that required by their parent/child
relationships. See innodb-foreign-key-constraints.
* `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' | 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'. See charset-connection.
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.
* `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 timezone
variables to reflect at rollforward what they were originally.
`ONE_SHOT' is available as of MySQL 5.0.
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}'
`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.
* `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.
* `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}'
This variable is deprecated, and is mapped to `SQL_LOG_BIN'.
* `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 5.0.3.
* `SQL_QUOTE_SHOW_CREATE = {0 | 1}'
If set to 1, 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
show-create-table, and show-create-database.
* `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.
* `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.
* `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'.
Info Catalog
(mysql.info) table-maintenance-sql
(mysql.info) database-administration-statements
(mysql.info) show
automatically generated byinfo2html