mysql统一编码_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

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