5.5.6 SET Syntax

SET [GLOBAL | SESSION] sql_variable=expression, [[GLOBAL | SESSION] sql_variable=expression...]

SET sets various options that affect the operation of the server or your client.

The following examples shows the different syntaxes one can use to set variables:

In old MySQL versions we allowed the use of the SET OPTION syntax, but this syntax is now deprecated.

In MySQL 4.0.3 we added the GLOBAL and SESSION options and access to most important startup variables.

LOCAL can be used as a synonym for SESSION.

If you set several variables on the same command line, the last used GLOBAL | SESSION mode is used.

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;

The @@variable_name syntax is supported to make MySQL syntax compatible with some other databases.

The different system variables one can set are described in the system variable section of this manual. See section 6.1.5 System Variables.

If you are using SESSION (the default) the option you set remains in effect until the current session ends, or until you set the option to a different value. If you use GLOBAL, which require the SUPER privilege, the option is remembered and used for new connections until the server restarts. If you want to make an option permanent, you should set it in one of the MySQL option files. See section 4.1.2 `my.cnf' Option Files.

To avoid wrong usage MySQL will give an error if you use SET GLOBAL with a variable that can only be used with SET SESSION or if you are not using SET GLOBAL with a global variable.

If you want to set a SESSION variable to the GLOBAL value or a GLOBAL value to the MySQL default value, you can set it to DEFAULT.

SET max_join_size=DEFAULT;

This is identical to:

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

If you want to restrict the maximum value a startup option can be set to with the SET command, you can specify this by using the --maximum-variable-name command line option. See section 4.1.1 mysqld Command-line Options.

You can get a list of most variables with SHOW VARIABLES. See section 4.5.7.4 SHOW VARIABLES. You can get the value for a specific value with the @@[global.|local.]variable_name syntax:

SHOW VARIABLES like "max_join_size";
SHOW GLOBAL VARIABLES like "max_join_size";
SELECT @@max_join_size, @@global.max_join_size;

Here follows a description of the variables that uses a the variables that uses a non-standard SET syntax and some of the other variables. The other variable definitions can be found in the system variable section, among the startup options or in the description of SHOW VARIABLES. See section 6.1.5 System Variables. See section 4.1.1 mysqld Command-line Options. See section 4.5.7.4 SHOW VARIABLES.

CHARACTER SET character_set_name | DEFAULT

This maps all strings from and to the client with the given mapping. Currently the only option for character_set_name is cp1251_koi8, but you can easily add new mappings by editing the `sql/convert.cc' file in the MySQL source distribution. The default mapping can be restored by using a character_set_name value of DEFAULT. Note that the syntax for setting the CHARACTER SET option differs from the syntax for setting the other options.

PASSWORD = PASSWORD('some password')

Set the password for the current user. Any non-anonymous user can change his own password!

PASSWORD FOR user = PASSWORD('some password')

Set the password for a specific user on the current server host. Only a user with access to the mysql database can do this. The user should be given in user@hostname format, where user and hostname are exactly as they are listed in the User and Host columns of the mysql.user table entry. For example, if you had an entry with User and Host fields of 'bob' and '%.loc.gov', you would write:

mysql> SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("newpass");

Which is equivalent to:

mysql> UPDATE mysql.user SET password=PASSWORD("newpass")
    ->                   WHERE user="bob" AND host="%.loc.gov";

SQL_AUTO_IS_NULL = 0 | 1

If set to 1 (default) then one can find the last inserted row for a table with an AUTO_INCREMENT column with the following construct: WHERE auto_increment_column IS NULL. This is used by some ODBC programs like Access.

AUTOCOMMIT= 0 | 1

If set to 1 all changes to a table will be done at once. To start a multi-command transaction, you have to use the BEGIN statement. See section 6.7.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax. If set to 0 you have to use COMMIT / ROLLBACK to accept/revoke that transaction. See section 6.7.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax. Note that when you change from not AUTOCOMMIT mode to AUTOCOMMIT mode, MySQL will do an automatic COMMIT on any open transactions.

BIG_TABLES = 0 | 1

