【实践】mysql解决特生僻汉字插入问题

【知识篇】

首先补习下有关mysql数据库的编码知识。

cmd登录之后，输入show variables like 'character%'; 命令，可以查看本机mysql数据库的编码设置：

 具体解释：

变量名	含义
character_set_server	默认的内部操作字符集
character_set_client	客户端来源数据使用的字符集，也就是客户端发过来的查询语句使用的什么字符集
character_set_connection	MySQL接受到用户查询后，按照character_set_client将其转化为character_set_connection设定的字符集。
character_set_results	查询结果编码的字符集
character_set_database	当前选中数据库的默认字符集
character_set_system	系统元数据(字段名等)字符集
collation_connection	执行字符比较时采用的编码规则

 

在mysql中，可以为数据库指定默认的字符编码，成为该数据库中每个新建表的默认字符编码集，但是对于已经建立的表则不受影响(这句话重要)。在新建一个表时，也可以使用诸如DEFAULT CHARSET=UTF8来指定表的字符编码。

如果要查看所有的字符集，用show character set语句。

字符集的设定不仅影响着存储，还会影响客户端和数据库服务器的通信，关于数据编码，mqsql中涉及到下面几个问题：

1、客户端发过来的数据使用什么字符集编码的？

2、接收到数据之后，应该用什么编码格式编码之后再将数据插入到mysql server中？

3、执行查询之后，查询出来的结果应该用什么编码集编码之后再返回？

4、数据库的各种表的数据，应该用什么字符集编码，以及它们用什么排序？

5、查询语句的字符串比较时，应该在哪一个标准里面来比较，比如：'Mueller' = 'Müller'是为真还是假？

6、数据库的各种元数据，包括表名、数据库名、密码、用户名、以及comment等，用什么字符集表示？

 

在数据库的查询（select update insert）操作中，涉及到的字符编码有character_set_client, character_set_connnection, character_set_result三个变量，这是三个变量是需要建立连接之后进行设置的.

1、针对第一个问题，使用character_set_client环境变量来回答：

character_set_client，这是用户告诉服务器，客户端发过来的SQL语句是用的什么字符集，要和客户端发出去的字节流采用的编码集一致，如果是shell，那么就是和shell的编码集一致，中文windows的cmd就是gbk。但是对于使用_utf8'xxx'标记的字符，则用标记的字符集解码。

 

2、针对第二个问题，使用character_set_connetion环境变量来回答：

character_set_connection，MySQL server接收到用户查询后，按照character_set_client将其转化为character_set_connection设定的字符集，一般就是所操作的表对应的编码集。

 

3、针对第三个问题，使用character_set_result环境变量来回答：

character_set_results， MySQL将存储的数据转换成character_set_results中设定的字符集发送给用户，客户端获取到的结果就是以这种形式编码的。

 

4.针对第四个问题，使用上面提到的四个等级的默认字符集以及排序规则，即character_set_server、 character_set_database以及建立表时的DEFAULT CHARACTER SET=xxx和指定字段时的DEFAULT CHARACTER SET=xxx来回答：

character_set_server决定了服务器的默认编码，character_set_database决定了新建数据库的默认字符集，而数据库的字符集又决定了新建表的默认字符集，而表的字符集又决定了字段的默认字符集，如果没有通过DEFAULT CHARACTER SET=xxx来改变表的字符集，则新表就使用character_set_database指定的字符集。

 

5.针对第五个问题，使用collation_connection来回答：

collation_connection变量制定了比较的规则。collation_connection的值得形式如下：字符集_语言_ci（大小不写敏感） 或字符集_语言_cs（大小写敏感），像中文这样的，没有大小写，所以只能是ci，比如set collatioin_connection=gbk_chinese_ci。就是设置成中文字典的排序规则。除了按具体语言排序，还可以按照二进制的位置排序，比如utf8_bin。

character_set_connection和collatioin_connection是一体的，设置了character_set_connection之后，collation_connection会跟着变成对应的默认排序规则，反之亦然。如果要显示的设置排序规则，可以用 SET NAMES 'charset_name' COLLATE 'collation_name' 。

但是如果查询语句的字符串和表的字段比较，则collation_connection不适用，因为表的字段有它自己的字符排序规则，而它自己的排序规则优先级高于collation_connection。

 

6.针对第六个问题，使用character_set_system来回答：

character_set_system表示元数据的字符集，默认就是utf8，而且不要去更改它，否则，因为类似于用户名密码这种东西，可能用各种奇葩的字符去表示，只有utf8能够容纳它们。如果变成了别的字符集，那么用户名和密码就不能用你想要的字符去表示了。

需要注意的是，这个character_set_system也好， character_set_dababase、character_set_server也好，都指标是在数据库内部的保存格式，而不是返回到客户端的编码格式，返回到客户端的结果都会转化为character_set_results指定的字符集之后再返回，官方文档原话是“Storage of metadata using Unicode does not mean that the server returns headers of columns and the results of DESCRIBE functions in the character_set_system character set by default. When you use SELECT column1 FROM t, the name column1 itself is returned from the server to the client in the character set determined by the value of the character_set_results system variable, which has a default value of latin1.”。

【实践篇】

☆修改数据表某个字段的编码类型，解决生僻汉字插入问题：

因为mysql数据库安装的时候，包括我们代码做数据库连接的时候都习惯用gbk，但是实际生产中发现部分生僻字，gbk都不支持，要用utf8，例如“䶮”：

