Set and show syntax set can be used to set various variables or options

Show can describe information about the database system in various forms, such as data tables, fields, server status, and so on. There are roughly the following methods:

Show [All] COLUMNS from tbl_name [to db_name] [like ' pattern ']
Show CREATE DATABASE db_name
Show CREATE TABLE tbl_name
Show DATABASES [as ' pattern ']
Show [STORAGE] Engines
Show ERRORS [LIMIT [Offset,] row_count]
Show grants for user
Show INDEX from Tbl_name [from Db_name]
Show INNODB STATUS
Show [BDB] LOGS
Show privileges
Show [Full] Processlist
Show STATUS [as ' pattern ']
Show TABLE STATUS [from db_name] [like ' pattern ']
Show [OPEN] TABLES [from db_name] [as ' pattern ']
Show [GLOBAL | Session] VARIABLES [as ' pattern ']
Show warnings [LIMIT [offset,] row_count]
If show syntax includes like ' pattern ' clauses, ' pattern ' is a string that can include wildcard characters such as '% ' and ' _ ' in the SQL syntax. This pattern statement is useful for obtaining the appropriate results.

Please note that there are several different ways to identify the following:

The set PASSWOR statement is used to set the password for the account, see "14.5.1.3 set PASSWORD Syntax" for details.
The show statement has several ways to describe the replication master server and the information from the server:
Show Binlog EVENTS
Show MASTER LOGS
Show MASTER STATUS
Show SLAVE HOSTS
Show SLAVE STATUS
　　
Please see "14.6 Replication statements" For detailed description of the show in these formats.


14.5.3.1 SET Syntax

SET variable_assignment [, Variable_assignment] ...

