MySQL utf8mb4字符集测试

文档说明

    本文档主要针对mysql 5.5之后,jdbc向mysql插入emoji表情或者4字节字符报错的情况，通过配置my.cnf,database,table,测试修改utf8mb4字符集的可行方法。

测试环境

mysql:Percona-Server-5.5.18
db:test_utf8
   test_utf8mb4
tables:t2 --utf8,两个db里均有
       t3 --utf8mb4,两个db里均有

测试结果

测试代码：

[main]

public static void main(String[] args) {
 Connection conn = null;
 Statement stmt = null;
 ResultSet rs = null;
 conn=mysqltest_character.getConnection();
 try {
      stmt=conn.createStatement();
      //准备sql操作语句
      String sql_read= "SELECT col1 FROM t2/t3";
      String sql_insert = "insert into t2/t3 values('ὄ')";
      int count = stmt.executeUpdate(sql_insert);
      if (count == 0) {
        System.out.println("插入数据失败!");
        System.exit(1);
      }
      else {
        System.out.println("插入成功！");
      }
       rs = stmt.executeQuery(sql_read);
      //从结果集中提取数据
      while(rs.next()){
           String c1 = rs.getString("col1");
           System.out.println("col1: " + c1);
      }
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
        mysqltest_character.close(conn, stmt, rs);
    }
}

[db.properties]

url=jdbc:mysql://10.37.12x.xxx:3309/test_utf8?useSSL=false&characterEncoding=utf8&characterSetResults=utf8&generateSimpleParameterMetadata=true&useServerPrepStmts=true&cachePrepStmts=true&connectTimeout=5000
user=root
password=123456
driverClass=com.mysql.jdbc.Driver

测试过程：

my.cnf=utf8|database=utf8|tables=utf8

• result

无法写入4字节的字符

my.cnf=utf8|database=utf8|tables=utf8mb4

• result

无法写入4字节的字符

my.cnf=utf8|database=utf8mb4|tables=utf8

• result

无法写入4字节的字符

my.cnf=utf8|database=utf8mb4|tables=utf8mb4

• result

无法写入4字节的字符

my.cnf=utf8mb4|database=utf8|tables=utf8

修改my.cnf character-set-server=utf8mb4：

sed -i 's/utf8/utf8mb4/g' /home/mysql55/my55.cnf

kill -9 23079 24406
su - mysql55
/bin/sh /usr/local/mysql55/bin/mysqld_safe --defaults-file=/home/mysql55/my55.cnf &

• result

无法写入4字节的字符

my.cnf=utf8mb4|database=utf8|tables=utf8mb4

• result

成功写入。

在库中可以成功读取。

my.cnf=utf8mb4|database=utf8mb4|tables=utf8

• result

无法写入4字节的字符

my.cnf=utf8mb4|database=utf8mb4|tables=utf8mb4

• result

成功写入。

在库中可以成功读取。

utf8-utf8mb4修改方案

对于现有生产环境中的MySQL5.5版本以及由MySQL5.5升级后的MySQL5.7等使用utf8的情况（MySQL实例、DB、表、字段等均为utf8字符集），在遇到字符集问题时，可以采用以下快速简易方法进行修正：

1. Java代码中获取数据库连接时，执行set names utf8mb4 ，示例代码如下：

   Connection conn = DriverManager.getConnection(url, userName, password);
   conn.prepareStatement("set names utf8mb4").executeQuery();
   

2. 仅需修改相应数据库表中需要存储特殊字符的字段字符集为utf8mb4，实例如下：

   alter table t5 change col1 col1 char(20) CHARACTER SET utf8mb4;
   

Notes： 以上方法在每次遇到特殊字符引起的问题时都需要进行单独的修改，数据库中存在两种不同的字符集，不便于管理。因此，建议对整个数据库进行字符集的修改：（最好先做mysqldump备份）

alter database DBNAME DEFAULT CHARACTER SET utf8mb4;
alter table tbl_name convert to character set character_name ;
修改配置文件中 参数character_set_server = utf8mb4，并重启数据库。