ERROR 1366 (HY000): Incorrect string value: ' \XE4\XBD\XA0\XE5\XA5\XBD ' for column ' name ' at row 1

One: Problem description

mysql> INSERT INTO T2 (id,name) VALUES (1, ' hello ');

ERROR 1366 (HY000): Incorrect string value: ' \XE4\XBD\XA0\XE5\XA5\XBD ' for column ' name ' at row 1

Two: The cause of the error

The character set does not support Chinese.

Mysql> Show create table T2;
+-------+------------------------------------------------------------------------------------------------------ ------------------+
| Table | Create Table                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------ ------------------+
| T2    | CREATE TABLE ' T2 ' (
  ' id ' int () default null,
  ' name ' char () default null
) Engine=innodb default Charset=la tin1 |
+-------+------------------------------------------------------------------------------------------------------ ------------------+
1 row in Set (0.00 sec)

The character set is Latin1, so the Chinese data cannot be inserted.

III: Solutions

You can change the table character set and the corresponding column character set to UTF8.

Mysql> ALTER TABLE T2 CharSet UTF8;
Query OK, 0 rows affected (0.05 sec) records:0 duplicates:0 warnings:0 mysql> Show create TABLE T2; +-------+------------------------------------------------------------------------------------------------------ -------------------------------------+
| Table |                                                                                                                              Create Table
| +-------+------------------------------------------------------------------------------------------------------ -------------------------------------+
| T2 | CREATE TABLE ' T2 ' (' id ' int () DEFAULT null, ' name ' char () CHARACTER SET latin1 DEFAULT null) Engine=innodb Defau
LT Charset=utf8 | +-------+------------------------------------------------------------------------------------------------------
-------------------------------------+ 1 row in Set (0.00 sec) mysql> inserts into T2 (Id,name) VALUES (2, ' hello '); ERROR 1366 (HY000): IncorRect string value: ' \XE4\XBD\XA0\XE5\XA5\XBD ' for column ' name ' at row 1 or not. This is because the table character set is Latin1, the character set of the column is not set, the default is the same as the table, it is also latin1, so you need to modify the character set of the column:

 mysql> ALTER TABLE T2 modify name char (a) CHARACTER SET UTF8 DEFAULT NULL;
Query OK, 1 row affected (2.04 sec) records:1 duplicates:0 warnings:0 mysql> Show create TABLE T2; +-------+------------------------------------------------------------------------------------------------------ ----------------+
| Table |
Create Table | +-------+------------------------------------------------------------------------------------------------------ ----------------+
| T2 |
CREATE TABLE ' T2 ' (' id ' int () default null, ' name ' char () default null) Engine=innodb default Charset=utf8 | +-------+------------------------------------------------------------------------------------------------------ ----------------+ 1 row in Set (0.00 sec) is now plugged in again and no longer complains. 

mysql> INSERT INTO T2 (id,name) VALUES (3, ' hello ');
Query OK, 1 row affected (0.04 sec)