mysql插入中文字符失败修改字段字符集为utf-8插入中文正常

mysql插入中文字符失败

ERROR 1366 (22007): Incorrect string value: \
'\xE6\x9D\x8E' for column `poetry_db`.`author`.`name` at row 1

更改database的charset

用alter database dbname default character set 'utf8';修改database的charset

$ show create database poetry_db;
+-----------+----------------------------------------------------------------------+
| Database  | Create Database                                                      |
+-----------+----------------------------------------------------------------------+
| poetry_db | CREATE DATABASE `poetry_db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+-----------+----------------------------------------------------------------------+
$ alter database poetry_db default character set 'utf8';
$ show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |

发现以下

| character_set_database   | utf8                       |

还是插入报错

更改table的charset

用alter table tbname default character set 'utf8';修改table的charset

$ show create table author;
+--------+----+
| Table  | Create Table|
+--------+-----+
| author | CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 |

$ alter table author default character set 'utf8'; 

$ show create table author;
+--------+----+
| Table  | Create Table|
+--------+-----+
| author | CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 |

还是插入报错

更改表字段的charset

用```alter table tbname change fieldname fieldname xxx character set utf8 xxx;``修改字段`

$ alter table author change name name varchar(16) character set utf8 default null;

$ insert into author(name) values("李");
Query OK, 1 row affected (0.063 sec)

终于插入中文字符正常了