MySQL 修改字符集编码
查看数据库创建信息:
mysql> show create database oldboydb;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| oldboydb | CREATE DATABASE `oldboydb` /*!40100 DEFAULT 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来结束.
总结：不乱码的思想：系统的编码、客户端、服务端、库、表、列，这几项的编码都要统一才不会出现乱码的情况。
