mysql替换字段中的部分字符-关于MySQL如何修改编码问题(utf8转utf8mb4)–解决表情存库

系统接入微信公众号，需要存储用户昵称，但是发现，有些用户的昵称存储报错；

查询之后，发现用户昵称中包含emoji表情，而数据库的编码为utf8；

因此需要对相关的数据库、表、字段等的编码修改为utf8mb4，以达到保存emoji表情的目的。

一.utf8mb4与utf8的区别

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。

理论上讲， UTF-8 格式使用一至六个字节，最大能编码 31 位字符。最新的 UTF-8 规范只使用一到四个字节，最大能编码21位，正好能够表示所有的 17个 Unicode 平面。

utf8mb4与utf8的区别

二.遇到的问题

utf-8编码可能2个字节、3个字节、4个字节的字符，但是MySQL的utf8编码只支持3字节的数据，而移动端的表情数据是4个字节的字符。

如果直接往采用utf-8编码的数据库中插入表情数据，java程序中将报SQL异常：

SQL异常

三.那怎么样才能解决这种问题呢？

要解决字符集的问题，首先要知道现在的系统、数据库、表、客户端等使用什么样的字符集，以及系统支持什么字符集等，下面介绍一些获取相关信息的语句

1.查看数据库支持的所有字符集：

 show character set;或者show char set;

数据库支持的字符集

2.查看系统字符集设置，包括所有的字符集设置：

show variables like '%char%';

当前数据库的字符集设置

其中的含义如下：

上图字符集含义

character_set_connection、character_set_client、character_set_results三个字符集什么时候用到?其实，当客户端连接服务器的时候，它会将自己想要的字符集名称发给mysql服务器，然后服务器就会使用这个字符集去设置character_set_connection、character_set_client、character_set_results这三个值。如cmd是用gbk，而mysql workbench是用utf8.

3.查看数据表中字符集设置：

show full columns from tablename;

4.查看数据库编码：

show create database dbname;

四.从创建数据库时就指定utf8mb4

知道了怎么查找字符集的相关信息之后，我们就要懂得怎么在创建指定对象的时候，为该对象匹配相应的字符集。

1.服务器级：

在安装MySQL时可以设置服务器的默认编码格式mysql替换字段中的部分字符，也可对my.ini做修改，修改[mysqld]里面的character_set_server=utf8mb4，则可设置character_set_server的值。

2.数据库级：

 CREATE DATABASE db_name DEFAULT CHARACTER SET utf8mb4;

注意，如果不指定默认的字符集，则系统会根据character_set_database的值进行设置，如：

3.表级：

CREATE TABLE db_name.tb_name (id VARCHAR(20) NOT NULL,name VARCHAR(20) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

从下图可看出，定义表的默认字符集为utf8，即使character_set_database为gbk，但是表的列都未utf8

但要注意，如果没有定义表的默认字符集，则他会按照character_set_database的值来设置，如图所示：

4.列级：

CREATE TABLE db_name.tb_name ( id varchar(20) NOT NULL, name varchar(20) CHARACTER SET utf8mb4 );

从下图可以看到，整个表的默认字符集为gbkmysql替换字段中的部分字符，所以没有指定字符集的列都用默认的字符集，而指定了字符集的列name，则使用指定的字符集utf8mb4。

五.已经创建数据库修改原有字符集

如果已经是创建好的对象，那又应该如何处理呢。我们就应该对指定对象就行修改字符集的操作。

1.修改character_set_connection、character_set_client、character_set_results三值：

对于某一个连接来说，可以使用：

SET NAMES ‘charset_name’ [COLLATE ‘collation_name’]

命令

 SET NAMES 'charset_name' [COLLATE 'collation_name']

相当于

SET character_set_client = charset_name; 
SET character_set_results = charset_name; 
SET character_set_connection = charset_name;

另外、还可以修改配置文件，对[mysql]下增加default-character-set=utf8mb4，配置成你想要的字符集。（个人尝试在my.ini里面配置过，没有成效，不知道是不是被使用的客户端想要的字符集给覆盖掉了呢？）

2.修改character_set_database字段：

 ALTER DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name]

3.修改character_set_server字段：

最简单的方法是直接改my.ini配置文件里面[mysqld]的字段，增加character-set-server=gbk，然后重启mysqld，则可改为你想要的字符集。

4.修改表的字符集：

 ALTER TABLE tbl_name [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]

5.修改列的字符集：

 col_name {CHAR | VARCHAR | TEXT} (col_length) [CHARACTER SET charset_name] [COLLATE collation_name]

例如：

 ALTER TABLE t1 MODIFY col1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_swedish_ci;

限时特惠：本站每日持续更新海量设计资源，一年会员只需29.9元，全站资源免费下载
站长微信：ziyuanshu688

htebazile永久VIP

收藏 海报 链接