【MySQL】之解决字符集乱码问题

墨蓝 2023-02-13 14:35 17阅读 0赞

如果我们字段值里面存储的字符编码为 GB2312 而数据库使用的字符编码为 utf8 则查询结果的字符将会出现乱码问题:

mysql> select * from `user`;
    +----+-----------+-----------+-----------+-------------+
    | id | name      | password  | address   | phone       |
    +----+-----------+-----------+-----------+-------------+
    |  1 | 绔犲瓙鎬¡  | qwerty    | Beijing   | 13788658672 |
    |  2 | 閮?瘜鍩    | abc123    | HongKong  | 15678909898 |
    |  3 | 鏋楀織棰   | 654321    | Taiwan    | 18612124565 |
    |  4 | 姊侀潤鑼¹  | 987654367 | malaixiya | 18912340998 |
    +----+-----------+-----------+-----------+-------------+

解决这个问题之前我们先了解一下 MySQL 数据库相关的字符如编码变量：

如果想要查询结果显现正常的话我们只需要修改 character\_set\_results 就可以了：

set character_set_results=gb2312;

或者临时改变字符集：

SET NAMES GB2312;

这样查询结果就可以显示正常了：

mysql> select * from `user`;
    +----+----------+-----------+-----------+-------------+
    | id | name     | password  | address   | phone       |
    +----+----------+-----------+-----------+-------------+
    |  1 | 章子怡    | qwerty    | Beijing   | 13788658672 |
    |  2 | 郭富城    | abc123    | HongKong  | 15678909898 |
    |  3 | 林志颖    | 654321    | Taiwan    | 18612124565 |
    |  4 | 梁静茹    | 987654367 | malaixiya | 18912340998 |
    +----+----------+-----------+-----------+-------------+

如果想要插入 GB2312 编码的字符，可以修改变量：

set character_set_database=utf8;
    set character_set_server=utf8;
    set character_set_client=gb2312;
    set character_set_connection=gb2312;