mysql 字符集编码的修改_MySQL 修改字符集编码

查看数据库创建信息:

mysql>show create database oldboydb;

+----------+-------------------------------------------------------------------+

| Database | Create Database                                                   |

+----------+-------------------------------------------------------------------+

| oldboydb | CREATE DATABASE `oldboydb` /*!40100DEFAULT CHARACTER SET utf8 */ |

+----------+-------------------------------------------------------------------+

查看MySQL初始化信息:

mysql> \s

--------------

mysql  Ver 14.14 Distrib 5.6.44, for Win64 (x86_64)

Connection id:          1

Current database:

Current user:           root@localhost

SSL:                    Not in use

Using delimiter:        ;

Server version:         5.6.44 MySQL Community Server (GPL)

Protocol version:       10

Connection:             localhost via TCP/IP

Server characterset:    latin1  # 服务端编码

Db     characterset:    latin1  # 数据库默认编码

Client characterset:    gbk  # 现在用的客户端编码

Conn.  characterset:    gbk   # 双方连接的编码

TCP port:               3306

Uptime:                 3 hours 29 min 43 sec

Threads: 1  Questions: 8  Slow queries: 0  Opens: 67  Flush tables: 1  Open tabl

es: 60  Queries per second avg: 0.000

--------------

查看MySQL(服务端/客户端/数据库)编码:

mysql> show variables like '%char%';

+--------------------------+----------------------------------------------+

| Variable_name            | Value                                        |

+--------------------------+----------------------------------------------+

| character_set_client     | gbk                                          |

| character_set_connection | gbk                                      |

| character_set_database   | latin1                                       |

| character_set_filesystem | binary                                       |

| character_set_results    | gbk                                       |

| character_set_server     | latin1                                       |

| character_set_system     | utf8                                         |

| character_sets_dir       | G:\mysql\mysql-5.6.44-winx64\share\charsets\ |

+--------------------------+----------------------------------------------+

8 rows in set (0.00 sec)

出现乱码的原因: 客户端MySQL的字符集和服务端的字符集不一样.

character_set_client / character_set_connection 和 character_set_results被称为客户端编码.

插入数据前,先执行修改编码的指令: set names latin1;

mysql> set names latin1; (临时生效)

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%char%';

+--------------------------+----------------------------------------------+

| Variable_name            | Value                                        |

+--------------------------+----------------------------------------------+

| character_set_client     | latin1                                       |

| character_set_connection | latin1                                     |

| character_set_database   | latin1                                       |

| character_set_filesystem | binary                                       |

| character_set_results    | latin1                                 |

| character_set_server     | latin1                                       |

| character_set_system     | utf8                                         |

| character_sets_dir       | G:\mysql\mysql-5.6.44-winx64\share\charsets\ |

+--------------------------+----------------------------------------------+

8 rows in set (0.00 sec)

------------------------------------------------------------------永久修改编码------------------------------------------------------------------

查看mysql启动时加载my.ini的顺序:

C:\Users\Administrator>mysql --help | findstr my.ini

C:\windows\my.ini C:\windows\my.cnf C:\my.ini C:\my.cnf G:\mysql\mysql-5.6.44-wi

nx64\my.ini G:\mysql\mysql-5.6.44-winx64\my.cnf

查看端口:

C:\Users\Administrator>netstat -ano | findstr "3306"

TCP    0.0.0.0:3306           0.0.0.0:0              LISTENING       1800

TCP    [::]:3306              [::]:0                 LISTENING       1800

进入G:\mysql\mysql-5.6.44-winx64,copy一份my-default.ini文件,改名为my.ini文件,如果是linux环境,文件名为my.cnf,使用Notepad++打开,my.ini内容:

[mysql]

user=root

password=666

重启mysql服务

C:\Users\Administrator>net stop mysqld

mysqld 服务正在停止.

mysqld 服务已成功停止。

C:\Users\Administrator>net start mysqld

mysqld 服务正在启动 .

mysqld 服务已经启动成功。

C:\Users\Administrator>mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user();   # 查看当前登录的用户

+----------------+

| user()         |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

mysql>show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| oldboydb           |

| performance_schema |

| test               |

+--------------------+

5 rows in set (0.00 sec)

mysql>

my.ini文件内容:

[mysqld]

character_set_server=utf8

collation-server=utf8_general_ci

port=3306

[mysql]

user=root

password=666

default-character-set=utf8

重启Mysql服务

C:\Users\Administrator>net stop mysqld

mysqld 服务正在停止.

mysqld 服务已成功停止。

C:\Users\Administrator>net start mysqld

mysqld 服务正在启动 .

mysqld 服务已经启动成功。

C:\Users\Administrator>mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%char%';

+--------------------------+----------------------------------------------+

| Variable_name            | Value                                        |

+--------------------------+----------------------------------------------+

| character_set_client     | utf8                                         |

| character_set_connection | utf8                                         |

| character_set_database   | utf8                                    |

| character_set_filesystem | binary                                       |

| character_set_results    | utf8                                         |

| character_set_server     | utf8                                         |

| character_set_system     | utf8                                     |

| character_sets_dir       | G:\mysql\mysql-5.6.44-winx64\share\charsets\ |

+--------------------------+----------------------------------------------+

8 rows in set (0.00 sec)

mysql> select from \c

mysql>

命令写错了可以使用\c来结束.

总结：不乱码的思想：系统的编码、客户端、服务端、库、表、列，这几项的编码都要统一才不会出现乱码的情况。