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

Question

提问by Jorre

Currently we are using the following commands in PHP to set the character set to UTF-8in our application.

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

Since this is a bit of overhead, we'd like to set this as the default setting in MySQL. Can we do this in /etc/my.cnf or in another location?

由于这有点开销，我们想将其设置为 MySQL 中的默认设置。我们可以在 /etc/my.cnf 或其他位置执行此操作吗？

SET NAMES 'utf8'
SET CHARACTER SET utf8

I've looked for a default charset in /etc/my.cnf, but there's nothing there about charsets.

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

At this point, I did the following to set the MySQL charset and collation variables to UTF-8:

此时，我执行以下操作将 MySQL 字符集和排序规则变量设置为 UTF-8：

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

Is that a correct way to handle this?

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

Answer 1

回答by NinjaCat

To set the default to UTF-8, you want to add the following to my.cnf

要将默认设置为 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

If you want to change the character set for an existing DB, let me know... your question didn't specify it directly so I am not sure if that's what you want to do.

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

Answer 2

回答by Mustafah

For the recent version of MySQL,

对于最新版本的 MySQL，

default-character-set = utf8

causes a problem. It's deprecated I think.

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

As Justin Ballsays in "Upgrade to MySQL 5.5.12 and now MySQL won't start, you should:

正如Justin Ball在“升级到 MySQL 5.5.12 并且现在 MySQL 无法启动”中所说的那样，您应该：

Remove that directive and you should be good.

Then your configuration file ('/etc/my.cnf' for example) should look like that:

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

Restart MySQL.

For making sure, your MySQL is UTF-8, run the following queries in your MySQL prompt:

First query:

 mysql> show variables like 'char%';

The output should look like:

 +--------------------------+---------------------------------+
 | 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/|
 +--------------------------+---------------------------------+

Second query:

 mysql> show variables like 'collation%';

And the query output is:

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

删除该指令，您应该会很好。

然后你的配置文件（例如'/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 |
 +----------------------+-----------------+

Answer 3

回答by tomazzlender

On MySQL 5.5 I have in my.cnf

在 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

Result is

结果是

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)

Answer 4

回答by basic6

This question already has a lot of answers, but Mathias Bynens mentioned that 'utf8mb4' should be used instead of 'utf8' in order to have better UTF-8 support ('utf8' does not support 4 byte characters, fields are truncated on insert). I consider this to be an important difference. So here is yet another answer on how to set the default character set and collation. One that'll allow you to insert a pile of poo ().

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

This works on MySQL 5.5.35.

这适用于 MySQL 5.5.35。

Note, that some of the settings may be optional. As I'm not entirely sure that I haven't forgotten anything, I'll make this answer a community wiki.

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

Old Settings

旧设置

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)

Config

配置

#  
# 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

New Settings

新设置

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 is always utf8.

character_set_system总是 utf8。

This won't affect existing tables, it's just the default setting (used for new tables). The following ALTER codecan be used to convert an existing table (without the dump-restore workaround):

这不会影响现有表，它只是默认设置（用于新表）。以下ALTER 代码可用于转换现有表（无需转储还原解决方法）：

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

Edit:

编辑：

On a MySQL 5.0 server: character_set_client, character_set_connection, character_set_results, collation_connection remain at latin1. Issuing SET NAMES utf8(utf8mb4 not available in that version) sets those to utf8 as well.

在 MySQL 5.0 服务器上：character_set_client、character_set_connection、character_set_results、collation_connection 保持在 latin1。发布SET NAMES utf8（utf8mb4 在该版本中不可用）也将它们设置为 utf8。

Caveat: If you had a utf8 table with an index column of type VARCHAR(255), it can't be converted in some cases, because the maximum key length is exceeded (Specified key was too long; max key length is 767 bytes.). If possible, reduce the column size from 255 to 191(because 191 * 4 = 764 < 767 < 192 * 4 = 768). After that, the table can be converted.

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

Answer 5

