SET
|
|
SET
SET [GLOBAL | SESSION] variable_name=expression, [[GLOBAL | SESSION | LOCAL ] variable_name=expression...]
SET allows you to set variable values. SESSION (or LOCAL, a synonym) is the default, and it sets the value for the duration of the current connection. GLOBAL requires the SUPER privilege, and it sets the variable for all new connections until the server restarts. You still need to set it in the configuration file for an option to remain active after the server restarts. You can find the full list of variable names using SHOW VARIABLES. Table A.2 describes the variables that you set in a nonstandard way.
| Syntax | Description |
|---|---|
| AUTOCOMMIT= 0 | 1 | When set (1), MySQL automatically COMMITs statements unless you wrap them in BEGIN and COMMIT statements. MySQL also automatically COMMITs all open transactions when you set AUTOCOMMIT. |
| BIG_TABLES = 0 | 1 | When set (1), all temporary tables are stored on disk instead of in memory. This makes temporary tables slower, but it prevents the problem of running out of memory. The default is 0. |
| INSERT_ID = # | Sets the AUTO_INCREMENT value (so the next INSERT statement that uses an AUTO_INCREMENT field will use this value). |
| LAST_INSERT_ID = # | Sets the value returned from the next LAST_INSERT_ID() function. |
| LOW_PRIORITY_UPDATES = 0 | 1 | When set (1), all update statements (INSERT, UPDATE, DELETE, LOCKTABLE WRITE) wait for there to be no pending reads (SELECT, LOCK TABLE READ) on the table they're accessing. |
| MAX_JOIN_SIZE = value | DEFAULT | By setting a maximum size in rows, you can prevent MySQL from running queries that may not be making proper use of indexes or that may have thepotential to slow the server down when run in bulk or at peak times. Setting this to anything but DEFAULT resets SQL_BIG_ SELECTS. If SQL_BIG_SELECTS is set, then MAX_JOIN_SIZE is ignored. If the query is already cached, MySQL will ignore this limit and return the results. |
| QUERY_CACHE_TYPE = OFF | ON | DEMAND | Sets the query cache setting for the thread. |
| QUERY_CACHE_TYPE = 0 | 1 | 2 | Sets the query cache setting for the thread. |
| SQL_AUTO_IS_NULL = 0 | 1 | If set (1, the default), then the last inserted row for an AUTO_ INCREMENT can be found with WHERE auto_increment_column IS NULL. This isused by Microsoft Access and other programs connecting through ODBC. |
| SQL_BIG_SELECTS = 0 | 1 | If set (1, the default), then MySQL allows large queries. If not set (0), then MySQL will not allow queries where it will have to examine more than max_join_size rows. This is useful to avoid running accidental or malicious queries that could bring the server down. |
| SQL_BUFFER_RESULT = 0 | 1 | If set (1), MySQL places query results into a temporary table (in some cases speeding up performance by releasing table locks earlier). |
| SQL_LOG_OFF = 0 | 1 | If set (1), MySQL will not log for the client (this is not the update log). The SUPER permission is required. |
| SQL_LOG_UPDATE = 0 | 1 | If not set (0), MySQL will not use the update log for the client. This requires the SUPER permission. |
| SQL_QUOTE_SHOW_CREATE = 0 | 1 | If set (1, the default), MySQL will quote table and column names. |
| SQL_SAFE_UPDATES = 0 | 1 | If set (1), MySQL will not perform UPDATE or DELETE statements that don't use either an index or a LIMIT clause, which helps prevent unpleasant accidents. |
| SQL_SELECT_LIMIT = value | DEFAULT | Sets the maximum number of records (default unlimited) that can be returned with a SELECT statement. LIMIT takes precedence over this. |
| TIMESTAMP = timestamp_value | DEFAULT | Sets the time for the client. This can be used to get the original timestamp when using the update log to restore rows. The timestamp_value is a Unix epoch timestamp. |
The old SET OPTION syntax is now deprecated, so you should not use it anymore.
|
|
