ERROR 1366 (HY000): Incorrect string value（5.7.25)

出现这种错误的两种情况：建议将字符串编码改为utf8
1.在数据库建立时，建表后要插入中文记录时；(操作前没有将环境字符串编码改为为gbk或utf8）
2.建立了数据库后，且表中已经有中文记录，需要对数据库，表进行操作使用到中文时。（因为每次重新开启MYSQL就会将字符串编码改为默认）

使用： show variables like’%char%’;
一般会有两个是拉丁文的；

方法一：

在开启mysql后,直接建库，建表；
输入下列命令，出错ERROR 1366 (HY000):

create database cf;
use cf;
create table cff(name varchar(13) not null);
insert into cff values(‘王健林’);

出现错误：
ERROR 1366 (HY000): Incorrect string value: ‘\xE7\x8E\x8B\xE5\x81\xA5…’ for column ‘name’ at row 1

这是为什么?很简单，应因为数据库和表中都是latin1 编码；
输入命令
show create database cf;
show create table cff;

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

mysql> show create table cff;
±------±------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±------------------------------------------------------------------------------------------+
| cff | CREATE TABLE cff (
name varchar(13) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
±------±------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)show create table cff;

那么,该如何改变它的字符串编码呢？输入命令
alter database cf character set ‘gbk’

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

可知，数据库cf编码改为了gbk，那么，接下来在这个数据库建立的表的编码都是gbk;

mysql>create table dff(name varchar(13) not null);
Query OK, 0 rows affected (0.03 sec)
mysql> show create table dff;
±------±---------------------------------------------------------------------------------------+
| Table | Create Table |
±------±---------------------------------------------------------------------------------------+
| dff | CREATE TABLE dff (
name varchar(13) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
±------±---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into dff values(‘王健林’);
Query OK, 1 row affected (0.01 sec)
mysql> select * from dff;
±----------+
| name |
±----------+
| 王健林 |
±----------+
1 row in set (0.00 sec)

同理，已经在cf中建好的表可以用命令：alter table cff character set ‘gbk’;

mysql> alter table cff character set ‘gbk’;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into cff values(‘王健林’);
ERROR 1366 (HY000): Incorrect string value: ‘\xE7\x8E\x8B\xE5\x81\xA5…’ for column ‘name’ at row 1

但是，错了，再使用show create table cff; ，你会发现，name列的编码是latin1

mysql> show create table cff;
±------±------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±------------------------------------------------------------------------------------------------------------+
| cff | CREATE TABLE cff (
name varchar(13) CHARACTER SET latin1 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
±------±------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

再使用命令：alter table cff change name name varchar(13) character set gbk;

mysql> alter table cff change name name varchar(13) character set gbk;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

再使用：show create table cff; 会发现latin1不见了,说明当列的编码与表不同是，它会在列属性上显示；

mysql> show create table cff;
±------±-------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±-------------------------------------------------------------------------------------------+
| cff | CREATE TABLE cff (
name varchar(13) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
±------±-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into cff values(‘王健林’);
Query OK, 1 row affected (0.01 sec)
mysql> select * from cff;
±----------+
| name |
±----------+
| 王健林 |
±----------+
1 row in set (0.00 sec)

方法2：情况一可以在建库前避免，使用下列语句：（一般只要前两个即可，也可以使用utf8）,也可以将前7个都改掉，set character_set_XXXX=‘gbk’;(XXXX为不同的单词)

set character_set_database=‘gbk’;
set character_set_server=‘gbk’;
set character_set_client = ‘gbk’ ;
set character_set_connection = ‘gbk’ ;
show variables like’%char%’;

如果已经建库了且不想删库重建，那么可以与情况2用第一个方法
两种情况都可以用一种方式解决；

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 | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
±-------------------------±--------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
| xscj |
| yggl |
±-------------------+
8 rows in set (0.00 sec)

已知有数据库yggl，需要对它建立储存过程

mysql> use yggl;
Database changed
mysql> Delimiter $$
mysql> Create procedure delete_person(in name varchar(20),out x int(1))
    -> Begin
    -> Declare name2 varchar(10);
    -> Delete from employees where 姓名=name;
    -> Select 姓名 into name2 from employees where 姓名=name;
    -> If name2 is null then set x=1;
    -> Else set x=0;
    -> End if;
    -> End$$
Query OK, 0 rows affected (0.00 sec)
mysql> Delimiter ;

》在输入命令Call delete_person(‘李丽’,@a);
出现ERROR 1366 (HY000): Incorrect string value
说明要改变编码

alter database yggl character set utf8;
use yggl;
alter table employees character set utf8;
alter table salary character set utf8;
alter table departments character set utf8;

因为在建表时已经将列的编码设置为gbk，故现在不用重新设置

mysql> Call delete_person(‘李丽’,@a);
Query OK, 0 rows affected (0.01 sec)
mysql> Select @a;
±-----+
| @a |
±-----+
| 1 |
±-----+
1 row in set (0.00 sec)