If set to 1, all temporary tables are stored on disk rather than in memory. This will be a little slower, but you will not get the error The table tbl_name is full for big SELECT operations that require a large temporary table. The default value for a new connection is 0 (that is, use in-memory temporary tables). This option was before named SQL_BIG_TABLES. In MySQL 4.0 you should normally never need this flag as MySQL will automatically convert in memory tables to disk based ones if need.

SQL_BIG_SELECTS = 0 | 1

If set to 0, MySQL will abort if a SELECT is attempted that probably will take a very long time, which is defined as if the number of examined rows is probably going to be bigger than MAX_JOIN_SIZE. This is useful when an inadvisable WHERE statement has been issued. A big query is defined as a SELECT that probably will have to examine more than max_join_size rows. The default value for a new connection is 1 (which will allow all SELECT statements). If you set MAX_JOIN_SIZE to another value than DEFAULT SQL_BIG_SELECTS will be set to 0.

SQL_BUFFER_RESULT = 0 | 1

SQL_BUFFER_RESULT will force the result from SELECTs to be put into a temporary table. This will help MySQL free the table locks early and will help in cases where it takes a long time to send the result set to the client.

LOW_PRIORITY_UPDATES = 0 | 1

If set to 1, all INSERT, UPDATE, DELETE, and LOCK TABLE WRITE statements wait until there is no pending SELECT or LOCK TABLE READ on the affected table. This option was before named SQL_LOW_PRIORITY_UPDATES.

MAX_JOIN_SIZE = value | DEFAULT

Don't allow SELECTs that will probably need to examine more than value row combinations or is likely to do more than value disk seeks. By setting this value, you can catch SELECTs where keys are not used properly and that would probably take a long time. Setting this to a value other than DEFAULT will reset the SQL_BIG_SELECTS flag. If you set the SQL_BIG_SELECTS flag again, the SQL_MAX_JOIN_SIZE variable will be ignored. You can set a default value for this variable by starting mysqld with -O max_join_size=#. This option was before named SQL_MAX_JOIN_SIZE. Note that if the result of the query is already in the query cache, the above check will not be made. Instead, MySQL will send the result to the client. Since the query result is already computed and it will not burden the server to send the result to the client.

QUERY_CACHE_TYPE = OFF | ON | DEMAND

QUERY_CACHE_TYPE = 0 | 1 | 2

Set query cache setting for this thread.

Option	Description
0 or OFF	Don't cache or retrieve results.
1 or ON	Cache all results except SELECT SQL_NO_CACHE ... queries.
2 or DEMAND	Cache only SELECT SQL_CACHE ... queries.

SQL_SAFE_UPDATES = 0 | 1

If set to 1, MySQL will abort if an UPDATE or DELETE is attempted that doesn't use a key or LIMIT in the WHERE clause. This makes it possible to catch wrong updates when creating SQL commands by hand.

SQL_SELECT_LIMIT = value | DEFAULT

The maximum number of records to return from SELECT statements. If a SELECT has a LIMIT clause, the LIMIT takes precedence over the value of SQL_SELECT_LIMIT. 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.

SQL_LOG_OFF = 0 | 1

If set to 1, no logging is done to the standard log for this client, if the client has the SUPER privilege.

SQL_LOG_BIN = 0 | 1

If set to 0, no logging is done to the binary log for the client, if the client has the SUPER privilege.

SQL_LOG_UPDATE = 0 | 1

If set to 0, no logging is done to the update log for the client, if the client has the SUPER privilege. This variable is deprecated starting from version 5.0.

SQL_QUOTE_SHOW_CREATE = 0 | 1

If set to 1, SHOW CREATE TABLE will quote table and column names. This is on by default, for replication of tables with fancy column names to work. section 4.5.7.8 SHOW CREATE TABLE.

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.

LAST_INSERT_ID = #

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 command that updates a table.

INSERT_ID = #

Set the value to be used by the following INSERT or ALTER TABLE command when inserting an AUTO_INCREMENT value. This is mainly used with the binary log.

User Comments

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

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.

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.

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

Regards,

Marco Tedone

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.

The SET command seems not support some varibles such as ft_word_min_len, ft_stopword_list.
When submitting SET ft_word_min_len=3, it reports that "ERROR 1193: Unknown system variable 'ft_word_min_len'".
The doc says it should work, but it don't.

Statements like
set global query_cache_size=20m;

are not possible - must say 200000...

Add your own comment.