mysql统一(修改)字符编码

Windws:

查看当前字符编码：

mysql> \s
--------------
mysql Ver 14.14 Distrib 5.6.43, for Win64 (x86_64)

Connection id: 5
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.6.43 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: 15 min 0 sec

Threads: 1 Questions: 16 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.017
--------------

修改配置文件：

修改mysql安装目录下的配置文件my.ini，默认没有这个文件，但有一个my-default.ini文件，我们这里新建一个my.ini 文件进行修改：

文件内容为：

# mysql5.5以下用下面内容：
[mysqld]
default-character-set=utf8 
[client]
default-character-set=utf8 
[mysql]
default-character-set=utf8

# mysql5.5 及以上用下面内容：
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

然后重启服务。

再次登录查看字符编码：

mysql> \s
--------------
mysql Ver 14.14 Distrib 5.6.43, for Win64 (x86_64)

Connection id: 1
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.6.43 MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 11 sec

Threads: 1 Questions: 5 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.454
--------------

在my.ini文件中可配置的一些其他参数：

强调：配置文件中的注释可以有中文，但是配置项中不能出现中文

#在mysql的解压目录下，新建my.ini,然后配置
#1. 在执行mysqld命令时，下列配置会生效，即mysql服务启动时生效
[mysqld]
;skip-grant-tables
port=3306
character_set_server=utf8
default-storage-engine=innodb
innodb_file_per_table=1


#解压的目录
basedir=E:\mysql-5.7.19-winx64
#data目录
datadir=E:\my_data #在mysqld --initialize时，就会将初始数据存入此处指定的目录，在初始化之后，启动mysql时，就会去这个目录里找数据



#2. 针对客户端命令的全局配置，当mysql客户端命令执行时，下列配置生效
[client]
port=3306
default-character-set=utf8
user=root
password=123

#3. 只针对mysql这个客户端的配置，2中的是全局配置，而此处的则是只针对mysql这个命令的局部配置
[mysql]
;port=3306
;default-character-set=utf8
user=egon
password=4573


#！！！如果没有[mysql],则用户在执行mysql命令时的配置以[client]为准

Linux平台：

查看当前字符编码：

MariaDB [(none)]> \s
--------------
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 2
Current database: 
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.60-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 24 min 37 sec

Threads: 1 Questions: 5 Slow queries: 0 Opens: 0 Flush tables: 2 Open tables: 26 Queries per second avg: 0.003
--------------

修改配置文件 /etc/my.cnf

内容如下，根据内容自行进行增删：

# mysql5.5以下用下面内容：
[mysqld]
default-character-set=utf8 
[client]
default-character-set=utf8 
[mysql]
default-character-set=utf8

# mysql5.5 及以上用下面内容：
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

重启服务：

[root@localhost ~]# systemctl restart mariadb

再次查看字符编码：

MariaDB [(none)]> \s
--------------
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 2
Current database: 
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.60-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 11 sec

Threads: 1 Questions: 5 Slow queries: 0 Opens: 0 Flush tables: 2 Open tables: 26 Queries per second avg: 0.454
--------------