mysql 数据库修改编码为 utf8mb4

  1. 1. 准备工作
  2. 2. 修改配置文件
  3. 3. 重启数据库,检查变量
  4. 4. 数据库连接配置
  5. 5. 将数据库和已经建好的表也转换成 utf8mb4
  6. 6. 期间遇到的问题

mysql 的 utf-8 编码只支持 3 字节的数据,而移动端的表情数据是 4 个字节的字符,因此在向 mysql 中插入数据时会报如下错误。

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)

该问题可以通过将 mysql 的编码改为 utf8mb4 来解决。utf8mb4 是 utf8 的超集,兼容 utf8,并且可以存储 4 个字节的字符。

准备工作

  1. mysql 版本必须是 5.5.5+
  2. mysql 驱动版本必须是 5.1.13+

修改配置文件

修改 my.cnf 配置文件,内容如下。

1
2
3
4
5
6
7
8
9
[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’

重启数据库,检查变量

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 (默认的内部操作字符集)

数据库连接配置

参数中 characterEncoding=utf8 会被自动识别为 utf8mb4,也可以不加这个参数,会自动检测。

将数据库和已经建好的表也转换成 utf8mb4

  1. 更改数据库编码

    1
    ALTER DATABASE `tablename` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  2. 更改表编码

    1
    ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

期间遇到的问题

  1. my.cnf 文件在哪?
    通过 mysql --help | grep my.cnf 查看文件位置,显示顺序按照使用优先级排序。如下图。
    mysql --help | grep my.cnf

  2. 使用 root 用户执行 mysqld restart 失败,提示 Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!,如下图。
    run as root
    使用 mysqld restart --user=root 命令

  3. 执行 mysqld restart --user=root 命令失败,提示 unable to lock ./ibdata1 error check that you do not already hava another mysqld process using the same InnoDB data or log files.

    直接通过 ps -ef | grep mysql 找到 mysql 进程,强杀进程,然后通过 mysqld --user=root 启动,如果通过 mysqld start --user=root 启动会报错,提示 Too many arguments (first extra is 'start').,如下图。
    Too many arguments

原文转自 改MySQL数据库的编码为utf8mb4,然后自行整理了修改过程中遇到的问题。