With a LIKE  clause, the statement displays only those variables that match the pattern. To obtain a specific variable name, use a LIKE  clause as shown: 

SHOW VARIABLES LIKE ’max_join_size’; SHOW SESSION VARIABLES LIKE ’max_join_size’; 

To get a list of variables whose name match a pattern, use the %  wildcard character in a LIKE  clause: 

SHOW VARIABLES LIKE ’%size%’; SHOW GLOBAL VARIABLES LIKE ’%size%’; 

Wildcard characters can be used in any position within the pattern to be matched. Strictly speaking, because _  is a wildcard that matches any single character, you should escape it as \_  to match it literally. In practice, this is rarely necessary. 

For SHOW VARIABLES , if you specify neither GLOBAL  nor SESSION , MySQL returns SESSION  values. 

The reason for requiring the GLOBAL  keyword when setting GLOBAL -only variables but not when retrieving them is to prevent problems in the future: 

•Were a SESSION  variable to be removed that has the same name as a GLOBAL  variable, a client with privileges sufficient to modify global variables might accidentally change the GLOBAL  variable rather than just the SESSION  variable for its own session. 

•Were a SESSION  variable to be added with the same name as a GLOBAL  variable, a client that intends to change the GLOBAL  variable might find only its own SESSION  variable changed. 

## 5.1.9.1 System Variable Privileges 

A system variable can have a global value that affects server operation as a whole, a session value that affects only the current session, or both: 

•For dynamic system variables, the SET  statement can be used to change their global or session runtime value (or both), to affect operation of the current server instance. (For information about dynamic variables, see Section 5.1.9.2, “Dynamic System Variables” .) 

•For certain global system variables, SET  can be used to persist their value to the mysqld-auto.cnf  file in the data directory, to affect server operation for subsequent startups. (For information about persisting system variables and the mysqld-auto.cnf  file, see Section 5.1.9.3, “Persisted System Variables” .) 

•For persisted global system variables, RESET PERSIST  can be used to remove their value from mysqld-auto.cnf , to affect server operation for subsequent startups. 

This section describes the privileges required for operations that assign values to system variables at runtime. This includes operations that affect runtime values, and operations that persist values. 

To set a global system variable, use a SET  statement with the appropriate keyword. These privileges apply: 

•To set a global system variable runtime value, use the SET GLOBAL  statement, which requires the SYSTEM_VARIABLES_ADMIN  privilege (or the deprecated SUPER  privilege). 

•To persist a global system variable to the mysqld-auto.cnf  file (and set the runtime value), use the SET PERSIST  statement, which requires the SYSTEM_VARIABLES_ADMIN  or SUPER  privilege. 

•To persist a global system variable to the mysqld-auto.cnf  file (without setting the runtime value), use the SET PERSIST_ONLY  statement, which requires the SYSTEM_VARIABLES_ADMIN  and PERSIST_RO_VARIABLES_ADMIN  privileges. SET PERSIST_ONLY  can be used for both dynamic and read-only system variables, but is particularly useful for persisting read-only variables, for which SET PERSIST  cannot be used. 

