MySQL5.7.34-log修改默认字符集,解决中文问号问题

2021/07/16 15:52
阅读数 276

1. 首先查看是否字符集不是支持中文

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   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8mb4                                                 |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
| collation_connection     | utf8mb4_general_ci                                      |
| collation_database       | latin1_swedish_ci                                       |
| collation_server         | latin1_swedish_ci                                       |
+--------------------------+---------------------------------------------------------+
11 rows in set, 1 warning (0.00 sec)

2.编辑 my.ini 文件

文件位置 C:\ProgramData\MySQL\MySQL Server 5.7\my.ini ,添加部分如下:

[client]
default-character-set = utf8mb4
port=3306

[mysql]
no-beep
default-character-set = utf8mb4

[mysqld]
init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4
collation-server=utf8mb4_unicode_ci
port=3306

3. 重启查看

重启时可以在屏幕左下角的搜索框中直接搜索服务,然后重启即可。查看仍然是打开查询:

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                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
| collation_connection     | utf8mb4_general_ci                                      |
| collation_database       | utf8mb4_unicode_ci                                      |
| collation_server         | utf8mb4_unicode_ci                                      |
+--------------------------+---------------------------------------------------------+
11 rows in set, 1 warning (0.00 sec)

这样修改之后,虽然新建数据库新建表都是 utf8了,但是原来数据库和数据表还要手工修改。

4. 修改原有存在的数据库及表

4.1.修改数据库

修改已经存在的数据库编码 ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; 其中 da_name 为需要修改的数据库名。

4.2.修改表

修改数据表编码用了 segmentfault 上的大神批量生成修改数据表修改语句方案。

SELECT 
CONCAT("ALTER TABLE `", TABLE_NAME,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") 
AS target_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="db_name"
AND TABLE_TYPE="BASE TABLE"

其中 da_name 为需要修改的数据库名,这样就会把该数据库中数据表修改语句生成出来,再运行即可。

5. 拓展阅读

展开阅读全文
加载中

作者的其它热门文章

打赏
0
0 收藏
分享
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部