MySQL in a Nutshell, 2nd Edition

Name

SET

Synopsis

SET [GLOBAL|@@global.|SESSION|@@session.] variable = expression

This statement sets a system or user variable for global or session use. Global variables relate to all users. Session variables are available only to the connection thread that creates the variable. For system variables to be recognized as global, the GLOBAL keyword is used. Alternatively, the variable can be preceded by @@global. to signify that it is global. For system variables that are limited to the current session, use the SESSION keyword, or place @@session or just @@ immediately in front of the variable name. The default for variables is to limit them to the session, making them local. LOCAL and @@local are aliases for SESSION and @@session, respectively. Here are a couple of examples involving system variables, one using the keyword method and the other using the variable prefix method:

SET GLOBAL concurrent_insert =  1;
SET @@session.interactive_timeout=40000;

The first statement disables concurrent inserts without having to restart the server. The second statement changes the interactive timeout to a higher value than normal. This setting is for the current client connection only. For other clients, this variable will still contain the default value.

To see a list of system variables and their values, use the SHOW VARIABLES statement. For a description of these variables, see Appendix C. For examples involving user variables, see the description of the SET statement in Chapter 6.