Mysql处理emoji表情

在实际开发过程中，涉及到向mysql数据表中insert tweet，很多情况下会报错：

 1 org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
 2  
 3       at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
 4  
 5       at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
 6  
 7       at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
 8  
 9       at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
10  
11       at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
12  
13       at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
14  
15       at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
16  
17       at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
18  
19       at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
20  
21       at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
22  
23       at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
24  
25       at com.××××.××××.dao.TweetDaoImpl.saveTweet(TweetDaoImpl.java:63)
26  
27       at com.××××.××××.dao.CollectorFacadeImpl.saveTweetDate(CollectorFacadeImpl.java:36)
28  
29       at com.××××.××××.util.SavingTimerTask.run(SavingTimerTask.java:55)
30  
31       at java.util.TimerThread.mainLoop(Timer.java:512)
32  
33       at java.util.TimerThread.run(Timer.java:462)
34  
35 Caused by: java.sql.BatchUpdateException: Incorrect string value: '\xF0\x9F\x93\xBA\xF0\x9F...' for column 'body' at row 1
36  
37       at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1257)
38  
39       at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:943)
40  
41       at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
42  
43       at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
44  
45       ... 12 more
46  

 

原因是当前mysql的字符集为utf-8，最多3个字节，但遇到像emoji表情这样的特殊字符时，需要4个字节来表示。所以导致insert报错。

mysql> show character set;  
+----------+-----------------------------+---------------------+--------+  
| Charset  | Description                 | Default collation   | Maxlen |  
+----------+-----------------------------+---------------------+--------+  
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |  
+----------+-----------------------------+---------------------+--------+  

 

解决办法：

1.备份数据库

 

2.升级Mysql Server到v5.5.3+

Upgrade the MySQL server to v5.5.3+

 

3.修改database,table,column字符集

# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# (Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a `VARCHAR` column.)

 

4.修改my.ini(linux下为my.cnf)

[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'

重新启动Mysql，检查字符集：

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

 

5.如果是用java连接的mysql，需要升级mysql-connector-java.jar至少到5.1.14

 

参考资料：

http://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4

http://technovergence-en.blogspot.jp/2012/03/mysql-from-utf8-to-utf8mb4.html

http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-upgrading.html

http://stackoverflow.com/questions/7814293/how-to-insert-utf-8-mb4-characteremoji-in-ios5-in-mysql

http://stackoverflow.com/questions/8709892/mysql-throws-incorrect-string-value-error