Question

MySQL中，如何修改collation_server的latin1_swedish_ci为utf8_general_ci？

如题，在MySQL中，使用SHOW VARIABLES LIKE '%colla%'的查询结果为：collation_connection utf8_general_ci
collation_database utf8_general_ci
collation_server latin1_swedish_ci
如何修改collation_server的latin1_swedish_ci为utf8_general_ci？

Answer 1

这里以 table_lock_wait_timeout 为例：
首先查看参数值。

mysql> show variables like '%wait_timeout%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+--------------------------+-------+
3 rows in set (0.00 sec)

修改参数值

mysql> set table_lock_wait_timeout = 60;
ERROR 1229 (HY000): Variable 'table_lock_wait_timeout' is a GLOBAL variable and should be set with S
ET GLOBAL
mysql> set GLOBAL table_lock_wait_timeout = 60;
Query OK, 0 rows affected (0.00 sec)

查看参数是否被修改成功
mysql> show variables like '%wait_timeout%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
| table_lock_wait_timeout | 60 |
| wait_timeout | 28800 |
+--------------------------+-------+
3 rows in set (0.01 sec)

上面修改的是 “全局参数”。修改设置的时候，需要 “set GLOBAL”

下面修改的是 “非全局参数”。修改设置的时候，简单的“set”就可以了。

mysql> show variables like '%char%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | E:\MySQL\MySQL Server 5.1\share\charsets\ |
+--------------------------+-------------------------------------------+
8 rows in set (0.02 sec)

mysql> set character_set_client = 'UTF8';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | E:\MySQL\MySQL Server 5.1\share\charsets\ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)