mysql处理emoji表情_Mysql处理emoji表情

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

1 org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update2

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 ... 12more46

原因是当前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+

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 SETutf8mb4 COLLATE utf8mb4_unicode_ci;

#For each column:ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SETutf8mb4 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

参考资料：