MySQL: 修改默认编码, MySQL / MariaDB: Set Character Set and Collation to UTF8, Mysql2::Error: Incorrect string value: ‘\xE2\x80\xA8\x09

发现phpmyadmin默认是latin字符编码，导致某些特殊字符，或者乱码字符无法保存，常有的报错类似：

Mysql2::Error: Incorrect string value: '\xE2\x80\xA8\x09

1. 那么首先看一下我们的mysql默认字符是什么：  

sudo mysql

mysql> status
--------------
mysql  Ver 14.12 Distrib 5.0.45, for Win32 (ia32)

Connection id:          25
Current database:       spiel0
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.45-community-nt MySQL Community Edition (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Insert id:              1
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 32 min 33 sec

Threads: 3  Questions: 3185  Slow queries: 0  Opens: 22  Flush tables: 2  Open tables: 3  Queries per second avg: 1
.631
--------------

或者直接通过下面的命令查看：

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)

mysql>

2. 你会发现，原来是编码错误，修改方法如下：

To set the mysql server to utf8, add the following to /etc/mysql/my.cnf (debian/ubuntu) or /etc/my.cnf (fedora/centos/rhel) under the [mysqld] section and restart mysql/mariadb:

我使用的是ubuntu：

sudo vi /etc/mysql/my.cnf

加入内容：

[mysqld]
collation-server=utf8_unicode_ci
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]
default-character-set=utf8

这个操作是修改mysql设置，如果你不想修改server，而是是用自己的.my.cnf的话，也可以参看：Shell: 无密码读写Mysql数据库, 无密码被分导出Mysql数据库, mysqldump , Connect to MySQL Without Root Password on Terminal

然后重启mysql

sudo service mysql restart

最后再确认一下：Check to make sure the change went through ok:

mysql> show variables like 'char%'; 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)

mysql>

3. 当然，如果你无法修改server文件的话，最后一个笨办法就是，在phpmyadmin里面直接运行sql语句：

ALTER TABLE your_database_name.your_table CONVERT TO CHARACTER SET utf8

本文：MySQL: 修改默认编码, MySQL / MariaDB: Set Character Set and Collation to UTF8, Mysql2::Error: Incorrect string value: ‘\xE2\x80\xA8\x09