MySQL Tutorials - Herong's Tutorial Examples - v4.46, by Herong Yang
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)
Note that both ":=" and "=" are valid assignment operators.
Table of Contents
MySQL Introduction and Installation
Introduction of MySQL Programs
Perl Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
Table Column Types for Different Types of Values
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
►System, User-Defined and Stored Procedure Variables
►System Variables Like @@version
Scopes of System Variables: Global and Session
User-Defined Variables Like @x
User-Defined vs. Stored Procedure Variables
Storage Engines in MySQL Server
InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Installing MySQL Server on Linux