MySQL设置utf8mb4编码

MySQL设置utf8mb4编码

一、数据库配置

== 需要MySQL的版本在5.5.3及以上才支持utf8mb4。 ==

修改配置文件my.cnf或my.ini。增加或修改utf8mb4编码相关设置。

...
[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> show variables where variable_name like 'char%' or variable_name like 'coll%';
+--------------------------+----------------------------------------------+
| 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       | /mysql/share/charsets/                       |
| collation_connection     | utf8mb4_unicode_ci                           |
| collation_database       | utf8mb4_unicode_ci                           |
| collation_server         | utf8mb4_unicode_ci                           |
+--------------------------+----------------------------------------------+
11 rows in set (0.00 sec)

二、根据项目数据库连接池的数据源添加init_connect配置

如：

• c3p0自定义utf8mb4的ConnectionCustomizer并配置connectionCustomizerClassName属性。

package com.mchange.v2.c3p0;

import java.sql.Connection;
import java.sql.Statement;

/**
 * 自定义utf8mb4的ConnectionCustomizer
 */
public class Utf8mb4ConnectionCustomizer extends AbstractConnectionCustomizer {

    @Override
    public void onAcquire(Connection c, String parentDataSourceIdentityToken) throws Exception {
        Statement stmt = null;
        try {
            stmt = c.createStatement();
            stmt.executeUpdate("set names utf8mb4");
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
    }
}

<property name="connectionCustomizerClassName" value="com.mchange.v2.c3p0.Utf8mb4ConnectionCustomizer" />

• druid直接配置connectionInitSqls属性。

<property name="connectionInitSqls" value="set names utf8mb4" />

• 其它同理。

最终效果

通过数据库客户端与浏览器都可以正常查看Emoji数据，参见下图！