7.24 SET OPTION syntax

SET [OPTION] SQL_VALUE_OPTION= value, ...

SET OPTION sets various options that affect the operation of the server or your client. Any option you set remains in effect until the current session ends, or until you set the option to a different value.

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");

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 row with the following construct: WHERE auto_increment_column IS NULL. This is used by some ODBC programs like Access.

SQL_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 (i.e., use in-memory temporary tables).

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

SQL_LOW_PRIORITY_UPDATES = 0 | 1

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

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 will be done to the standard log for this client, if the client has the process privilege. This does not affect the update log!

SQL_LOG_UPDATE = 0 | 1

If set to 0, no logging will be done to the update log for the client, if the client has the process privilege. This does not affect the standard log!

TIMESTAMP = timestamp_value | DEFAULT

Set the time for this client. This is used to get the original timestamp if you use the update log to restore rows.

LAST_INSERT_ID = #

Set the value to be returned from LAST_INSERT_ID(). This is stored in the update 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 command when inserting an AUTO_INCREMENT value. This is mainly used with the update log.