ERROR 1366 (HY000): Incorrect string value: '\xE4\xB8\x8D\xE5\xA5\xBD' for column 'description' at r

一：问题描述

更新字段为中文内容时，报错：

mysql>  update t set description='不好' where id=11;
ERROR 1366 (HY000): Incorrect string value: '\xE4\xB8\x8D\xE5\xA5\xBD' for column 'description' at row 1

二：出错原因

该表和字段的字符集不支持中文

三：解决办法

将表和字段的字符集改为支持中文的字符集如utf8，并和变量like 'char%'的字符集保持一致

四：详细步骤

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.00 sec)

--查看该表字符集

mysql> show create table t;
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

--查看该字段字符集

mysql> show full columns from t;
+-------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field       | Type         | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |
+-------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| id          | int(11)      | NULL              | YES  |     | NULL    |       | select,insert,update,references |         |
| description | varchar(100) | latin1_swedish_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)

--修改表字符集为utf8

mysql> alter table t default character set utf8;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL,
  `description` varchar(100) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

--修改该字段字符集为utf8

mysql> alter table t change description description varchar(100)  character set utf8;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

--现在再次修改字段内容为中文时，就不报错了，如下：

mysql> update t set description ='不好' where id=12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 

mysql> select *
    -> from t;
+------+-------------+
| id   | description |
+------+-------------+
|   11 | good        |
|   12 | 不好        |
|   13 | NULL        |
+------+-------------+
3 rows in set (0.00 sec)