Problems & Solutions -- 6. MySql 中文乱码问题

web表单中输入数据，使用python连接MySql 数据库，准备把表单数据写入到MySql时报错：

输入英文没有问题，输入中文出了问题。好久之后，才反应过来，是编码出了问题。

幸运的是，在网上找到了解决办法。

（1）首先，查看MySql中字符串编码。
发现character_set_database 和 character_set_server使用的时latin1编码，不是utf-8，不能兼容中文。

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

（2）在mysql中直接运行python代码中的mysql语句，一样的报错：

mysql> INSERT INTO words(word, meaning) VALUES('shanbay',' 扇贝');
ERROR 1366 (HY000): Incorrect string value: '\xE6\x89\x87\xE8\xB4\x9D' for column 'meaning' at row 1

（3）把MySql中的latin1编码设置成utf8：

mysql> set character_set_database='utf8';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set character_set_server='utf8';
Query OK, 0 rows affected (0.00 sec)

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/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

(4) 再写入数据，还是有问题：

mysql> INSERT INTO words(word, meaning) VALUES('shanbay','扇贝');
ERROR 1366 (HY000): Incorrect string value: '\xE6\x89\x87\xE8\xB4\x9D' for column 'meaning' at row 1

（5）重新建立一个数据库test，在建立数据库时配置好编码。中文数据能顺利写入到MySql中了：

mysql> CREATE DATABASE `test` CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.21 sec)

mysql> USE test;
Database changed
mysql> CREATE TABLE words (id INT(11) AUTO_INCREMENT PRIMARY KEY, word VARCHAR(200), meaning TEXT, create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.56 sec)

mysql> INSERT INTO words(word, meaning) VALUES('shanbay','扇贝');
Query OK, 1 row affected (5.17 sec)

mysql> SELECT * FROM words;
+----+---------+---------+---------------------+
| id | word    | meaning | create_date         |
+----+---------+---------+---------------------+
|  1 | shanbay | 扇贝    | 2017-08-14 11:16:24 |
+----+---------+---------+---------------------+
1 row in set (0.01 sec)