MySQL in a Nutshell

Name

SET

Synopsis

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

Use this statement to set 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 flag 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 flag, or place @@session. or just @@ immediately in front of the variable name. The default for variables is to limit them to the session, to make them local. LOCAL and @@local. are aliases for SESSION and @@session., respectively. For a user variable, a single @ is placed in front of the variable name. Here is an example of creating a user variable:

SET @current_quarter = QUARTER(CURDATE( ));

This statement uses the CURDATE( ) function to determine the current date. It’s wrapped in the QUARTER( ) function, which determines the quarter for the date given. The result is a number from one to four depending on the date. The number is stored in the user variable, @current_quarter. Here are a couple of examples involving system variables—one using the flag 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 ...