mysql 5.7.17 字符集设置_mysql 5.7.17 设置字符集为UTF-8

今天使用mysql进行中文插入数据的时候，系统报错，确认是编码问题后开始修改

ERROR 1366 (HY000): Incorrect string value:XXX

目前网上的通用版本是分别修改/etc/mysql/my.cnf文件中的[client]、[musqld]、[mysql]三个部分，添加utf8设置

[client]

port = 3306

socket = /var/lib/mysql/mysql.sock

default-character-set=utf8

[mysqld]

port = 3306

socket = /var/lib/mysql/mysql.sock

character-set-server=utf8

[mysql]

no-auto-rehash

default-character-set=utf8

然后重启服务

sudo /etc/init.d/mysql restart

但自己尝试的时候，发现在/etc/mysql/my.cnf文件跟示例不一样，根本没有[client]、[musqld]、[mysql]三个部分，而是如下：

#

2 # The MySQL database server configuration file.

3 #

4 # You can copy this to one of:

5 # - "/etc/mysql/my.cnf" to set global options,

6 # - "~/.my.cnf" to set user-specific options.

7 #

8 # One can use all long options that the program supports.

9 # Run program with --help to get a list of available options and with

10 # --print-defaults to see which it would actually understand and use.

11 #

12 # For explanations see

13 # http://dev.mysql.com/doc/mysql/en/server-system-variables.html

14

15 # This will be passed to all mysql clients

16 # It has been reported that passwords should be enclosed with ticks/quotes

17 # escpecially if they contain "#" chars...

18 # Remember to edit /etc/mysql/debian.cnf when changing the socket location.

19 !includedir /etc/mysql/conf.d/

~

文件结构也有所不同

.

├── conf.d

│ ├── msql.cnf

│ └── mysqldump.cnf

├── debian.cnf

├── debian-start

├── my.cnf -> /etc/alternatives/my.cnf

├── my.cnf.fallback

├── mysql.cnf

└── mysql.conf.d

├── mysqld.cnf

└── mysqld_safe_syslog.cnf

查了半天才发现，原来mysqld直接放在了mysql.conf.d文件夹下的mysqld.cnf里面，打开文件备注里面写到

# You can copy this to one of:

# - "/etc/mysql/my.cnf" to set global options,

# - "~/.my.cnf" to set user-specific options.

于是返回上一层目录，将my.cnf重新配置，添加了utf8设置

...

18 # Remember to edit /etc/mysql/debian.cnf when changing the socket location.

19

20 [client]

21 ##port = 3306

22 ##socket = /var/lib/mysql/mysql.sock

23 default-character-set=utf8

24

25 [mysqld]

26 character-set-server=utf8

27 collation-server=utf8_general_ci

28

29 [mysql]

30 no-auto-rehash

31 default-character-set=utf8

...

重启mysql服务之后，发现终于修改完成了。这么一个简单的事情，花费了近三个小时，惭愧~~

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

+--------------------------+----------------------------+

8 rows in set (0.01 sec)

ps. 在设置utf8之前，如果已经创建了数据库，那之前的数据库编码依旧保持之前的状态，直接插入中文仍旧会报错。最好重新创建，并在数据库创建的时候就指明编码状态。

CREATE DATABASE mydb

DEFAULT CHARACTER SET utf8

DEFAULT COLLATE utf8_general_ci;