今天使用sqlalchemy往mysql数据库里插入数据的时候，发生了错误，因为字段包含中文，所以报错。在问题解决之后做一个总结。 mysql中文乱码包括几个方面。mysql服务器层面、mysql数据库层面、客户端层面、表层面。所以要解决mysql的中文乱码，需要从这几个层面入手。 1.在创建表的时候指定编码格式 创建测试用表，填充数据，插入中文报错
mysql> create table test(
-> id int,
-> name varchar(),
-> primary key(id)
-> );
Query OK, rows affected (0.06 sec)mysql> insert into test(id, name) values(, "你好");
ERROR (HY000): Incorrect string value: '\xC4\xE3\xBA\xC3' for column 'name' at row
查看表字符编码，可以看到字符编码是latin1
mysql> show create table test \G;
*************************** . row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int() NOT NULL DEFAULT '',
`name` varchar() DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
row in set (0.00 sec)
创建另一个测试用表，指定编码格式
mysql> CREATE TABLE XXX(
-> `id` int() NOT NULL DEFAULT '',
-> `name` varchar() DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf-
-> ;mysql> insert into XXX(id, name) values(, "你好");
Query OK, row affected (0.01 sec)
2.在创建数据库时指定编码格式
mysql> create database mydb character set utf8;
Query OK, row affected (0.00 sec)
这样在mydb数据库里创建的表默认都是utf8格式的编码 3.在当前会话中更改编码格式 查看数据库编码
mysql> show variables like "%char%";
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.6\share\charsets\ |
+--------------------------+---------------------------------------------------------+
rows in set (0.00 sec)
修改字符编码
mysql> set character_set_server=utf8;
mysql> set character_set_database=utf8;
Query OK, rows affected (0.00 sec)mysql> show variables like "%char%";
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.6\share\charsets\ |
+--------------------------+---------------------------------------------------------+
rows in set (0.00 sec)
可以看到，在上面的命令起作用之后，character_set_server和character_set_database的编码格式都变成了utf8，也就是说，mysql服务器和数据库的字符编码都是utf8了。但是要注意的是这个命令只对当前的会话起作用。 4.修改my.ini配置文件，一劳永逸解决mysql中文乱码的问题 找到my.ini配置文件，windows系统下mysql的my.ini文件放在mysql安装目录的根目录下，做出下面的修改。
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci[mysql]
default-character-set=utf8
重启mysql，进入mysql查看字符集
mysql> show variables like "%char%";
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.6\share\charsets\ |
+--------------------------+---------------------------------------------------------+
rows in set (0.00 sec)
