GNU Info

Info Node: (mysql.info)SET OPTION

(mysql.info)SET OPTION


Prev: DNS Up: Optimizing the Server
Enter node , (file) or (file)node

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

SET TRANSACTION
`SET TRANSACTION' Syntax

automatically generated by info2www version 1.2.2.9