4.2.2.5 Using Options to Set Program Variables使用选项设置程序变量

Many MySQL programs have internal variables that can be set at runtime using the SET statement. 许多MySQL程序都有内部变量,可以在运行时使用SET语句进行设置。See Section 13.7.6.1, “SET Syntax for Variable Assignment”, and Section 5.1.9, “Using System Variables”.请参阅第13.7.6.1节,“变量赋值的SET语法”第5.1.9节,“使用系统变量”

Most of these program variables also can be set at server startup by using the same syntax that applies to specifying program options. 这些程序变量中的大多数也可以在服务器启动时使用与指定程序选项相同的语法进行设置。For example, mysql has a max_allowed_packet variable that controls the maximum size of its communication buffer. 例如,mysql有一个max_allowed_packet变量,用于控制其通信缓冲区的最大大小。To set the max_allowed_packet variable for mysql to a value of 16MB, use either of the following commands:要将mysqlmax_allowed_packet变量设置为16MB的值,请使用以下命令之一:

mysql --max_allowed_packet=16777216
mysql --max_allowed_packet=16M

The first command specifies the value in bytes. The second specifies the value in megabytes. 第一个命令指定以字节为单位的值。第二个指定以兆字节为单位的值。For variables that take a numeric value, the value can be given with a suffix of K, M, or G to indicate a multiplier of 1024, 10242 or 10243. 对于取数值的变量,可以用后缀KMG来表示1024、10242或10243的倍数。(For example, when used to set max_allowed_packet, the suffixes indicate units of kilobytes, megabytes, or gigabytes.) (例如,当用于设置max_allowed_packet时,后缀表示千字节、兆字节或千兆字节的单位。)As of MySQL 8.0.14, a suffix can also be T, P, and E to indicate a multiplier of 10244, 10245 or 10246. 从MySQL 8.0.14开始,后缀也可以是TPE,表示10244、10245或10246的倍数。Suffix letters can be uppercase or lowercase.后缀字母可以是大写或小写。

In an option file, variable settings are given without the leading dashes:在选项文件中,变量设置不带前导短划线:

[mysql]
max_allowed_packet=16777216

Or:

[mysql]
max_allowed_packet=16M

If you like, underscores in an option name can be specified as dashes. The following option groups are equivalent. Both set the size of the server's key buffer to 512MB:如果您愿意,选项名称中的下划线可以指定为短划线。以下选项组是等效的。两者都将服务器的密钥缓冲区大小设置为512MB:

[mysqld]
key_buffer_size=512M

[mysqld]
key-buffer-size=512M

Suffixes for specifying a value multiplier can be used when setting a variable at program invocation time, but not to set the value with SET at runtime. 用于指定值乘数的后缀可以在程序调用时设置变量时使用,但不能在运行时使用SET设置值。On the other hand, with SET, you can assign a variable's value using an expression, which is not true when you set a variable at server startup. For example, the first of the following lines is legal at program invocation time, but the second is not:另一方面,使用SET,您可以使用表达式来分配变量的值,但当您在服务器启动时设置变量时,这是不正确的。例如,以下第一行在程序调用时是合法的,但第二行不是:

shell> mysql --max_allowed_packet=16M
shell> mysql --max_allowed_packet=16*1024*1024

Conversely, the second of the following lines is legal at runtime, but the first is not:相反,以下第二行在运行时是合法的,但第一行不是:

mysql> SET GLOBAL max_allowed_packet=16M;
mysql> SET GLOBAL max_allowed_packet=16*1024*1024;