最近在弄帖子的發(fā)布與回復(fù)問(wèn)題秉颗，然后再iOS端和Android端添加表情的時(shí)候都會(huì)出錯(cuò)
Caused by: java.sql.SQLException: Incorrect string value: '\xF6\x9D\x98\x84' for column 'comment' at row 1
提示表情有問(wèn)題痢毒，然后趕緊查資料。
首先數(shù)據(jù)庫(kù)編碼為UTF-8蚕甥，字段content設(shè)置為text(CHARSET=utf8 COLLATE=utf8_unicode_ci)用于存儲(chǔ)帖子內(nèi)容信息闸准，現(xiàn)在上線新功能存儲(chǔ)emoji表情，插入emoji表情就會(huì)報(bào)出上述錯(cuò)誤梢灭，UTF-8編碼有可能是兩個(gè)夷家、三個(gè)、四個(gè)字節(jié)敏释。Emoji表情是4個(gè)字節(jié)库快，而Mysql的utf8編碼最多3個(gè)字節(jié)，所以數(shù)據(jù)插不進(jìn)去钥顽。utf8mb4兼容utf8义屏，且比utf8能表示更多的字符，所以要設(shè)置數(shù)據(jù)庫(kù)的編碼為utf8mb4蜂大。
數(shù)據(jù)庫(kù)編碼從utf8設(shè)置為utf8mb4
1 修改my.cnf文件
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
character-set-client-handshake = false
2 對(duì)數(shù)據(jù)庫(kù)相關(guān)的表進(jìn)行字符集修改
將數(shù)據(jù)庫(kù)轉(zhuǎn)換為utf8mb4
ALTER DATABASE post CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `post_article` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `post_article` MODIFY COLUMN `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3 重啟數(shù)據(jù)庫(kù)
sudo /etc/init.d/mysql restart
4 檢查數(shù)據(jù)庫(kù)的字符集是否是utf8mb4
SHOW VARIABLES WHERE Variable_name LIKE 'character%' OR Variable_name LIKE 'collation%';
5 java 設(shè)置
設(shè)置Java的數(shù)據(jù)庫(kù)連接闽铐，將characterEncoding去掉，然后保證mysql connector版本高于5.1.13奶浦，就OK了
