2018-08-18 MySQL之ERROR 1366 (HY0

如图所示：

image.png

mysql> insert into user1(name,password,email,age) values('振松', md5('123456'),'
zs@sohu.com',45);
ERROR 1366 (HY000): Incorrect string value: '\xD5\xF1\xCB\xC9' for column 'name'
 at row 1

这是因为我的name字段接受的字符集与传入的字符不匹配。
修改方法如下：
1.查看现状：

mysql> show create table user1;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------+
| Table | Create Table


                                               |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------+
| user1 | CREATE TABLE `user1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `password` varchar(64) NOT NULL,
  `email` varchar(128) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----------------------------------------------+
1 row in set (0.00 sec)

2.切换对应字段编码

mysql> alter table user1 modify name varchar(20) character set utf8;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table user1;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------+
| Table | Create Table


                                                                      |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------+
| user1 | CREATE TABLE `user1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `password` varchar(64) NOT NULL,
  `email` varchar(128) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------+
1 row in set (0.00 sec)

3.再次尝试插入中文

mysql> insert into user1(name,password,email,age) values('振松', md5('123456'),'
zs@sohu.com',45);
Query OK, 1 row affected (0.00 sec)

mysql> select *from user1;
+----+----------+----------------------------------+--------------+-----+
| id | name     | password                         | email        | age |
+----+----------+----------------------------------+--------------+-----+
|  1 | xiaoming | 202cb962ac59075b964b07152d234b70 | xm@souhu.com |  34 |
|  2 | 振松     | e10adc3949ba59abbe56e057f20f883e | zs@sohu.com  |  45 |
+----+----------+----------------------------------+--------------+-----+
2 rows in set (0.00 sec)

搞定，如果你明确此问题，那么可以直接操作2步骤。