MySQL 透過set global設定變數的注意點

abce發表於2024-08-21
MySql變數

今天有人問，為什麼他修改動態變數 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)>

　　

相關文章