open连接MySQL超时,如何根据MySQL中的用户登录设置连接超时

I have currently more than 100 connections in Sleep state.

Some connection must stay in Sleep state (and don't close) because it's permanent connection but some others (with a different user name) are from some php script and I want them to timeout very fast.

Is it possible to setup a wait_timeout per user? and if yes, How?

解决方案

There's no per-user timeout configuration, but you can set the wait_timeout value dynamically. That is, after you make a connection as a given user, you can issue a statement to change the timeout value to what you want it to be for that user's session.

Try the following experiment in the mysql command-line client:

mysql> SHOW VARIABLES LIKE 'wait_timeout';

...shows 28800 (i.e. 8 hours), which is the default wait_timout.

mysql> SET SESSION wait_timeout = 60;

mysql> SHOW VARIABLES LIKE 'wait_timeout';

...shows 60.

Then you can quit the session, reconnect, and again the default wait_timeout is 28800. So it's limited to the scope of the current session.

You can also open a second window and start a separate mysql client session, to prove that changing the wait_timeout in one session does not affect other concurrent sessions.