MySQL中的编码问题

1.查看MySQL数据库的默认编码

　　(1).使用status命令

复制代码 1 mysql> status 2 -------------- 3 mysql Ver 14.14 Distrib 5.5.28, for Win64 (x86) 4 5 Connection id: 2 6 Current database: day17 7 Current user: root@localhost 8 SSL: Not in use 9 Using delimiter: ; 10 Server version: 5.5.28 MySQL Community Server (GPL) 11 Protocol version: 10 12 Connection: localhost via TCP/IP 13 Server characterset: utf8 14 Db characterset: utf8 15 Client characterset: utf8 16 Conn. characterset: utf8 17 TCP port: 3306 18 Uptime: 1 hour 14 min 9 sec

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
mysql> status
--------------
mysql
Ver 14.14 Distrib 5.5.28, for Win64 (x86)

Connection id:
2
Current database:
day17
Current user:
root@localhost
SSL:
Not in use
Using delimiter:
;
Server version:
5.5.28 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:
1 hour 14 min 9 sec

　　(2)使用show variables命令

复制代码 1 mysql> show variables like '%char%'; 2 +--------------------------+----------------------------------------------------+ 3 | Variable_name | Value | 4 +--------------------------+----------------------------------------------------+ 5 | character_set_client | utf8 | 6 | character_set_connection | utf8 | 7 | character_set_database | utf8 | 8 | character_set_filesystem | binary | 9 | character_set_results | utf8 | 10 | character_set_server | utf8 | 11 | character_set_system | utf8 | 12 | character_sets_dir | D:WorkToolMySQLMySQL Server 5.5sharecharsets | 13 +--------------------------+----------------------------------------------------+ 14 8 rows in set (0.08 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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
| D:WorkToolMySQLMySQL Server 5.5sharecharsets |
+--------------------------+----------------------------------------------------+
8 rows in set (0.08 sec)

2.查看某个数据库的默认编码

　　首先执行use db_name命令，切换到要查看的数据库，再执行上述命令。

3.改变数据库的默认编码

　　安装MySQL数据库时的默认编码是latin1，实际使用时可能要使用其他编码。下文描述如何将默认编码改为utf8：

　　首先修改MySQL的配置文件/etc/mysql/my.cnf：
　　在[client]下追加：
　　default-character-set=utf8

　　在[mysqld]下追加：
　　character-set-server=utf8

　　在[mysql]下追加：
　　default-character-set=utf8

　　修改完毕后，使用如下命令之一重启mysql服务：
　　service mysql restart
　　/etc/init.d/mysql restart

　　改变后，之前手工创建的数据库如未显式指定编码，则其编码仍是默认的latin1，可使用如下命令更改编码：
　　mysql> alter database db_name CHARACTER SET utf8;