mysql创建表后不能插入中文，这是为何？

(一)遇到如下错误的时候，了解字符集就很有必要啦

ERROR 1366 (HY000): Incorrect string value: ‘\xE9\x83\x91\xE5\xB7\x9E’ for column ‘aa’ at row 1

mysql> use test
Database changed
mysql> create table zgy(aa char(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into zgy values('zgzg');
Query OK, 1 row affected (0.01 sec)

mysql> insert into zgy values('郑州');
ERROR 1366 (HY000): Incorrect string value: '\xE9\x83\x91\xE5\xB7\x9E' for column 'aa' at row 1
mysql> show variables like '%char%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

我们可以通过以下的操作去改变mysql的字符集

set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_results=utf8;
set character_set_server=utf8;
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;
set character_set_server=utf8;

mysql> set character_set_database=utf8;
mysql> show variables like'%char%';
+--------------------------+----------------------------------+
| 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/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

仍然报错？

mysql> insert into zgy values('郑州');
ERROR 1366 (HY000): Incorrect string value: '\xE9\x83\x91\xE5\xB7\x9E' for column 'aa' at row 1

这时候只是将DB的改成utf8了，而zgy表是之前创建的

mysql> alter database test character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table zgy;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------+
| zgy | CREATE TABLE `zgy` (
`aa` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

改变表的字符集，发现还不行

mysql> alter table zgy character set utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into zgy values('郑州');
ERROR 1366 (HY000): Incorrect string value: '\xE9\x83\x91\xE5\xB7\x9E' for column 'aa' at row 1
mysql>
mysql> show create table zgy;
+-------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------+
| zgy | CREATE TABLE `zgy` (
`aa` char(20) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改字段的字符集
创建库时默认已经指定了表和字段等对象的字符集

mysql> ALTER TABLE zgy CHANGE aa aa char(20) CHARACTER SET utf8;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> show create table zgy;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| zgy | CREATE TABLE `zgy` (
`aa` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into zgy values('郑州');
Query OK, 1 row affected (0.01 sec)

mysql> insert into zgy values('深圳');
Query OK, 1 row affected (0.00 sec)

mysql> select * from zgy;
+--------------+
| aa |
+--------------+
| zgzg |
| asasas121212 |
| 郑州 |
| 深圳 |
+--------------+
4 rows in set (0.00 sec)

小常识：
看到是gbk和latin1说明不支持中文，所以需要修改字符类型
在latin1中，每个字符只有一个字节长。在utf8中，一个字符可以由多个字节组成。因此，utf8具有比latin1更多的字符（并且它们具有的字符不一定由相同的字节/字节序列表示）。但是我们的中文汉字是要2个字节还是3个字节哦，所有latin1就不能存储汉字啦。

优点：一劳永逸，在创建数据库时就设置为utf8格式，以后再次数据库下创建表时以及添加表数据时都不用担心中文字符问题。

mysql>CREATE database testdb DEFAULT CHARACTER SET utf8;

（二）一些关于查看和修改字符集的MySQL知识

1、查看mysql的字符集

show variables like '%char%';

2、查看某一个数据库字符集

show create database enterprises;

3、查看某一个数据表字符集

show create table employees;

4、修改mysql的字符集

set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_results=utf8;
set character_set_server=utf8;
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;

5、修改数据库的字符集

alter database enterprises character set utf8

6、修改数据表的字符集

alter table employees character set utf8

7、修改字段的字符集

alter table employees change name name char(10) character set utf-8;