MySQL emoji表情插入

MySQL emoji表情插入操作步骤

step1：修改mysql配置文件（windows:my.ini；linux:my.cnf）

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
#character-set-server=utf8 这行注释掉
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'

step2：重启mysql

service mysql restart

step3:修改您要使用的库表字段的编码为utf8mb4

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE     utf8mb4_general_ci;

step4:测试执行包含emoji的sql语句

INSERT INTO `db_name`.`table_name` (field_name) VALUES ('🙂') 

如果还是不行，可以尝试一下手动去数据库执行一下SET NAMES utf8mb4;我之前就遇到这种情况可能是修改配置的文件中的init_connect = 'SET NAMES utf8mb4'没生效

step5：修改jdbc的mysql连接url，去掉其中的characterEncoding=utf8

jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true

改成

jdbc:mysql://localhost:3306/test?useUnicode=true&useSSL=true