ERROR 1366 (HY000): Incorrect string value: '\xE4\xBD\xA0\xE5\xA5\xBD' for column 'name' at row 1

来源：互联网发布：sql trigger update 编辑：程序博客网时间：2024/04/24 10:42

一：问题描述

mysql> insert into t2(id,name) values(1,'你好');

二：出错原因

字符集不支持中文。

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

字符集为latin1,故不能插入中文数据。

三：解决办法

可以将表字符集和相应的列字符集改为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(11) DEFAULT NULL,  `name` char(30) CHARACTER SET latin1 DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+-------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec) mysql> insert into t2(id,name) values(2,'你好');ERROR 1366 (HY000): Incorrect string value: '\xE4\xBD\xA0\xE5\xA5\xBD' for column 'name' at row 1 还是不行。这是因为之前表字符集是latin1,列的字符集没有设置，默认也跟表一样，也是latin1,故也需要修改列的字符集：

mysql> alter table t2 modify name char(30) 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(11) DEFAULT NULL,  `name` char(30) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+----------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec) 现在再次插入，就不再报错了。

mysql> insert into t2(id,name) values(3,'你好');Query OK, 1 row affected (0.04 sec)

0 0