如何让MySql数据库支持emoji表情
1. 字符集
1.1 字符集的分类
- MySQL可以在服务器上、数据库、表、字段上指定字符编码
- 在服务器上指定字符编码是安装服务器的时候指定的
- 数据库、表、字段上是创建时候指定的
常用的字符集有：
gbk,gb2312,utf8,utf8mb4
utf-8
通常情况，Mysql数据编码格式为“utf-8”，对于汉字来说足够；Mysql中utf8占3个字节，但是，3个字节对于表情符号是不够的，需4个字节；此时使用utf8，会出现‘\xF0\x9F\x8D\x83\xF0\x9F’的问题。
utf8mb4
utf8mb4编码是utf8编码的超集，兼容utf8，并且能存储4字节的表情字符。
1.2 如何查看字符集
-
查看MYSQL数据库服务器和数据库字符集
方法一：show variables like '%character%'; 方法二：show variables like 'collation%';-
方法1：
mysql> show variables like '%character%'; +--------------------------+--------------------------------------+ | 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/mysql5535/share/charsets/ | +--------------------------+--------------------------------------+ 8 rows in set (0.00 sec) -
方法2：
mysql> show variables like 'collation%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec)
-
-
查看库的字符集
语法：show create database 数据库\G;mysql> show create database shiyan\G *************************** 1. row *************************** Database: shiyan Create Database: CREATE DATABASE `shiyan` /*!40100 DEFAULT CHARACTER SET gbk */ 1 row in set (0.00 sec) -
查看表的字符集
语法：show table status from 库名 like 表名;mysql> show table status from class_7 like 'test_info'; +-----------+--------+---------+------------+------+----------------+-------------------------+-------------+------------+-----------------+----------+- | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_leate_time | Update_time | Check_time | Collation | Checksum | +-----------+--------+---------+------------+------+----------------+-------------------------+-------------+------------+-----------------+----------+- | test_info | InnoDB | 10 | Compact | 10 | 1638 | 17-12-05 19:01:55 | NULL | NULL | utf8_general_ci | NULL | +-----------+--------+---------+------------+------+----------------+-------------------------+-------------+------------+-----------------+----------+- 1 row in set (0.00 sec) -
查看表中所有列的字符集
语法：show full columns from 表名;mysql> show full columns from test_info; +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | id | int(3) | NULL | NO | PRI | NULL | | select,insert,update,references | | | name | char(12) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | dorm | char(10) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | addr | char(12) | utf8_general_ci | YES | | 未知 | | select,insert,update,references | | | score | int(3) | NULL | YES | | NULL | | select,insert,update,references | | +-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+ 5 rows in set (0.00 sec)
1.3 字符集的解释
- 查询结果如下图：
系统变量： – character_set_client： 客户端来源数据使用的字符集
– character_set_connection： 连接层字符集
– character_set_database： 当前选中数据库的默认字符集
– character_set_results： 查询结果字符集
– character_set_filesystem： 用于没有字符集导入符的文字和数字－字符串转换
– character_set_server： 默认的内部操作字符集
– character_set_system： 系统元数据(字段名等)字符集
– 还有以collation_开头的同上面对应的变量，用来描述字符序。
系统变量，在MySQL架构图中的位置：
1.4 如何修改字符集
1. 针对字段修改编码格式（推荐使用）
- 数据库表对应字段上修改编码格式为**utf8mb4，**命令示例如下：
ALTER TABLE insure_user CHANGE nickname nickname VARCHAR(64) CHARACTER SET utf8mb4 ;
2. 可视化工具，Navicat 设计表示例：
-
改字段字符集
-
改表字符集
-
改datebase字符集
2. 排序规则的选择
todo
3. 为什么MySql数据库不支持emoji表情
- MYSQL 插入表情符号时，报异常：
java.sql.BatchUpdateException: Incorrect string value: '\xF0\x9F\x8D\x83\xF0\x9F...' for column 'nickname' at row 1
-
原因：
通常情况，Mysql数据编码格式为“utf-8”，对于汉字来说足够；Mysql中utf8占3个字节，但是，3个字节对于表情符号是不够的，需4个字节；此时使用utf8，会出现‘\xF0\x9F\x8D\x83\xF0\x9F’的问题。
4. 支持emoji表情的两种方法
前提：修改相应字段的字符集改为utf8mb4
首先，两种方法实现的前提是：先修改相应字段的字符集改为utf8mb4( 修改方法见1.4 )
4.1 java配置文件增加“connection-init-sql: set names utf8mb4”
相应字段的字符集修改为utf8mb4后，在java服务的yml配置文件中，数据库连接信息增加：
connection-init-sql: set names utf8mb4
如图：
测试结果：
总结：
- 是否能够插入表情，不仅仅要求相关的字段是utf8mb4编码格式，utf8mb4仅仅只是前提
- 除了要求字段的格式是utf8mb4，还要求当前的session会话连接的编码同时是utf8mb4
- set name utf8mb4 只对当前会话有效，不影响其他连接和全局的配置
这么做的原理分析：connectionInitSqls 是用来干嘛的，为什么配置一个这个就可以了？
connectionInitSqls 是druid 数据库特有的配置（查看官方文档说明该参数是在初始化连接时执行的sql），
那这样就很好理解了，当我们执行set name utf8mb4之后就在当前session 中一直使用utf8mb4的编码格式
druid 数据源在初始化连接之后，还会进行配置的initSql初始化，在这次初始化里，用户设置了set names utf8mb4 之后，就会指定了最终的字符集。
4.2 修改Server端 character_set_server的字符集，并重启
查看MySQL官方文档，发现这么一句话：
Connector/J now auto-detects servers configured with character_set_server=utf8mb4 or treats the Java encoding utf-8 passed using characterEncoding=... as utf8mb4 in the SET NAMES= calls it makes when establishing the connection
意思大概是：java 会自动检测 MySQL系统参数：character_set_server的字符集来编码
所以，我们来修改MySQL Server端的字符集，重启数据库后，也能实现 emoji标清的插入：
修改Mysql配置文件my.cnf（windows下为my.ini），然后重启数据库
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
总结：
- 是否能够插入表情，不仅取决于字段的编码格式，还有当前session会话的编码格式 或 MySQL Server端的编码格式
- 不论哪种方法，前提都是先将相应字段的字符集改为utf8mb4
- 然后 4.1 、 4.2方法，二选一执行即可
记录：4.1方法，java服务采用 手动创建数据库连接时，解决方案：
背景是：我手头的 java项目用到了多数据源(2个MySQL + 1个sqlServer)，所以在建立数据库连接时，采用的是配置类 手动创建的；
@Primary
@Bean(name = "orderDataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid.order")
public DataSource orderDataSource() {
return DataSourceBuilder.create().type(DruidDataSource.class).build();
}
这样就导致，我在配置文件增加“connection-init-sql: set names utf8mb4”，不会在创建连接时，被使用到；导致会话连接的编码格式没有修改成功；
解决方式：
配置类创建连接的方法中，手动增加 set names utf8mb4，如下：
@Primary
@Bean(name = "orderDataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid.order")
public DataSource orderDataSource() {
// 建立数据库连接时，设置utf8mb4编码。目的兼容 emoji表情
dataSource.setConnectionInitSqls(new ArrayList<String>() {{ add("set names utf8mb4;");}});
return dataSource;
}
