支持emoji图标直接存储到数据库,更改MySQL数据库的编码为utf8mb4
星期一, 2017-10-23 | Author: Lee | Database, JAVA-and-J2EE | 2,659 views
随着emoji的频繁使用,对接微信后会带名字的emoji的很多,现在都需要支持.
utf-8编码可能2个字节、3个字节、4个字节的字符,但是MySQL的utf8编码只支持3字节的数据,而移动端的表情数据是4个字节的字符。
如果直接往采用utf-8编码的数据库中插入表情数据,java程序中将报SQL异常:
1 2 3 4 5 6 7 8 9 | java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x92\x94’ for column ‘name’ at row 1 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1662) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581) |
可以对4字节的字符进行编码存储,然后取出来的时候,再进行解码。但是这样做会使得任何使用该字符的地方都要进行编码与解码。
utf8mb4编码是utf8编码的超集,兼容utf8,并且能存储4字节的表情字符。
采用utf8mb4编码的好处是:存储与获取数据的时候,不用再考虑表情字符的编码与解码问题。
更改数据库的编码为utf8mb4:
1. MySQL的版本
utf8mb4的最低mysql版本支持版本为5.5.3+,若不是,请升级到较新版本。
2. MySQL驱动
5.1.34可用,最低不能低于5.1.13
3.修改MySQL配置文件
my.cnf一般在/etc/my.cnf位置。找到后请在以下三部分里添加如下内容:
1 2 3 4 5 6 7 8 9 10 | [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci init_connect='SET NAMES utf8mb4' [client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 |
4. 重启数据库,检查变量
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 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 collation_connection 、collation_database 、collation_server是什么没关系。 但必须保证 系统变量 描述 character_set_client (客户端来源数据使用的字符集) character_set_connection (连接层字符集) character_set_database (当前选中数据库的默认字符集) character_set_results (查询结果字符集) character_set_server (默认的内部操作字符集) 这几个变量必须是utf8mb4。 |
5. 数据库连接的配置
数据库连接参数中:
characterEncoding=utf8会被自动识别为utf8mb4,也可以不加这个参数,会自动检测。
而autoReconnect=true是必须加上的。
6. 将数据库和已经建好的表也转换成utf8mb4
更改数据库编码:ALTER DATABASE xxdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
更改表编码:ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
如有必要,还可以更改列的编码
可以写代码批量进行转换:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | //获取所有表 Connection conn = null; try { conn = DbUtilsTool.openConn("MySQL", "127.0.0.1", "3306", "xxdb", "root", "root"); String sql = "show tables"; QueryRunner qr = new QueryRunner(); List<String> tblNameList= (List<String>) qr.query(conn, sql, new ColumnListHandler(1)); sql = "ALTER DATABASE xx CHARACTER SET `utf8mb4` COLLATE `utf8mb4_general_ci`"; qr.update(conn,sql); for (String str:tblNameList) { sql = "ALTER TABLE "+str+" CONVERT TO CHARACTER SET `utf8mb4` COLLATE `utf8mb4_general_ci`"; qr.update(conn,sql); } } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { if(conn!=null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 | DbUtilsTool类: package com.mysql.chartest; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.List; import java.util.Map; import org.apache.commons.dbutils.BasicRowProcessor; import org.apache.commons.dbutils.BeanProcessor; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.ArrayHandler; import org.apache.commons.dbutils.handlers.ArrayListHandler; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ColumnListHandler; import org.apache.commons.dbutils.handlers.KeyedHandler; import org.apache.commons.dbutils.handlers.MapHandler; import org.apache.commons.dbutils.handlers.MapListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; public class DbUtilsTool { private static final QueryRunner runner = new QueryRunner(); /* 打开数据库连接(type: MySQL,Oracle,SQLServer) */ public static Connection openConn(String type, //数据库类型 String host, //主机ip String port, //主机端口 String name, //数据库名 String username, //用户名 String password)//密码 { Connection conn = null; try { String driver; String url; if (type.equalsIgnoreCase("MySQL")) { driver = "com.mysql.jdbc.Driver"; url = "jdbc:mysql://" + host + ":" + port + "/" + name; } else if (type.equalsIgnoreCase("Oracle")) { driver = "oracle.jdbc.driver.OracleDriver"; url = "jdbc:oracle:thin:@" + host + ":" + port + ":" + name; } else if (type.equalsIgnoreCase("SQLServer")) { driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; url = "jdbc:sqlserver://" + host + ":" + port + ";databaseName=" + name; } else { throw new RuntimeException(); } Class.forName(driver); conn = DriverManager.getConnection(url, username, password); } catch (Exception e) { e.printStackTrace(); } return conn; } /* 关闭数据库连接 */ public static void closeConn(Connection conn) { try { if (conn != null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } } /* 查询(返回Array结果) */ public static Object[] queryArray(Connection conn, String sql, Object... params) { Object[] result = null; try { result = runner.query(conn, sql, new ArrayHandler(), params); } catch (SQLException e) { e.printStackTrace(); } return result; } /* 查询(返回ArrayList结果) */ public static List<Object[]> queryArrayList(Connection conn, String sql, Object... params) { List<Object[]> result = null; try { result = runner.query(conn, sql, new ArrayListHandler(), params); } catch (SQLException e) { e.printStackTrace(); } return result; } /* 查询(返回Map结果) */ public static Map<String, Object> queryMap(Connection conn, String sql, Object... params) { Map<String, Object> result = null; try { result = runner.query(conn, sql, new MapHandler(), params); } catch (SQLException e) { e.printStackTrace(); } return result; } /* 查询(返回MapList结果) */ public static List<Map<String, Object>> queryMapList(Connection conn, String sql, Object... params) { List<Map<String, Object>> result = null; try { result = runner.query(conn, sql, new MapListHandler(), params); } catch (SQLException e) { e.printStackTrace(); } return result; } /* 查询(返回Bean结果) */ public static <T> T queryBean(Class<T> cls, Map<String, String> map, Connection conn, String sql, Object... params) { T result = null; try { if (map != null) { result = runner.query(conn, sql, new BeanHandler<T>(cls, new BasicRowProcessor(new BeanProcessor(map))), params); } else { result = runner.query(conn, sql, new BeanHandler<T>(cls), params); } } catch (SQLException e) { e.printStackTrace(); } return result; } /* 查询(返回BeanList结果) */ public static <T> List<T> queryBeanList(Class<T> cls, Map<String, String> map, Connection conn, String sql, Object... params) { List<T> result = null; try { if (map != null) { result = runner.query(conn, sql, new BeanListHandler<T>(cls, new BasicRowProcessor(new BeanProcessor(map))), params); } else { result = runner.query(conn, sql, new BeanListHandler<T>(cls), params); } } catch (SQLException e) { e.printStackTrace(); } return result; } /* 查询指定列名的值(单条数据) */ public static <T> T queryColumn(String column, Connection conn, String sql, Object... params) { T result = null; try { result = runner.query(conn, sql, new ScalarHandler<T>(column), params); } catch (SQLException e) { e.printStackTrace(); } return result; } /* 查询指定列名的值(多条数据) */ public static <T> List<T> queryColumnList(String column, Connection conn, String sql, Object... params) { List<T> result = null; try { result = runner.query(conn, sql, new ColumnListHandler<T>(column), params); } catch (SQLException e) { e.printStackTrace(); } return result; } /* 查询指定列名对应的记录映射 */ public static <T> Map<T, Map<String, Object>> queryKeyMap(String column, Connection conn, String sql, Object... params) { Map<T, Map<String, Object>> result = null; try { result = runner.query(conn, sql, new KeyedHandler<T>(column), params); } catch (SQLException e) { e.printStackTrace(); } return result; } /* 更新(包括UPDATE、INSERT、DELETE,返回受影响的行数) */ public static int update(Connection conn, String sql, Object... params) { int result = 0; try { result = runner.update(conn, sql, params); } catch (SQLException e) { e.printStackTrace(); } return result; } } |
文章作者: Lee
本文地址: https://www.pomelolee.com/1763.html
除非注明,Pomelo Lee文章均为原创,转载请以链接形式标明本文地址
一条评论 to 支持emoji图标直接存储到数据库,更改MySQL数据库的编码为utf8mb4
修改数据库字符集:
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE …];
把表默认的字符集和所有字符列(CHAR,VARCHAR,TEXT)改为新的字符集:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE …]
如:ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
只是修改表的默认字符集:
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE…];
如:ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
修改字段的字符集:
ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE …];
如:ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;
查看数据库编码:
SHOW CREATE DATABASE db_name;
查看表编码:
SHOW CREATE TABLE tbl_name;
查看字段编码:
SHOW FULL COLUMNS FROM tbl_name;
Leave a comment
Search
相关文章
热门文章
最新文章
文章分类
- ajax (10)
- algorithm-learn (3)
- Android (6)
- as (3)
- computer (85)
- Database (30)
- disucz (4)
- enterprise (1)
- erlang (2)
- flash (5)
- golang (3)
- html5 (18)
- ios (4)
- JAVA-and-J2EE (186)
- linux (143)
- mac (10)
- movie-music (11)
- pagemaker (36)
- php (50)
- spring-boot (2)
- Synology群晖 (2)
- Uncategorized (6)
- unity (1)
- webgame (15)
- wordpress (33)
- work-other (2)
- 低代码 (1)
- 体味生活 (40)
- 前端 (21)
- 大数据 (8)
- 游戏开发 (9)
- 爱上海 (19)
- 读书 (4)
- 软件 (3)
2017 年 11 月 18 日