在my.cnf中将MySQL默认字符集更改为UTF-8？

目前我们在PHP中使用以下命令在我们的应用程序中将字符集设置为UTF-8.

由于这有点开销,我们想将其设置为MySQL中的默认设置.我们可以在/etc/my.cnf或其他地方这样做吗？

SET NAMES 'utf8'
SET CHARACTER SET utf8

我在/etc/my.cnf中找了一个默认的字符集,但是没有关于字符集的内容.

此时,我执行以下操作将MySQL字符集和校对变量设置为UTF-8:

skip-character-set-client-handshake
character_set_client=utf8
character_set_server=utf8

这是处理这个问题的正确方法吗？

1> NinjaCat..：

---

要将默认值设置为UTF-8,您需要将以下内容添加到my.cnf

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

如果您想更改现有数据库的字符集,请告诉我...您的问题没有直接指定,所以我不确定这是否是您想要做的.

---

上面的my.cnf设置也适用于我.另外,我必须确保表格设置正确,例如ALTER TABLE`Table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

---

在Ubuntu 12.04上,这对我有用 - 如果我在`[mysqld]`之后删除了第一行.

---

不适用于mysql 5.5.我用过:
[mysqld]#如上所述,utf-8 collat​​ion-server = utf8mb4_unicode_ci init-connect ='SET NAMES utf8mb4'character-set-server = utf8mb4和utf8mb4的更改.

---

看起来[mysqld]部分中不再允许使用default-character-set

---

请注意,如果您想要UTF-8,请不要使用MySQL的`utf8`字符集.[改用'utf8mb4`.](http://mths.be/utf8mb4)

---

注意:来自mysql docs:对于具有SUPER权限的用户,不执行init_connect的内容.http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_character_set_server

---

小输入,考虑使用`utf8mb4`作为字符集,使用`utf8mb4_unicode_ci`进行整理以覆盖所有utf8.请参阅/sf/ask/17360801/

2> Mustafah..：

---

对于最新版本的MySQL,

default-character-set = utf8

导致问题.我认为它已被弃用了.

正如Justin Ball在" 升级到MySQL 5.5.12并且现在MySQL无法启动 "中所说,你应该:

删除该指令,你应该是好的.

然后您的配置文件(例如'/etc/my.cnf')应如下所示:

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

重启MySQL.

为了确保您的MySQL是UTF-8,请在MySQL提示符中运行以下查询:

第一个查询:

 mysql> show variables like 'char%';

输出应如下所示:

 +--------------------------+---------------------------------+
 | Variable_name            | Value                           |
 +--------------------------+---------------------------------+
 | character_set_client     | utf8                            |
 | character_set_connection | utf8                            |
 | character_set_database   | utf8                            |
 | character_set_filesystem | binary                          |
 | character_set_results    | utf8                            |
 | character_set_server     | utf8                            |
 | character_set_system     | utf8                            |
 | character_sets_dir       | /usr/local/mysql/share/charsets/|
 +--------------------------+---------------------------------+

第二个查询:

 mysql> show variables like 'collation%';

查询输出是:

 +----------------------+-----------------+
 | Variable_name        | Value           |
 +----------------------+-----------------+
 | collation_connection | utf8_general_ci |
 | collation_database   | utf8_unicode_ci |
 | collation_server     | utf8_unicode_ci |
 +----------------------+-----------------+

3> tomazzlender..：

---

在MySQL 5.5上我有my.cnf

[mysqld] 
init_connect='SET collation_connection = utf8_unicode_ci' 
init_connect='SET NAMES utf8' 
character-set-server=utf8 
collation-server=utf8_unicode_ci 
skip-character-set-client-handshake

结果是

mysql> show variables like "%character%";show variables like "%collation%";

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

---

`skip-character-set-client-handshake`为我做了(当然还有"字符集 - 服务器"和"校对服务器" - 在Debian 7上).无论有没有`skip`,`init_connect'都没有区别,看起来多余.+1,谢谢.

---

@Simon,当使用`show variables like"%collat​​ion%"时;`我看到'collat​​ion_connection'为`utf8_general_ci`,collat​​ion_database和collat​​ion_server为`utf8_unicode_ci`.添加行`skip-character-set-client-handshake`会将一般条目更改为unicode,因此所有三个都是一致的.

---

[skip-character-set-client-handshake](http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_character-set-client-handshake)忽略发送的字符集信息客户端.在通信过程中强制使用特定字符集会很好,但如果客户端期待其他内容,则可能会导致问题.我会说你最好在客户端(你的应用程序)上指定它.

---

`skip-character-set-client-handshake`究竟做了什么,在什么情况下需要它？

---

在MySQL 5.6上,skip-character-set-client-handshake并没有像@Vaughany所说的那样做.有什么建议吗？

4> basic6..：

---

这个问题已经有了很多答案,但Mathias Bynens提到应该使用'utf8mb4'代替'utf8'以获得更好的UTF-8支持('utf8'不支持4字节字符,字段在插入时被截断).我认为这是一个重要的区别.所以这是关于如何设置默认字符集和排序规则的另一个答案.一个允许你插入一堆便便()的人.

这适用于MySQL 5.5.35.

请注意,某些设置可能是可选的.由于我不完全确定我没有忘记任何事情,我会将此答案作为社区维基.

旧设置

mysql> SHOW VARIABLES LIKE 'char%'; SHOW VARIABLES LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

配置

#  
# UTF-8 should be used instead of Latin1. Obviously.
# NOTE "utf8" in MySQL is NOT full UTF-8: http://mathiasbynens.be/notes/mysql-utf8mb4

[client]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4

新设置

mysql> SHOW VARIABLES LIKE 'char%'; SHOW VARIABLES LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

---

character_set_system 总是utf8.

这不会影响现有表,它只是默认设置(用于新表).以下ALTER代码可用于转换现有表(不使用dump-restore解决方法):

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

---

编辑:

在MySQL 5.0服务器上:character_set_client,character_set_connection,character_set_results,collat​​ion_connection保留在latin1.发布SET NAMES utf8(在该版本中不提供utf8mb4)也将这些设置为utf8.

---

警告:如果你有一个带有VARCHAR(255)类型索引列的utf8表,在某些情况下它无法转换,因为超出了最大密钥长度(Specified key was too long; max key length is 767 bytes.).如果可能,将列大小从255减小到191(因为191*4 = 764 <767 <192*4 = 768).之后,表格可以转换.

5> М.Б...：

---

注意: my.cnf文件位于/etc/mysql/

添加这些行后:

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
skip-character-set-client-handshake

[client]
default-character-set   = utf8

[mysql]
default-character-set   = utf8

别忘了重启服务器:

sudo service mysql restart

---

注意:这会更改默认值; 它确实_not_更改任何现有列的编码.

6> Derek..：

---

NijaCat很接近,但指定过度杀伤:

要将默认值设置为UTF-8,您需要将以下内容添加到my.cnf

[client]
default-character-set=utf8

[mysqld]
default-character-set = utf8

然后,验证:

mysql> show variables like "%character%";show variables like "%collation%";

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

---

很好的答案.也许关于哪些部分过度的简短解释将有助于人们在他们的配置中确切地确定他们想要什么.

7> amiroff..：

---

我还发现,设置后default-character-set = utf8在[mysqld]标题中,MySQL的5.5.x不会下启动的Ubuntu 12.04(精确穿山甲).

---

那是[MySQL bug#52047](http://bugs.mysql.com/bug.php?id=52047).在`[mysqld]`下,你应该使用`character-set-server`而不是`default-character-set`.(非常困惑,我同意!)

8> mug896..：

---

MySQL v5.5.3及更高版本:

只需在[mysqld]部分添加三行:

[mysqld]
character-set-server = utf8
collation-server = utf8_unicode_ci
skip-character-set-client-handshake

注:包括skip-character-set-client-handshake这里省却了包括init-connect在[mysqld]和default-character-set中[client]和[mysql]节.

9> 小智..：

---

在Xubuntu 12.04下我简单地补充道

[mysqld]
character_set_server = utf8

到/etc/mysql/my.cnf

结果是

mysql> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

另请查看http://dev.mysql.com/doc/refman/5.6/en/charset-server.html

10> gahcep..：

---

此处列出的所有设置都是正确的,但这里是最佳且充分的解决方案:

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
character-set-server = utf8
collation-server = utf8_unicode_ci

[client]
default-character-set = utf8

添加这些/etc/mysql/my.cnf.

请注意,由于性能问题,我选择utf8_unicode_ci类型的排序规则.

结果是:

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

这是当您作为非SUPER用户连接时!

例如,作为SUPER和非SUPER用户的连接之间的区别(当然在utf8_unicode_ci整理的情况下):

超级私人用户:

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci | <---
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

非SUPER私人用户:

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

我写了一篇全面的文章(rus),详细解释了为什么你应该使用一个或另一个选项.考虑所有类型的字符集和排序规则:用于服务器,用于数据库,用于连接,用于表,甚至用于列.

我希望这篇文章和文章将有助于澄清不明确的时刻.

---

我希望我能阅读您的链接文章,但无法阅读俄文和谷歌翻译对技术文本没有帮助.据我所知,虽然这会非常有趣.你能考虑发一个英文版吗？

---

你能解释哪个设置无法设置SUPER用户连接整理？(有解决方案)

11> Loren..：

---

该指令已更改为 character-set-system=utf8

http://dev.mysql.com/doc/refman/5.6/en/charset-configuration.html