错误
ERROR 1366 (HY000): Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for column 'name' at row 1
原因
字符集服务端和客户端采用的不是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.04 sec)
发现service端用的是latin1,所以会中文乱码。
修改本表的数据格式:
先用该命令将表中的数据清除:
mysql> truncate table user;
Query OK, 0 rows affected (0.04 sec)
修改表字段字符编码:
mysql> alter table user modify name char(20) character set gbk;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
还有一种方式是修改/etc/my.cnf这个配置文件中的client端和mysqld端,加入utf8字符集。参考如下方案修改: