mysql utf8mb4与emoji表情
canca
MYSQL 5.5 之前， UTF8 编码只支持1-3个字节，只支持BMP这部分的unicode编码区， BMP是从哪到哪，到http://en.wikipedia.org/wiki/Mapping_of_Unicode_characters这里看，基本就是0000～FFFF这一区。 从MYSQL5.5开始，可支持4个字节UTF编码utf8mb4，一个字符最多能有4字节，所以能支持更多的字符集。
utf8mb4 is a superset of utf8
utf8mb4兼容utf8，且比utf8能表示更多的字符。
至于什么时候用，看你的做什么项目了。。
在做移动应用时，会遇到ios用户会在文本的区域输入emoji表情，如果不做一定处理，就会导致插入数据库异常。
一 什么是Emoji
<此段摘自百度百科 http://baike.baidu.com/view/2631589.htm>
CREATE TABLE `ios_emoji` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`unicode` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Unicode编码',
`utf8` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'UTF8编码',
`utf16` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'UTF16编码',
`sbunicode` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'SBUnicode编码',
`filename` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '文件名',
`filebyte` longblob COMMENT '文件内容字节',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='ios表情编码表';
import java.io.UnsupportedEncodingException;
import org.apache.commons.lang.StringUtils;
public class IOSEmojiUtil {
public static String[] ios5emoji ;
public static String[] ios4emoji ;
public static String[] androidnullemoji ;
public static String[] adsbuniemoji;
public static void initios5emoji(String[] i5emj,String[] i4emj,String[] adnullemoji,String[] adsbemoji){
ios5emoji = i5emj;
ios4emoji = i4emj;
androidnullemoji = adnullemoji;
adsbuniemoji = adsbemoji;
}
//在ios上将ios5转换为ios4编码
public static String transToIOS4emoji(String src) {
return StringUtils.replaceEach(src, ios5emoji, ios4emoji);
}
//在ios上将ios4转换为ios5编码
public static String transToIOS5emoji(String src) {
return StringUtils.replaceEach(src, ios4emoji, ios5emoji);
}
//在android上将ios5的表情符替换为空
public static String transToAndroidemojiNull(String src) {
return StringUtils.replaceEach(src, ios5emoji, androidnullemoji);
}
//在android上将ios5的表情符替换为SBUNICODE
public static String transToAndroidemojiSB(String src) {
return StringUtils.replaceEach(src, ios5emoji, adsbuniemoji);
}
//在android上将SBUNICODE的表情符替换为ios5
public static String transSBToIOS5emoji(String src) {
return StringUtils.replaceEach(src, adsbuniemoji, ios5emoji);
}
//eg. param: 0xF0 0x9F 0x8F 0x80
public static String hexstr2String(String hexstr) throws UnsupportedEncodingException{
byte[] b = hexstr2bytes(hexstr);
return new String(b, "UTF-8");
}
//eg. param: E018
public static String sbunicode2utfString(String sbhexstr) throws UnsupportedEncodingException{
byte[] b = sbunicode2utfbytes(sbhexstr);
return new String(b, "UTF-8");
}
//eg. param: 0xF0 0x9F 0x8F 0x80
public static byte[] hexstr2bytes(String hexstr){
String[] hexstrs = hexstr.split(" ");
byte[] b = new byte[hexstrs.length];
for(int i=0;i<hexstrs.length;i++){
b[i] = hexStringToByte(hexstrs[i].substring(2))[0];
}
return b;
}
//eg. param: E018
public static byte[] sbunicode2utfbytes(String sbhexstr) throws UnsupportedEncodingException{
int inthex = Integer.parseInt(sbhexstr, 16);
char[] schar = {(char)inthex};
byte[] b = (new String(schar)).getBytes("UTF-8");
return b;
}
public static byte[] hexStringToByte(String hex) {
int len = (hex.length() / 2);
byte[] result = new byte[len];
char[] achar = hex.toCharArray();
for (int i = 0; i < len; i++) {
int pos = i * 2;
result[i] = (byte) (toByte(achar[pos]) << 4 | toByte(achar[pos + 1]));
}
return result;
}
private static byte toByte(char c) {
byte b = (byte) "0123456789ABCDEF".indexOf(c);
return b;
}
public static void main(String[] args) throws UnsupportedEncodingException {
// TODO Auto-generated method stub
byte[] b1 = {-30,-102,-67}; //ios5 //0xE2 0x9A 0xBD
byte[] b2 = {-18,-128,-104}; //ios4 //"E018"
//-------------------------------------
byte[] b3 = {-16,-97,-113,-128}; //0xF0 0x9F 0x8F 0x80
byte[] b4 = {-18,-112,-86}; //E42A
ios5emoji = new String[]{new String(b1,"utf-8"),new String(b3,"utf-8")};
ios4emoji = new String[]{new String(b2,"utf-8"),new String(b4,"utf-8")};
//测试字符串
byte[] testbytes = {105,111,115,-30,-102,-67,32,36,-18,-128,-104,32,36,-16,-97,-113,-128,32,36,-18,-112,-86};
String tmpstr = new String(testbytes,"utf-8");
System.out.println(tmpstr);
//转成ios4的表情
String ios4str = transToIOS5emoji(tmpstr);
byte[] tmp = ios4str.getBytes();
//System.out.print(new String(tmp,"utf-8"));
for(byte b:tmp){
System.out.print(b);
System.out.print(" ");
}
}
}
http://punchdrunker.github.com/iOSEmoji/table_html/flower.html
http://code.iamcal.com/php/emoji/
http://stackoverflow.com/questions/7856775/how-to-convert-the-old-emoji-encoding-to-the-latest-encoding-in-ios5
http://dropblood.com/archives/ios-mysql-emoji
http://www.iapps.im/wp-content/uploads/2012/02/emoji-pinyin.png?r=010
七 下载资源
emoji图片和编码表 http://download.csdn.net/detail/qdkfriend/4309051
包括emoji文件表,emoji数据编码表(Unicode编码,UTF8编码,UTF16编码,SBUnicode编码)
mysql支持utf8mb4升级方案
http://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4
发表评论