Movatterモバイル変換

SHOW [GLOBAL | SESSION] VARIABLES    [LIKE 'pattern' | WHEREexpr]

SHOW VARIABLES shows the values of MySQL system variables (seeSection 5.1.7, “Server System Variables”). This statement does not require any privilege. It requires only the ability to connect to the server.

System variable information is also available from these sources:

ForSHOW VARIABLES, aLIKE clause, if present, indicates which variable names to match. AWHERE clause can be given to select rows using more general conditions, as discussed inSection 24.8, “Extensions to SHOW Statements”.

SHOW VARIABLES accepts an optionalGLOBAL orSESSION variable scope modifier:

The scope for each system variable is listed atSection 5.1.7, “Server System Variables”.

SHOW VARIABLES is subject to a version-dependent display-width limit. For variables with very long values that are not completely displayed, useSELECT as a workaround. For example:

SELECT @@GLOBAL.innodb_data_file_path;

Most system variables can be set at server startup (read-only variables such asversion_comment are exceptions). Many can be changed at runtime with theSET statement. SeeSection 5.1.8, “Using System Variables”, andSection 13.7.4.1, “SET Syntax for Variable Assignment”.

Partial output is shown here. The list of names and values may differ for your server.Section 5.1.7, “Server System Variables”, describes the meaning of each variable, andSection 5.1.1, “Configuring the Server”, provides information about tuning them.

mysql> SHOW VARIABLES;+-----------------------------------------+---------------------------+| Variable_name                           | Value                     |+-----------------------------------------+---------------------------+| auto_increment_increment                | 1                         || auto_increment_offset                   | 1                         || autocommit                              | ON                        || automatic_sp_privileges                 | ON                        || back_log                                | 50                        || basedir                                 | /home/jon/bin/mysql-5.5   || big_tables                              | OFF                       || binlog_cache_size                       | 32768                     || binlog_direct_non_transactional_updates | OFF                       || binlog_format                           | STATEMENT                 || binlog_stmt_cache_size                  | 32768                     || bulk_insert_buffer_size                 | 8388608                   |...| max_allowed_packet                      | 4194304                   || max_binlog_cache_size                   | 18446744073709547520      || max_binlog_size                         | 1073741824                || max_binlog_stmt_cache_size              | 18446744073709547520      || max_connect_errors                      | 100                       || max_connections                         | 151                       || max_delayed_threads                     | 20                        || max_error_count                         | 64                        || max_heap_table_size                     | 16777216                  || max_insert_delayed_threads              | 20                        || max_join_size                           | 18446744073709551615      |...| thread_handling                         | one-thread-per-connection || thread_stack                            | 262144                    || time_format                             | %H:%i:%s                  || time_zone                               | SYSTEM                    || timestamp                               | 1316689732                || tmp_table_size                          | 16777216                  || tmpdir                                  | /tmp                      || transaction_alloc_block_size            | 8192                      || transaction_isolation                   | REPEATABLE-READ           || transaction_prealloc_size               | 4096                      || transaction_read_only                   | OFF                       || tx_isolation                            | REPEATABLE-READ           || tx_read_only                            | OFF                       || unique_checks                           | ON                        || updatable_views_with_limit              | YES                       || version                                 | 5.7.44                    || version_comment                         | Source distribution       || version_compile_machine                 | x86_64                    || version_compile_os                      | Linux                     || wait_timeout                            | 28800                     || warning_count                           | 0                         |+-----------------------------------------+---------------------------+

With aLIKE clause, the statement displays only rows for those variables with names that match the pattern. To obtain the row for a specific variable, use aLIKE clause as shown:

SHOW VARIABLES LIKE 'max_join_size';SHOW SESSION VARIABLES LIKE 'max_join_size';

To get a list of variables whose name match a pattern, use the% wildcard character in aLIKE clause:

SHOW VARIABLES LIKE '%size%';SHOW GLOBAL VARIABLES LIKE '%size%';

Wildcard characters can be used in any position within the pattern to be matched. Strictly speaking, because_ is a wildcard that matches any single character, you should escape it as\_ to match it literally. In practice, this is rarely necessary.