修改步骤如下，以appebms数据库的app_user_patient表（患者信息）为例，要把里面的姓名字段改成utf8编码方式。

（1）修改表字段类型为utf8：

alter table app change Patient_Name Patient_Name VARCHAR(20) CHARACTER SET utf8 DEFAULT '' COMMENT '姓名';

其实也可以手工在数据库工具里设置排序方式：

 （2）修改mybatis里配置的数据库连接字符串的属性为utf8：

 

当然，如果在新建表时就指定默认编码或排序规则，则也可以解决问题，全表所有的字段都是utf8编码了，例如：

CREATE TABLE `app_user_patient3` ( `HOSPITAL_ID` VARCHAR(20) NULL DEFAULT '' COMMENT '医院id', `USER_ID` VARCHAR(40) NULL DEFAULT '' COMMENT '用户标识（微信openid）', `HIS_CARD_NO` VARCHAR(30) NULL DEFAULT '' COMMENT '就诊卡号', `PATIENT_ID` VARCHAR(20) NULL DEFAULT '' COMMENT '患者PatID', `PATIENT_MZH` VARCHAR(20) NULL DEFAULT '' COMMENT '门诊号', `IN_HOSPITAL_NO` VARCHAR(30) NULL DEFAULT '' COMMENT '患者住院号', `PATIENT_IDNO` VARCHAR(20) NULL DEFAULT '' COMMENT '患者身份证号', `PATIENT_NAME` VARCHAR(30) NULL DEFAULT '' COMMENT '患者姓名', `PATIENT_SEX` VARCHAR(4) NULL DEFAULT '' COMMENT '性别', `PATIENT_AGE` VARCHAR(4) NULL DEFAULT '0' COMMENT '年龄', `PATIENT_PHONE_NO` VARCHAR(20) NULL DEFAULT '' COMMENT '患者手机号码', `ACTIVE_FLAG` VARCHAR(2) NULL DEFAULT '' COMMENT '激活标志', `CREATE_TIME` VARCHAR(20) NULL DEFAULT '' COMMENT '创建时间', `REMARK` TEXT NULL COMMENT '备注', INDEX `KEY_UserID` (`USER_ID`), INDEX `KEY_UserID_PatID` (`USER_ID`, `PATIENT_ID`) ) COMMENT='用户患者表' DEFAULT CHARSET=UTF8 ENGINE=InnoDB;

 

CREATE TABLE `app_user_patient2` ( `HOSPITAL_ID` VARCHAR(20) NULL DEFAULT '' COMMENT '医院id', `USER_ID` VARCHAR(40) NULL DEFAULT '' COMMENT '用户标识（微信openid）', `HIS_CARD_NO` VARCHAR(30) NULL DEFAULT '' COMMENT '就诊卡号', `PATIENT_ID` VARCHAR(20) NULL DEFAULT '' COMMENT '患者PatID', `PATIENT_MZH` VARCHAR(20) NULL DEFAULT '' COMMENT '门诊号', `IN_HOSPITAL_NO` VARCHAR(30) NULL DEFAULT '' COMMENT '患者住院号', `PATIENT_IDNO` VARCHAR(20) NULL DEFAULT '' COMMENT '患者身份证号', `PATIENT_NAME` VARCHAR(30) NULL DEFAULT '' COMMENT '患者姓名', `PATIENT_SEX` VARCHAR(4) NULL DEFAULT '' COMMENT '性别', `PATIENT_AGE` VARCHAR(4) NULL DEFAULT '0' COMMENT '年龄', `PATIENT_PHONE_NO` VARCHAR(20) NULL DEFAULT '' COMMENT '患者手机号码', `ACTIVE_FLAG` VARCHAR(2) NULL DEFAULT '' COMMENT '激活标志', `CREATE_TIME` VARCHAR(20) NULL DEFAULT '' COMMENT '创建时间', `REMARK` TEXT NULL COMMENT '备注', INDEX `KEY_UserID` (`USER_ID`), INDEX `KEY_UserID_PatID` (`USER_ID`, `PATIENT_ID`) ) COMMENT='用户患者表' COLLATE='utf8_general_ci' ENGINE=InnoDB;

 

以上两种方式经过测试都可以，但是同样记得要把数据库连接字符串的属性为utf8。

 

现在我倒是可以得出一个不是100%确定的经验，那就是utf8支持的汉字要比gbk多，我擦。

 

 

我又做了以下实验：

alter table app_user_patient3 character set gbk; 也就是把app_user_patient3表本身的编码改成gbk，然后查看一下本表的定义的变化（用show create table app_user_patient3;命令）。

在未改变表编码之前是这个结果：

 

可以看到最后一行是重点，编码格式是utf8。

那么在执行alter语句之后呢？结果如下：

 可以看到最后一行的表本身的编码变成gbk了，但是表里面的字段却依然保持了utf8编码，当然用HeidiSQL工具查看表字段定义，可以看到排序方式都是utf8_general_ci：

由此可以看出，mysql数据库的编码定义应该是这样的，定义数据库时的编码决定了其下所建立表的编码，建立表时所指定的（如不指定则默认采用数据库的编码）编码决定了表中所建立字段的默认编码。但是，我是说但是，如果在建立好表之后，单独修改表的编码，不会影响字段的编码定义，这个可以从上面的实验中得出这个结论。