win10 mysql修改默认编码_Windows10 修改 MySQL 5.7 默认编码

自从上次重装系统之后，装好 MySQL 5.7 之后就没管过，今天发现居然存储不了中文，使用 show variables like 'char%'; 查询发现居然不是 utf8 而是 Latin 编码，怪不得不支持中文。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)

怒修改，编辑 my.ini 文件，文件位置 C:\ProgramData\MySQL\MySQL Server 5.7\my.ini 当然可以在 Windows 服务中找到，添加部分如下[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

然后重启 MySQL5.7 服务

修改后如下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了，但是原来数据库和数据表还要手工修改。

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

修改数据表编码用了 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 为需要修改的数据库名，这样就会把该数据库中数据表修改语句生成出来，再运行即可。