首页 » 编程 » Python-8.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
--------------

 

 

 

 

 

 

 

 

发表评论

验证码加载中....