ERROR 1366 (HY000): Incorrect string value: ‘\xE7\x9A\x84‘ for column ‘name‘ at row 1

mysql> insert into t3(id,name) values(1,'的');

ERROR 1366 (HY000): Incorrect string value: '\xE7\x9A\x84' for column 'name' at row 1

检查数据库此字段的字符集是否与SQL语句传递数据的字符集相同；不相同则会引发MySQL1366错误。

mysql> show full columns from t3;
+-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type        | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| id    | int(11)     | NULL              | NO   | PRI | NULL    |       | select,insert,update,references |         |
| name  | varchar(30) | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+

看到name列是latin类型的，将name改成utf8：

mysql> alter table t3 modify name varchar(30) character set utf8;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> show full columns from t3;
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type        | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id    | int(11)     | NULL            | NO   | PRI | NULL    |       | select,insert,update,references |         |
| name  | varchar(30) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)


mysql>  insert into t3(id,name) values(3,'的');
Query OK, 1 row affected (0.00 sec)


mysql> select * from t3;
+----+------+
| id | name |
+----+------+
|  1 | dan  |
|  3 | 的   |

/*

也需要排查下mysql配置文件中[client]下的default-character-set，如果和远程连接的数据库的字符集不一致，也会出现该问题。

*/

--本篇文章参考自：http://blog.csdn.net/clare504/article/details/9569347