MySQL設定utf8mb4編碼

MySQL設定utf8mb4編碼

有一個項目需要儲存ios的表情(emoji表情)
這種表情雖然是utf8編碼，但是一個字元需要佔用4個位元組，而MySQL utf8編碼只能存放3位元組的字元。
在MySQL 5.6中，可以設定編碼為utf8mb4,這個字元集是utf8的超集。

實驗環境
MySQL 5.6.14
JDBC 5.1.31
測試表 create table test( content varchar(50) )engine=innodb,charset=utf8mb4;
測試程式:

import java.io.IOException;

import java.net.URLDecoder;

import java.net.URLEncoder;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

 

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

 

/**

* Servlet implementation class CharsetTest

*/

@WebServlet("/CharsetTest")

public class CharsetTest extends HttpServlet {

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

String str = request.getParameter("content");

str = URLDecoder.decode(str, "utf8");

System.out.println(URLEncoder.encode(str, "utf8"));

 

try {

save(str);

} catch (Exception e) {

e.printStackTrace();

}

 

}

 

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

doGet(request, response);

}

 

private static void save(String content) throws Exception {

/**

* create table test( content varchar(50) )engine=innodb,charset=utf8mb4

*/

Class.forName("com.mysql.jdbc.Driver");

Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/xx", "xx", "xx");

 

connection.setAutoCommit(true);

 

//通過查詢回合設定字元集的命令

//connection.prepareStatement("set names utf8mb4").executeQuery();

 

PreparedStatement cmd = connection.prepareStatement("insert into test values(?)");

cmd.setString(1, content);

cmd.executeUpdate();

 

cmd.close();

connection.close();

}

}
測試連結:
兩次編碼後的ios表情:
http://127.0.0.1:8080/Web/CharsetTest?content=%25F0%259F%2598%2584

兩次編碼後的中文:
http://127.0.0.1:8080/Web/CharsetTest?content=%25E4%25B8%25AD%25E6%2596%2587

關於兩次編碼參見:

首先，修改mysql的設定檔
character_set_server=utf8mb4
然後重啟資料庫和中介軟體.
點擊兩個測試的連結,查看資料庫，探索資料成功插入。

這個過程理論上是不需要重啟資料庫的。
但是實際測試中發現，如果不重啟資料庫，則插入會報錯。

如果運氣好，直接修改character_set_server參數，重啟資料庫，一切正常，就OK了。
運氣不好(比如我),就很悲劇了。
我在生產庫上修改了配置,並且重啟了資料庫。
居然發現ios的表情插入資料庫都是亂碼(全是問號 ????)
更悲劇的是，過了幾分鐘突然發現線上新插入的資料都是亂碼(也都是問號)。
幸虧發現的早，還原了資料庫的配置，否則運行幾天之後發現，估計就得收拾小包袱走人了。

後來排查到這個問題是JDBC驅動造成的，線上JDBC驅動的版本是mysql-connector-java-5.1.6-bin
如果MySQL伺服器設定為utf8mb4 高版本的JDBC驅動沒有關係，但是低版本的驅動插入之後，就是下面這個樣子。
所有輸入的非英文字元都是亂碼了。

因為JDBC驅動並不支援utf8mb4字元集，所以不能設定JDBC URL的characterEncoding

不過還有三種方式可以設定字元集
1.不顯式設定字元集，繼承伺服器的配置

2.在執行SQL之前，運行set names 的查詢 (Query方式)

3.設定MySQL init_connect參數

經過測試各種因素的結果如下所示:

	JDBC版本	普通中文	蘋果表情
伺服器utf8編碼	5.1.6	正常	插入報錯
	5.1.6 Query	正常	正常
	5.1.6 init_connect	正常	插入報錯
	5.1.31	正常	插入報錯
	5.1.31 Query	正常	正常
	5.1.31 init_connect	正常	插入報錯
伺服器utf8mb4編碼	5.1.6	亂碼	亂碼
	5.1.6 Query	亂碼	亂碼
	5.1.6 init_connect	亂碼	亂碼
	5.1.31	正常	正常
	5.1.31 Query	正常	正常
	5.1.31 init_connect	正常	正常

總結:
1.修改了character_set_server參數，需要重啟資料庫
2.使用高版本的JDBC

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

--------------------------------------分割線 --------------------------------------

本文永久更新連結地址：