一:问题描述
mysql> insert into t2(id,name) values(1,'你好');
ERROR 1366 (HY000): Incorrect string value: '\xE4\xBD\xA0\xE5\xA5\xBD' for column 'name' at row 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)