Database mysql/mariadb Knowledge points--database variables

Variable

In the MySQL database, there are two types of variables: System variables and custom variables.

According to the scope of the variable, but also divided into:

• Session-level variables: Valid only for the current client as a secondary connection;
• Global level variable: Any connection to all clients is valid.

Server variables can also be divided into dynamic variables and non-dynamic variables, dynamic variables can be modified in the case of the server does not restart

Note: Some of these parameters support runtime modification and will take effect immediately; some parameters are not supported and can only be changed by modifying the configuration file and restarting the server program; some parameters are global in scope and immutable; some can provide individual (session) settings for each user

Server Options

# mysqld--help-verbose #获取所有可以的选项

# mysqld--print-defaults #获取默认设置

#mysqld_safe –-skip-name-resolve=1

: Add option parameter when starting service, prohibit anti-parsing IP, improve login efficiency, also can add skip_name_resolve=1 parameter to my.cnf configuration file

Server System Variables

View all global variables

MariaDB [(None)]> SHOW GLOBAL VARIABLES;

View all current session variables

MariaDB [(None)]> SHOW VARIABLES;

Modify Global variables: Valid only for newly created sessions after a modification; invalid for an already established session

modifying Session Variables

Server state variables

View state variables (read-only): Variables for saving statistics in mysqld Run, cannot be changed

MariaDB [(None)]> SHOW GLOBAL STATUS;  #全局状态变量MariaDB [(None)]> SHOW STATUS;  #会话状态变量

Refer to the official documentation:

• Https://dev.mysql.com/doc/refman/5.7/en/mysqld-option-tables.html
• Https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables

Sql_mode

? Sql_mode is both an option and a variable that can be set up to perform some constraint checks on its settings, either globally or in the current session settings.

MariaDB [(None)]> SHOW VARIABLES like ' Sql_mode ';  #查看sql_mode变量, default is empty

• No_auto_create_user disallow grant to create a user with a blank password
• No_auto_value_on_zero inserting 0 in a self-growing column or null will not be the next self-growth value
• No_backslash_escapes backslash "" as normal character rather than escape character
• When Pad_char_to_full_length is enabled, empty data will not be truncated for CHAR types
• Pipes_as_concat will "| |" Treat as join operator instead of "or operator"
• Traditional: including Strict_trans_tables,strict_all_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero, Traditional,no_auto_create_user,no_engine_substitution, which is a collection of parameters

MariaDB [(none)]> SET  

Reference Official Document: Https://mariadb.com/kb/en/library/sql-mode

Database mysql/mariadb Knowledge points--database variables