MySQL in a Nutshell, 2nd Edition

Name

SHOW VARIABLES

Synopsis

SHOW [GLOBAL|LOCAL|SESSION] VARIABLES [LIKE 'pattern'|WHERE expression]

This statement displays the system variables for the MySQL server. The SESSION keyword displays values for current sessions or connections. This is the default and is synonymous with LOCAL. The GLOBAL keyword shows variables that relate to new connections. You can limit the variables with the LIKE clause and a naming pattern for the variables. Similarly, the WHERE clause can be used to refine the results set. Here is an example of this statement with the LIKE clause:

SHOW GLOBAL VARIABLES LIKE 'version%';

+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.1.16-beta                  | 
| version_comment         | MySQL Community Server (GPL) | 
| version_compile_machine | i686                         | 
| version_compile_os      | pc-linux-gnu                 | 
+-------------------------+------------------------------+

In this example, the variables shown are limited to global variables whose names begin with the word version. Suppose that we wanted to see only the two variables of these results that contain a numeric value. We could do this by using the WHERE clause like so:

SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'version%' AND Value REGEXP '[0-9]'; +-------------------------+-------------+ | Variable_name | Value | +-------------------------+-------------+ | version | 5.1.16-beta | | version_compile_machine | i686 | +-------------------------+-------------+ ...