System Variables Like @@version

MySQL Tutorials - Herong's Tutorial Examples

∟System, User-Defined and Stored Procedure Variables

∟System Variables Like @@version

This section provides a quick introduction on system variables, which are used by MySQL server to represent system information or control system behaviors.

What Are System Variables? System Variables are variables used MySQL Server to store parameters that represent system information or control system behaviors.

In MySQL 8.0.17, there are 570 system variables used by the server. You can log in to the server as "root" and list all of them using the "SHOW VARIABLES" command:

mysql> SHOW VARIABLES
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| auto_increment_increment        | 1                                 |
| auto_increment_offset           | 1                                 |
| automatic_sp_privileges         | ON                                |
| back_log                        | 151                               |
| basedir                         | /home/mysql/                      |
| binlog_cache_size               | 32768                             |
| bulk_insert_buffer_size         | 8388608                           |
| character_set_client            | utf8                              |
| character_set_connection        | utf8                              |
...
| version                         | 8.0.17                            |
| version_comment                 | MySQL Community Server - GPL      |
| version_compile_machine         | x86_64                            |
| version_compile_os              | macos10.14                        |
| version_compile_zlib            | 1.2.11                            |
| wait_timeout                    | 28800                             |
| warning_count                   | 0                                 |
| windowing_use_high_precision    | ON                                |
+---------------------------------+-----------------------------------+
570 rows in set (0.00 sec)

By the way, the "SHOW VARIABLES" command can take a "WHERE" clause to filter out returning rows like the "SELECT" statement.

mysql> show variables where Variable_name like '%time' and Value = '0';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| binlog_max_flush_queue_time | 0     |
| default_password_lifetime   | 0     |
| flush_time                  | 0     |
| max_execution_time          | 0     |
+-----------------------------+-------+

If the "WHERE" clause only has one Boolean operation on the "Variable_name" field, you can use the shorthand format:

mysql> show variables like '%time';
+-----------------------------+-----------+
| Variable_name               | Value     |
+-----------------------------+-----------+
| binlog_max_flush_queue_time | 0         |
| default_password_lifetime   | 0         |
| flush_time                  | 0         |
| innodb_old_blocks_time      | 1000      |
| long_query_time             | 10.000000 |
| max_execution_time          | 0         |
| slow_launch_time            | 2         |
+-----------------------------+-----------+

If you want to use system variables in operations, you must prefix their names with "@@".

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.17    |
+-----------+

Some system variables can be modified, if you have "root" privileges.

mysql> set @@version='9.0';
ERROR 1238 (HY000): Variable 'version' is a read only variable

mysql> set @@wait_timeout := 30000;
Query OK, 0 rows affected (0.00 sec)

mysql> set @@wait_timeout = 28800;
Query OK, 0 rows affected (0.00 sec)