The SET Statement
MySQL supports two kinds of variables: SESSION
and GLOBAL
. SESSION
variables are set for the current
connection only and do not affect others. GLOBAL
variables apply to all connections
created after you set such a variable. But setting a GLOBAL
variable does not affect the current
connection,[10] so you should either set both SESSION
and GLOBAL
variables or reconnect if you need to use
a new value in the current connection.
You can set a session variable using the statement:
SET [SESSION]var_name
=value
I put SESSION
in square brackets
because you can omit this keyword; the set
command uses
SESSION
as the default.
To set a GLOBAL
variable, use the
statement:
SET GLOBALvar_name
=value
When you test options, I suggest you try to use a SESSION
variable whenever possible. After you
are happy with the results, you can use a GLOBAL
variable to change the running serverâs configuration, and then change the
configuration file so that this value will be applied after a
restart.
Note
Using SET SESSION
is also very
helpful when you want to check the effect of an option on a particular
query. In this case, you can set the variable before the query, test,
and then return back to the default value using the statement SET [SESSION] variable_name=DEFAULT
.
If an option is shared among threads, you can start by setting a
GLOBAL
variable, and then examine how the server behaves. After you are happy with the result, change the configuration file to include the new variable and its setting. ...
Get MySQL Troubleshooting now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.