支持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;
    }
}

Tags: , ,

文章作者: Lee

本文地址: https://www.pomelolee.com/1763.html

除非注明,Pomelo Lee文章均为原创,转载请以链接形式标明本文地址

一条评论 to 支持emoji图标直接存储到数据库,更改MySQL数据库的编码为utf8mb4

Lee
2017 年 11 月 18 日

修改数据库字符集:

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

文章分类

Links

Meta