MySQL wait_timeout 变量 - 全局 vs SESSION
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4440336/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL wait_timeout Variable - GLOBAL vs SESSION
提问by Arunjith
SHOW VARIABLES LIKE "%wait%"
Result: 28800
SET @@GLOBAL.wait_timeout=300
SHOW GLOBAL VARIABLES LIKE "%wait%"
Result: 300
SHOW SESSION VARIABLES LIKE "%wait%"
Result:28800
I am confused by the results. Why does the last query give Result:28800 ?
我对结果感到困惑。为什么最后一个查询给出 Result:28800 ？
回答by Riedsio
Your session status are set once you start a session, and by default, take the current GLOBAL value.
一旦您开始会话，您的会话状态就会设置，默认情况下，采用当前的 GLOBAL 值。
If you disconnected after you did SET @@GLOBAL.wait_timeout=300, then subsequently reconnected, you'd see
如果您在完成后断开连接SET @@GLOBAL.wait_timeout=300，然后重新连接，您会看到
SHOW SESSION VARIABLES LIKE "%wait%";
Result: 300
Similarly, at any time, if you did
同样，在任何时候，如果你
mysql> SET session wait_timeout=300;
You'd get
你会得到
mysql> SHOW SESSION VARIABLES LIKE 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 300 |
+---------------+-------+
回答by Anish Tangbe
SHOW SESSION VARIABLES LIKE "wait_timeout"; -- 28800
SHOW GLOBAL VARIABLES LIKE "wait_timeout"; -- 28800
At first, wait_timeout = 28800 which is the default value. To change the session value, you need to set the global variable because the session variable is read-only.
首先，wait_timeout = 28800 这是默认值。要更改会话值，您需要设置全局变量，因为会话变量是只读的。
SET @@GLOBAL.wait_timeout=300
After you set the global variable, the session variable automatically grabs the value.
设置全局变量后，会话变量会自动获取该值。
SHOW SESSION VARIABLES LIKE "wait_timeout"; -- 300
SHOW GLOBAL VARIABLES LIKE "wait_timeout"; -- 300
Next time when the server restarts, the session variables will be set to the default value i.e. 28800.
下次服务器重新启动时，会话变量将设置为默认值，即 28800。
P.S. I m using MySQL 5.6.16
PS 我使用的是 MySQL 5.6.16
回答by Cez
As noted by Riedsio, the session variables do not change after connecting unless you specifically set them; setting the global variable only changes the session value of your next connection.
正如Riedsio所指出的，除非您特别设置，否则会话变量在连接后不会更改；设置全局变量只会更改下一个连接的会话值。
For example, if you have 100 connections and you lower the global wait_timeoutthen it will not affect the existing connections, only new ones after the variable was changed.
例如，如果您有 100 个连接并降低全局连接，wait_timeout那么它不会影响现有连接，只会影响更改变量后的新连接。
Specifically for the wait_timeoutvariable though, there is a twist.
If you are using the mysqlclient in the interactive mode, or the connector with CLIENT_INTERACTIVEset via mysql_real_connect()then you will see the interactive_timeoutset for @@session.wait_timeout
wait_timeout但是，特别是对于变量，有一个转折。如果您mysql在交互模式下使用客户端，或者使用CLIENT_INTERACTIVEset via的连接器，mysql_real_connect()那么您将看到interactive_timeout设置为@@session.wait_timeout
Here you can see this demonstrated:
在这里你可以看到这个演示：
> ./bin/mysql -Bsse 'select @@session.wait_timeout, @@session.interactive_timeout, @@global.wait_timeout, @@global.interactive_timeout'
70 60 70 60
> ./bin/mysql -Bsse 'select @@wait_timeout'
70
> ./bin/mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.12-5 MySQL Community Server (GPL)
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@wait_timeout;
+----------------+
| @@wait_timeout |
+----------------+
| 60 |
+----------------+
1 row in set (0.00 sec)
So, if you are testing this using the client it is the interactive_timeoutthat you will see when connecting and not the value of wait_timeout
因此，如果您正在使用客户端进行测试interactive_timeout，那么您将在连接时看到的是wait_timeout