Variable_assignment:
User_var_name = Expr
| [GLOBAL | Session] System_var_name = expr
| @@[global. | System_var_name = Expr
Set can set up a variety of variable types that can affect the parameters of the server or the client. It can help you set a user variable or a system variable.
In the MySQL 4.0.3, we have added GLOBAL and session options and can dynamically modify the most important parameters in the system at runtime. The details of the system parameters that can be modified dynamically are described in detail in the 5.2.3.1 dynamic system Variables.
In the old version of MySQL, set is actually a substitute for SET option, which has fewer options, but is not recommended for use now.
The following example demonstrates several ways to use a set variable.

The user variable is written @var_name, set in the following ways:

SET @var_name = expr;
A description of the user variable's attributes will be mentioned in the section "10.3 User Variables".

System variables can be referenced in the SET syntax in a var_name manner. The variable name is preceded by the GLOBAL or @ @global. To indicate that this is a global variable, with the session, @ @session. Or @@ 来 indicate that this is a session variable. Local and @ @local. In fact, session and @ @session. is synonymous. If the variable name is not preceded by an additional modifier, the SET modifies only the session variable.

System variables are represented in the @ @var_name format, and can be supported in MySQL to be compatible with other database systems.

If you want to modify multiple system variables in the same statement, the last occurrence of GLOBAL or session will be the default option for variables that do not have modifiers in the statement.

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;
If you set the system variable with the session (default) option, its value will be valid until the end of the conversation or until you change it. If you set the system variable with the GLOBAL option, this must require you to have super (super) permissions, and its value will be valid until the system restarts before the new connection begins. If you want a variable to take effect permanently, you can put it in the configuration file. For more information, see "4.3.2 Using Option Files."

To prevent manipulation errors, for example, if you want to modify a variable with set global that can only be modified with set session, or if you are not modifying a global variable with set global, MySQL reports an error.

If you want to set the value of a session variable to the value of a global variable, or if you want the value of the global variable to become the default value of MySQL inline compilation, you can set it as default. The following 2 examples are all the same set the value of the session variable max_join_size as a global variable:

SET Max_join_size=default;
SET @ @session. max_join_size=@ @global. max_join_size;
You can list most of the system variables by show VARIABLES command. For more information, see "14.5.3.20 Show VARIABLES Syntax" syntax. If you want to get a list of variables in a pattern-matching way, you can use the LIKE clause:

Show VARIABLES like ' max_join_size ';
Show GLOBAL VARIABLES like ' max_join_size ';
You can also use SELECT @@[global.| Local.] Var_name to get the value of the specified variable:

SELECT @ @max_join_size, @ @global. max_join_size;
When you get the value of a variable by using the SELECT @ @var_name (so that you do not need to specify global or session or local), MySQL returns the value of the session variable, and if it does not, returns the global variable's Value.

Listed below are those that are not standard or are not listed in the chapter "5.2.3 Server System Variables". Although these variables are not displayed with show VARIABLES, you can obtain their values with the exception of SELECT (CHARACTER set and set NAMES). For example:

mysql> SELECT @ @AUTOCOMMIT;
+--------------+
| @ @autocommit |
+--------------+
| 1 |
+--------------+

Autocommit = {0 | 1}
Set autocommit (autocommit) mode. If set to 1, all operations on the datasheet will take effect immediately. If set to 0, a commit must be executed to commit the transaction or perform a ROLLBACK cancellation transaction. If you change the autocommit from 0 to 1, MySQL will automatically submit any open transactions. Another way to start a transaction can be to execute the start TRANSACTION or BEGIN statement. For more information, see "14.4.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax".
Big_tables = {0 | 1}
If set to 1, all temporary tables will be stored on disk rather than memory. This makes the speed a little bit slower, but the table tbl_name is not full error is no longer reported when a large temporary table is required to perform a SELECT operation. For a new connection, its default value is 0 (a temporary table is stored in memory). From MySQL 4.0, you do not need to set this variable, because MySQL will automatically, if necessary, the memory stored tables into the hard disk stored tables. The variable is now named Sql_big_tables.
CHARACTER SET {charset_name | DEFAULT}
It is used to map strings that are obtained from the client and sent to the client to the specified string. Before MySQL 4.1, the only allowed character set was cp1251_koi8, but you can also add new conversion character sets by modifying the ' sql/convert.cc ' file in the source code of the MySQL release. Starting with the MySQL 4.1.1, the set CHARACTER set sets 3 session system variables: Character_set_client and Character_set_results are set to the specified character set, character_set_ The value of the connection is set to the Character_set_database value. The default conversion character set can be reset to the value default. Note that the syntax of set CHARACTER sets is different from the syntax of most other settings options.
Foreign_key_checks = {0 | 1}
If it is set to 1 (the default), the InnoDB table checks the foreign KEY constraint relationship. If set to 0, it skips and does not check. It is useful to prevent foreign key constraint relationships from checking for InnoDB tables that are in an inconsistent order of the parent-child relationships that are actually required. This variable is added to the MySQL 3.23.52. Please see "16.7.4 FOREIGN KEY Constraints" for details.
IDENTITY = value
This variable and the variable last_insert_id are actually the same. It exists to be compatible with other database systems. Starting with the MySQL 3.23.25, you can get its value by executing the SELECT @ @IDENTITY. Starting with the MySQL 4.0.3, you can also perform a SET IDENTITY to change its value.
insert_id = value
Modifying its value can be used as the next value when inserting a auto_increment value in the execution of an INSERT or ALTER TABLE statement. It is mainly used in binary log (binary log).
last_insert_id = value
Modifying its value can be used as the return value of last_insert_id (). When the datasheet is updated, its value is stored in the binary log, and the last_insert_id () statement can be executed to get its value. Setting its value does not change the result value returned by the C API function mysql_insert_id ().
NAMES {' Charset_name ' | DEFAULT}
Set NAMES sets 3 session variables Character_set_client, character_set_connection and character_set_results for the specified character set. The default mapping relationship can be set to defaults. Note that the set NAMES syntax is also different from most other setup options. It's been there since MySQL 4.1.0.
Sql_auto_is_null = {0 | 1}
If set to 1 (the default), you can obtain the last inserted record of a table containing the Auto_increment type field by using the following statement:
WHERE Auto_increment_column is NULL
This practice is used in some ODBC programs, such as Access. The sql_auto_is_null variable is added to the MySQL 3.23.52.

Sql_big_selects = {0 | 1}
If set to 0, MySQL discards a SELECT statement that can take a long time (refers to cases where the optimizer estimates that the number of rows that need to be checked exceeds the Max_join_size setting). This is useful when there are undesirable where statements appear. The default value of Sql_big_selects for each new connection is 1 to allow all SELECT statements to be executed. If you set the system variable max_join_size to a Non-default value (default), the Sql_big_selects is also automatically set to 0.
Sql_buffer_result = {0 | 1}
Sql_buffer_result forces the result of a SELECT statement query to be placed in a temporary table. This allows MySQL to release the lock on the load table as soon as possible, while also helping to improve the situation that it takes a long time to send the results to the client. This variable is added to the MySQL 3.23.13.
Sql_log_bin = {0 | 1}
If set to 0, any binary logs are no longer logged. You must have a super (super) permission to modify its value. This variable is added to the MySQL 3.23.13.
Sql_log_off = {0 | 1}
If set to 1, no normal query log will be logged. You must have a super (super) permission to modify its value.
Sql_log_update = {0 | 1}
If set to 0, the update log will not log any logs. You must have a super (super) permission to modify its value. This variable is added to the MySQL 3.22.5. Starting with MySQL 5.0.0, it has been suggested that it be discontinued and turned into Sql_log_bin (see "c.1.3 Changes in Release 5.0.0" for details).
Sql_quote_show_create = {0 | 1}
If set to 1, the data table name and field are enclosed in quotation marks when the show CREATE table is executed, and if set to 0, it is no longer referenced. This option is turned on by default, because all table names and field names must be referenced when the database is synchronized. This option is added to the MySQL 3.23.26. For more information, see "14.5.3.6 Show CREATE TABLE Syntax".