How to modify variables in mysql

Method: 1. Use the "set GLOBAL|SESSION variables that need to be set" statement to modify; 2. Use the "set @user variables" or "set @@system variables" statement to modify. Variables refer to parameter variables that affect data resources.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to modify variables in mysql

MySQL Variables

MySQL Variables here actually refers to some parameters used to initialize or set the database Occupation of system resources, file storage location, etc. After the new system is installed, it has been initialized. But sometimes we don't want to take the default values, so we need to change these values.

MySQL variables are divided into two types:

• System variables: Configure the running environment of the MySQL server, you can use show variables to view

  can be divided into the following two types according to their different scopes:

  • is divided into global (GLOBAL) level : valid for the entire MySQL server

  • Session (SESSION or LOCAL) level: Only affects the current session

    Some variables have the above two levels at the same time, MySQL will use global when establishing a connection Level variables initialize session-level variables, but once the connection is established, changes to global-level variables will not affect session-level variables.

• Status variables: Monitor the running status of the MySQL server. You can use show status to view it and cannot be modified.

Modify the value of the system variable

1. The syntax for modifying the variable value:

set [GLOBAL | SESSION] 需要设置的变量

mysql> set global  log_queries_not_using_indexes=ON;

Query OK, 0 rows affected (0.00 sec)

2. There is another way of writing: @@

mysql> set  @@global.log_queries_not_using_indexes=ON;

Query OK, 0 rows affected (0.00 sec)

@: represents user variables

@@: represents system variables

View Values ​​of system variables (show)

View method one: System variables are stored in GLOBAL_VARIABLES and SESSION_VARIABLES# in the performance_schema database ##In the table, it can be obtained directly by viewing the contents of the table.

mysql> use performance_schema
Database changed

mysql> show tables like '%variables';
+-------------------------------------------+
| Tables_in_performance_schema (%variables) |
+-------------------------------------------+
| global_variables                          |
| persisted_variables                       |
| session_variables                         |
+-------------------------------------------+
3 rows in set (0.00 sec)

View method two: Use show variables syntax

SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern' | WHERE expr]

• Precise query:

  mysql> show variables like 'slow_query_log';
  
  +----------------+-------+
  | Variable_name  | Value |
  +----------------+-------+
  | slow_query_log | ON    |
  +----------------+-------+
  1 row in set, 1 warning (0.00 sec)

• Wildcard query (%)

  mysql> show variables like '%log';
  
  +----------------------------------+---------------------------+
  | Variable_name                    | Value                     |
  +----------------------------------+---------------------------+
  | back_log                         | 80                        |
  | general_log                      | OFF                       |
  | innodb_api_enable_binlog         | OFF                       |
  | log_statements_unsafe_for_binlog | ON                        |
  | relay_log                        | DESKTOP-Q8KGU39-relay-bin |
  | slow_query_log                   | ON                        |
  | sync_binlog                      | 1                         |
  | sync_relay_log                   | 10000                     |
  +----------------------------------+---------------------------+
  8 rows in set, 1 warning (0.00 sec)

• Single character matching query (_)

  mysql> show variables like 'log_b__';
  
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | log_bin       | ON    |
  +---------------+-------+
  1 row in set, 1 warning (0.01 sec)

• where statement query (for syntax, see sql where statement)

  mysql> show variables where variable_name = 'version';
  
  +---------------+--------+
  | Variable_name | Value  |
  +---------------+--------+
  | version       | 8.0.13 |
  +---------------+--------+
  1 row in set, 1 warning (0.00 sec)
  
  mysql> show variables where value like '8.%';
  +----------------+--------+
  | Variable_name  | Value  |
  +----------------+--------+
  | innodb_version | 8.0.13 |
  | version        | 8.0.13 |
  +----------------+--------+
  2 rows in set, 1 warning (0.00 sec)

Recommended learning:

mysql video tutorial

The above is the detailed content of How to modify variables in mysql. For more information, please follow other related articles on the PHP Chinese website!