Table of Contents for

MySQL in a Nutshell, 2nd Edition

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

Notice that, for the WHERE clause, we specify the field names of the results set: Variable_name and Value. In this case, we’re also using 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. See Chapter 15 for more details. You can change some of them while the daemon is running with the SET statement, without having to restart the server. That statement is covered earlier in this chapter.