MySQL 通过set global设置变量的注意点

今天有人问，为什么他修改动态变量 log_output 的时候，部分可以设置成功，部分设置失败，具体现象如下：

root@localhost (none)>show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.01 sec)

root@localhost (none)>set global log_output=file;
Query OK, 0 rows affected (0.00 sec)

root@localhost (none)>show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.02 sec)

root@localhost (none)>set global log_output=none;
Query OK, 0 rows affected (0.00 sec)

root@localhost (none)>show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | NONE  |
+---------------+-------+
1 row in set (0.01 sec)

root@localhost (none)>set global log_output=table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table' at line 1
root@localhost (none)>

 

在配置文件中给MySQL做配置的时候，一般习惯了不加上引号的格式。比如：

[mysqld]
...
log_output=FILE
slow_query_log
slow_query_log_file=slow-queries.log

 

但是在 MySQL 会话中建议加上单引号。对于设置 MySQL 系统变量，尤其是需要字符串值的变量，应该使用单引号括起来，以确保 MySQL 能够正确解释这个值。比如，按照如下方式就可以正确设置了：

root@localhost (none)>show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | NONE  |
+---------------+-------+
1 row in set (0.01 sec)

root@localhost (none)>SET GLOBAL log_output = 'TABLE';
Query OK, 0 rows affected (0.01 sec)

root@localhost (none)>show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.02 sec)

root@localhost (none)>SET GLOBAL log_output = 'TABLE,FILE';
Query OK, 0 rows affected (0.00 sec)

root@localhost (none)>show variables like 'log_output';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| log_output    | FILE,TABLE |
+---------------+------------+
1 row in set (0.01 sec)

root@localhost (none)>