MySQL - SHOW VARIABLES Statement



MySQL SHOW VARIABLES Statement

The SHOW VARIABLES Statement is used to display names and values of MySQL system variables.

Syntax

Following is the syntax of the SHOW VARIABLES Statement −

SHOW [GLOBAL | SESSION] VARIABLES
[LIKE 'pattern' | WHERE expr]

Example

You can retrieve the list GLOBAL VARIABLES in MySQL using the GLOBAL clause as shown below −

SHOW GLOBAL VARIABLES\G;

Output

The above query produces the following output −

************* 1. row *************
Variable_name: activate_all_roles_on_login
        Value: OFF
************* 2. row *************
Variable_name: admin_address
        Value:
************* 3. row *************
Variable_name: admin_port
        Value: 33062
************* 4. row *************
Variable_name: admin_ssl_ca
        Value:
************* 5. row *************
Variable_name: admin_ssl_capath
        Value:
************* 6. row *************
Variable_name: admin_ssl_cert
        Value:
************* 7. row *************
Variable_name: admin_ssl_cipher
        Value:
************* 8. row *************
Variable_name: admin_ssl_crl
        Value:
************* 9. row *************
Variable_name: admin_ssl_crlpath
        Value:
************* 10. row *************
Variable_name: admin_ssl_key
        Value:
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . .

Example

You can retrieve the list SESSION VARIABLES in MySQL using the SESSION class as shown below −

SHOW SESSION VARIABLES\G;

Output

Following is the output of the above query −

************* 1. row *************
Variable_name: admin_tls_version
        Value: TLSv1,TLSv1.1,TLSv1.2,TLSv1.3
************* 2. row *************
Variable_name: auto_generate_certs
        Value: ON
************* 3. row *************
Variable_name: auto_increment_increment
        Value: 1
************* 4. row *************
Variable_name: auto_increment_offset
        Value: 1
************* 5. row *************
Variable_name: autocommit
        Value: ON
************* 6. row *************
Variable_name: automatic_sp_privileges
        Value: ON
************* 7. row *************
Variable_name: avoid_temporal_upgrade
        Value: OFF
************* 8. row *************
Variable_name: back_log
        Value: 80

The LIKE clause

Using the LIKE clause, you can specify a pattern to retrieve specific variables.

Example

Let us verify whether loading local data is enabled, if not you can observe the local_infile variable value as −

SHOW GLOBAL VARIABLES LIKE 'local_infile';

Output

After executing the above, it will produce the following output −

Variable_name Value
local_infile OFF

Following query enables local_infile option as −

SET GLOBAL local_infile = 'ON';

If you verify the value of the variable local_infile again, you can observe the changed value as −

SHOW GLOBAL VARIABLES LIKE 'local_infile';

Output

Once the query is executed, it will generate following output −

Variable_name Value
local_infile ON

The WHERE clause

You can use the WHERE clause of the SHOW VARIABLES statements to retrieve names of the variables which match the specified condition.

SHOW VARIABLES WHERE Variable_name= 'autocommit';

Output

Following is the output of the above mysql query −

Variable_name Value
autocommit ON
Advertisements