MySQL 如何在 my.ini 中将 character_set_database 和 collation_database 设置为 utf8？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22572558/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set character_set_database and collation_database to utf8 in my.ini?
提问by Judking
I've googled a lot about this problem. To sum up, this is what my my.inilooks like:
我在谷歌上搜索了很多关于这个问题的信息。总结一下，这就是我的my.ini样子：
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[client]
database = abcdef
user = root
password = XXXXXX
default-character-set = utf8
[mysql]
default-character-set=utf8
[mysqld]
character_set_server=utf8
max_connections = 200
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
When I get into mysql via cmd and issue: show variables like "%character%";show variables like "%collation%";, this is what I got:
当我通过 cmd 进入 mysql 并发出：时show variables like "%character%";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 | utf8 |
| character_set_system | utf8 |
| character_sets_dir | D:\env\MySQL5.6\share\charsets\ |
+--------------------------+---------------------------------+
8 rows in set (0.00 sec)
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-------------------+
I've already restart the MySQL service, so could anyone give me some idea on how to change character_set_databaseas well as collation_databaseto utf8? Thanks a lot.
我已经重启MySQL服务，所以任何人都可以给我如何改变一些想法character_set_database，以及collation_database对utf8？非常感谢。
回答by Chris J
This actually isn't a setting in the my.cnf(or my.iniin this case). mySQL gets this setting from the database's own collation (when it was created). Inorder to get this inline with the utf8 encoding you want, do this:
这实际上不是my.cnf（或my.ini在这种情况下）中的设置。mySQL 从数据库自己的排序规则（创建时）获取此设置。为了使其与您想要的 utf8 编码内联，请执行以下操作：
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_general_ci;
then do a restart on mysql (cant remember if its needed though), followed by a:
然后在 mysql 上重新启动（虽然不记得是否需要），然后是：
SHOW VARIABLES;
All should be well, Hope that helps!
一切都应该很好，希望有帮助！
side note: i think default-character-setis deprecated now-a-days (mySQL 5.5+) and seems to make the config file fidgety.
旁注：我认为default-character-set现在不推荐使用（mySQL 5.5+）并且似乎使配置文件烦躁。
回答by tinyhare
I do a summary:
我做一个总结：
determine which charset/collations are available
确定哪些字符集/排序规则可用
SHOW CHARSET;
SHOW COLLATION;
check charset
检查字符集
SHOW VARIABLES LIKE '%character%';
SHOW VARIABLES LIKE '%collation%';
set charset (in configure file -> my.cnf)
设置字符集（在配置文件 -> my.cnf 中）
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
check database/table charset
检查数据库/表字符集
SHOW CREATE DATABASE databasename;
SHOW CREATE TABLE tablename;
change the database/table charset：
更改数据库/表字符集：
ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
set when create database/table:
创建数据库/表时设置：
CREATE DATABASE new_db CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
CREATE TABLE new_table (id INT) CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
Note： I heard that in Mysql utf8 is not the true utf8，utf8mb4 is the real utf8. so, if you have special character that can't save to mysql, maybe you should use utf8mb4 and utf8mb4_general_ci
注意：听说在Mysql中utf8不是真正的utf8，utf8mb4才是真正的utf8。因此，如果您有无法保存到 mysql 的特殊字符，也许您应该使用 utf8mb4 和 utf8mb4_general_ci
回答by Vipin Jain
How to configure Character Setand Collation.
如何配置Character Set和Collation.
For applications that store data using the default MySQL character set and collation (
latin1, latin1_swedish_ci), no special configuration should be needed. If applications require data storage using a different character set or collation, you can configure character set information several ways:
对于使用默认 MySQL 字符集和排序规则 (
latin1, latin1_swedish_ci)存储数据的应用程序，不需要特殊配置。如果应用程序需要使用不同的字符集或排序规则存储数据，您可以通过多种方式配置字符集信息：
- Specify character settings per database. For example, applications
that use one database might require
utf8, whereas applications that use another database might require sjis. - Specify character settings at server startup. This causes the server to use the given settings for all applications that do not make other arrangements.
- Specify character settings at configuration time, if you build MySQL from source. This causes the server to use the given settings for all applications, without having to specify them at server startup.
- 指定每个数据库的字符设置。例如，使用一个数据库的应用程序可能需要
utf8，而使用另一个数据库的应用程序可能需要 sjis。 - 在服务器启动时指定字符设置。这会导致服务器对所有不进行其他安排的应用程序使用给定的设置。
- 如果您从源代码构建 MySQL，请在配置时指定字符设置。这会导致服务器对所有应用程序使用给定的设置，而不必在服务器启动时指定它们。
The examples shown here for your question to set utf8 character set , here also set collation for more helpful(utf8_general_cicollation`).
此处显示的示例用于设置 utf8 字符集的问题，此处还设置了更有用的utf8_general_ci排序规则（排序规则）。
Specify character settings per database
指定每个数据库的字符设置
CREATE DATABASE new_db
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
Specify character settings at server startup
在服务器启动时指定字符设置
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
Specify character settings at MySQL configuration time
在 MySQL 配置时指定字符设置
shell> cmake . -DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
This May be lengthy answer but there is all way, you can use. Hopeful my answer is helpful for you. for more information http://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
这可能是冗长的答案，但有所有方法，您可以使用。希望我的回答对你有帮助。更多信息http://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
