mysql无法插入中文解决方案

在插入中的时候报错：

mysql> insert into product (name,price) values('无人机',1000);
ERROR 1366 (HY000): Incorrect string value: '\xE6\x97\xA0\xE4\xBA\xBA...' for column 'name' 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/soft/mysql/share/charsets/ |
+--------------------------+---------------------------------+
8 rows in set (0.00 sec)

在第三行character_set_database为latin1，修改字符集为utf-8：

mysql> alter database test default character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)

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/soft/mysql/share/charsets/ |
+--------------------------+---------------------------------+
8 rows in set (0.00 sec)

修改字符集完成，重新插入数据，结果依旧报错：

mysql> insert into product (name,price) values('无人机',1000);
ERROR 1366 (HY000): Incorrect string value: '\xE6\x97\xA0\xE4\xBA\xBA...' for column 'name' at row 1

感觉有点问题，数据库的字符集已经改过来了，为啥还是不好用呢？我又重新查了一下表的字符集，发现表的字符集竟然没有变(test是数据库名，product是表名)：

mysql> show table status from test like 'product'\G
*************************** 1. row ***************************
           Name: product
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 2
    Create_time: 2019-07-27 22:29:27
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment：
1 row in set (0.00 sec)

到这里，我又想起来表的列好像也有自己的字符集，查了一下：

mysql> show full columns from product\G
*************************** 1. row ***************************
     Field: product_id
      Type: int(10) unsigned
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra: auto_increment
Privileges: select,insert,update,references
   Comment:
*************************** 2. row ***************************
     Field: name
      Type: varchar(10)
 Collation: latin1_swedish_ci
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 3. row ***************************
     Field: price
      Type: double
 Collation: NULL
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
3 rows in set (0.00 sec)

果然在2. row中name那一列的Collation: latin1_swedish_ci的编码也是需要改的，开始进行修改：

mysql> ALTER TABLE product CONVERT TO CHARACTER SET utf8 COLLATTE utf8_general_ci;
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0
###
mysql> show table status from test like 'product'\G
*************************** 1. row ***************************
           Name: product
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 2
    Create_time: 2019-07-27 23:22:31
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)
mysql> show full columns from product\G
*************************** 1. row ***************************
     Field: product_id
      Type: int(10) unsigned
 Collation: NULL
      Null: NO
       Key: PRI
   Default: NULL
     Extra: auto_increment
Privileges: select,insert,update,references
   Comment:
*************************** 2. row ***************************
     Field: name
      Type: varchar(10)
 Collation: utf8_general_ci
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 3. row ***************************
     Field: price
      Type: double
 Collation: NULL
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
3 rows in set (0.00 sec)

修改完毕，进行验证：

mysql> insert into product (name,price) values('无人机',1000);
Query OK, 1 row affected (0.01 sec)

问题解决！