`SET' 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");
or
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 row 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. Note:COMMIT. If set to `0' you have to use `COMMIT' /
`ROLLBACK' to accept/revoke that transaction. Note:COMMIT. Note
that when you change from not `AUTOCOMMIT' mode to `AUTOCOMMIT'
mode, MySQL will do an automatic `COMMIT' on any open transactions.
`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' (that is, 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_BUFFER_RESULT = 0 | 1'
`SQL_BUFFER_RESULT' will force the result from `SELECT''s 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.
`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_MAX_JOIN_SIZE = value | DEFAULT'
Don't allow `SELECT's that will probably need to examine more than
`value' row combinations. By setting this value, you can catch
`SELECT's 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=#'.
`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 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!
`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. Note:`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 update 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 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' or `ALTER TABLE'
command when inserting an `AUTO_INCREMENT' value. This is mainly
used with the update log.