mysql数据库存入emoji表情

环境：CentOS7 64 和 win10 64

 

前言

　　随着互联网发展，现在 emoji 表情越来越多，记得前几年还在做微信公众号时，此类表情就会在微信名字中出现了，在存储到 MySQL 时因为此类字符比较特殊（emoji为4字节字符，utf8 正常都是3字节）存储过程中会失败，后来将库表更改为了 utf8mb4 编码格式就好了，当时也没有记录下来。最近在写内容存储时再次碰到此问题，而且在更改 utf8mb4 编码后，存进去的表情符合不是变空白就是已 ???? 问好的形式存进去，现在解决了，是由于在连接 MySQL 数据库时设定的编码格式还是utf8，特来记录下。

 

主要是修改以下项

[client]

default-character-set=utf8  改为 utf8mb4

[mysql]

default-character-set=utf8  改为 utf8mb4

[mysqld]

# The TCP/IP Port the MySQL Server will listen on
character-set-client-handshake = FALSE
collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

更改完成后一定要重启数据库服务。

 

查看数据库编码已经是 utf8mb4 了

 

 

 

CentOS 7 安装的数据库是 MariaDB

　　配置文件在 /etc/my.cnf.d/server.cnf

　　原配置文件

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

# this is only for embedded server
[embedded]

# This group is only read by MariaDB-5.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mysqld-5.5]

# These two groups are only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

[mariadb-5.5]

 

更改后文件:

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

# this is only for the mysqld standalone daemon
[mysqld]
character-set-client-handshake = FALSE
collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

# this is only for embedded server
[embedded]

# This group is only read by MariaDB-5.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mysqld-5.5]

# These two groups are only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

[mariadb-5.5]

 

 

 

Win10 

　　配置文件在  我的在 C:\Program Files (x86)\MySQL\MySQL Server 5.5\my.ini     （一般找到安装目录就找到配置文件了）

　　更改前  截取的部分内容

 

[client]

port=36973
default-character-set=utf8
[mysql]

default-character-set=utf8


# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=36973
character_set_server=utf8 

 

 

更改后

[client]

port=3306
default-character-set=utf8mb4

[mysql]

default-character-set=utf8mb4


# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306
character-set-client-handshake = FALSE
collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4