回答by М.Б.

Note:my.cnf file is located at /etc/mysql/

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

After adding these lines:

添加这些行后：

[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

Don't forget to restart server:

不要忘记重新启动服务器：

sudo service mysql restart

Answer 6

回答by Derek

NijaCat was close, but specified overkill:

NijaCat 很接近，但指定了矫枉过正：

To set the default to UTF-8, you want to add the following to my.cnf

要将默认设置为 UTF-8，您需要在 my.cnf 中添加以下内容

[client]
default-character-set=utf8

[mysqld]
default-character-set = utf8

Then, to verify:

然后，验证：

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)

Answer 7

回答by amiroff

I also have found out that after setting default-character-set = utf8under [mysqld]title, MySQL 5.5.x would not start under Ubuntu 12.04(Precise Pangolin).

我还发现default-character-set = utf8在[mysqld]标题下设置后，MySQL 5.5.x 不会在Ubuntu 12.04（精确穿山甲）下启动。

Answer 8

回答by mug896

MySQL v5.5.3 and greater:

MySQL v5.5.3 及更高版本：

Just add three lines only in the [mysqld] section:

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

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

Note:Including skip-character-set-client-handshakehere obviates the need to include both init-connectin [mysqld]and default-character-setin the [client]and [mysql]sections.

注意：skip-character-set-client-handshake在此处包含可以避免在和部分init-connect中[mysqld]和default-character-set中都包含。[client][mysql]

Answer 9

回答by Falko

Under Xubuntu 12.04 I simply added

在Xubuntu 12.04下我只是添加了

[mysqld]
character_set_server = utf8

to /etc/mysql/my.cnf

到 /etc/mysql/my.cnf

And the result is

结果是

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)

Also take a look at http://dev.mysql.com/doc/refman/5.6/en/charset-server.html

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

Answer 10

回答by gahcep

All settings listed here are correct, but here are the most optimal and sufficient solution:

这里列出的所有设置都是正确的，但这里是最优化和最充分的解决方案：

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

[client]
default-character-set = utf8

Add these to /etc/mysql/my.cnf.

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

Please note, I choose utf8_unicode_citype of collation due to the performance issue.

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

The result is:

结果是：

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 |
+----------------------+-----------------+

And this is when you connect as non-SUPER user!

这是当您以非超级用户身份连接时！

For example, the difference between connection as SUPER and non-SUPER user (of course in case of utf8_unicode_cicollation):

例如，作为超级用户和非超级用户的连接之间的区别（当然在utf8_unicode_ci排序规则的情况下）：

user with SUPERpriv.:

具有超级权限的用户：

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

user with non-SUPERpriv.:

具有非超级权限的用户：

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

I wrote a comprehensive article(rus) explaining in details why you should use one or the other option. All types of Character Setsand Collationsare considered: for server, for database, for connection, for table and even for column.

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

I hope this and the article will help to clarify unclear moments.

我希望这篇文章和文章将有助于澄清不清楚的时刻。

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

提问by Jorre

回答by NinjaCat

回答by Mustafah

回答by tomazzlender

回答by basic6

Old Settings

旧设置

Config

配置

New Settings

新设置

回答by М.Б.

回答by Derek

回答by amiroff

回答by mug896

回答by Falko

回答by gahcep

相关推荐

最近更新

标签

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

提问by Jorre

回答by NinjaCat

回答by Mustafah

回答by tomazzlender

回答by basic6

Old Settings

旧设置

Config

配置

New Settings

新设置

回答by М.Б.

回答by Derek

回答by amiroff

回答by mug896

回答by Falko

回答by gahcep

相关推荐

mysql 加载数据 infile 无法获取文件的统计信息 Errcode: 2

如何在 MySQL 中使用 ALTER 将 VARCHAR 类型更改为 DATETIME？

MySQL DROP 所有表，忽略外键

MySQL 使用多个“where”子句为每一行更新多行

相关推荐

最近更新

标签