mysql 插入汉字 Incorrect string value 解决办法

项目开发的时候，由网页表单提交的过来的中文字符，会报Incorrect string value: '\xF0\x90\x8D\x83\xF0\x90...' 异常；

原因是mysql的编码造成，使用 show variables like 'character%' 查看数据库的编码：

mysql> show variables like 'character%'
    -> ;
+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | latin1                                                 |
| character_set_connection | latin1                                                 |
| character_set_database   | latin1                                                 |
| character_set_filesystem | binary                                                 |
| character_set_results    | latin1                                                 |
| character_set_server     | latin1                                                 |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.1.63-osx10.6-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+
8 rows in set (0.00 sec)

 

网上找了些解决方案：修改字段或者表的字符集为utf8

方案1，转换需要插入汉字的数据表编码为utf8

alter table sysadmin_article convert to character set utf8;
Query OK, 16 rows affected (0.35 sec)
Records: 16  Duplicates: 0  Warnings: 0

方案2，更改数据库的编码格式，再重新建表

alert database fruitdata character set utf8;