一般我們數(shù)據(jù)編碼格式默認(rèn)設(shè)置為utf-8, 但是utf-8并不支持表情,而表情需要utf8mb4字段(向下兼容utf-8)
1 : 在終端或者SSH shell登錄服務(wù)器,
2 : 修改 my.cnf (linux)或者mysql.ini (win)
vim /etc/my.cnf
添加或者修改以下內(nèi)容:
[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'
3 : 重啟 MySQL Server:
service mysql restart
4 : 修改字段,或者表,或者數(shù)據(jù)庫編碼格式:
table_name 和 column_name 根據(jù)你的名稱修改
如果只是某個(gè)字段支持,就用1
1 修改數(shù)據(jù)庫字符集：
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
2 修改表的字符集：
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3 修改字段的字符集：
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
5 : 查看編碼信息:
SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
先登錄mysql:
mysql -u 賬號(hào) -p
....
mysql>
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
結(jié)果:
mysql> 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 |
+--------------------------+--------------------+
10 rows in set (0.00 sec)
設(shè)置完成!
其中:
character_set_client為客戶端編碼方式；
character_set_connection為建立連接使用的編碼；
character_set_database數(shù)據(jù)庫的編碼；
character_set_results結(jié)果集的編碼；
character_set_server數(shù)據(jù)庫服務(wù)器的編碼；
只要保證以上四個(gè)采用的編碼方式一樣，就不會(huì)出現(xiàn)亂碼問題。
6 : 如果不成功,看看數(shù)據(jù)庫連接:
服務(wù)器連接數(shù)據(jù)庫 Connector/J的連接參數(shù)中，不要加characterEncoding參數(shù)。 不加這個(gè)參數(shù)時(shí)，默認(rèn)值就時(shí)autodetect。
#jdbc_url=jdbc:mysql://localhost:3306/user?useUnicode=true&
characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
去掉characterEncoding=UTF-8
