SHOW VARIABLES

This MySQL statement displays the system variables for the MySQL server.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

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

Explanation

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 could be used to refine the results set.

Examples

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 only see 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        | 
+-------------------------+-------------+

Notice that for the WHERE clause we specify the field names of the results set: Variable_name and Value. We're also using in this case the LIKE and REGEXP string comparison functions to narrow the results.

You can change many of the variables at server startup with options for the MySQL server daemon. You can change some of them while the daemon is running with the SET statement, without having to restart the server.