Emoji表情符号录入MySQL数据库报错的解决方案

来源:IT165收集  发布日期:2014-08-01 21:13:31

前言:手机app应用评论的时候,恢复表情符号,提示失败。
?
1,查看tomcat后台日志,核心报错信息如下:
Caused by: java.sql.SQLException: Incorrect string value: 'ð???ð?...' for column 'CONTENT' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:80)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)
... 46 more

[root@mysqlvm2 ~]# mysql -root -p
mysql> use test;
Database changed
mysql> show tables;
Ignoring query to other database
mysql> Ctrl-C -- exit!
Aborted
忘记输入-u参数了,冲洗你输入,OK,如下所示:
[root@mysqlvm2 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.6.12-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
mysql> use test;
Database changed
mysql> show tables;
+------------------------+
| Tables_in_test |
+------------------------+
| c |
| lubin_test |
| test |
| tt |
+------------------------+
10 rows in set (0.00 sec)
这种输入Incorrect string value: 'ð???ð?...'的问题,多数都是字符集的,以前从latain改成gbk,从gbk改成utf8,而我的CONTENT字段已经utf8了,那么比utf8更多的就只有utf8mb4了,所以去修改表字段的字符集吧。

mysql>


2,先去修改表字段字符集为utf8mb4:
ALTER TABLE UGC_REVIEW_CONTENT MODIFY `CONTENT` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '评论内容';
执行完后,再在手机端的app测试,依然报同样的错误。


3,再去修改表字符集utf8mb4:
ALTER TABLE UGC_REVIEW_CONTENT CHARSET=utf8mb4 COMMENT='晒单/推荐的评论内容';
执行完后,再在手机端的app测试,依然报同样的错误。


4,再去修改数据库的字符集utf8mb4:
vim my.cnf
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4

重启mysql数据库
[root@mysqlvm4 ~]# service mysql restart
Shutting down MySQL.... [确定]
Starting MySQL.............................................[确定]..
[root@mysqlvm4 ~]#

查看db的字符集
mysql> show variables like '%char%';
+--------------------------+----------------------------------+
| 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 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
mysql>

在手机app端重新评论,输入表情符号,点击提交提示评论成功。

5,问题分析总结:
㈠ 原因
普通的字符串或者表情都是占位3个字节,所以utf8足够用了,但是移动端的表情符号占位是4个字节,普通的utf8就不够用了,为了应对无线互联网的机遇和挑战、避免 emoji 表情符号带来的问题、涉及无线相关的 MySQL 数据库建议都提前采用 utf8mb4 字符集,这必须要作为移动互联网行业的一个技术选型的要点
㈡ 限制
需要 >= MySQL 5.5.3版本、从库也必须是5.5的了、低版本不支持这个字符集、复制报错。


Tag标签: 符号   表情   解决方案  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规