mysql编码修改

erawrq

查看mysql指定数据库编码

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> show create database php; +----------+-------------------------------------------------------------+ | Database | Create Database                                             | +----------+-------------------------------------------------------------+ | php      | CREATE DATABASE `php` /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+-------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create database mysql; +----------+----------------------------------------------------------------+ | Database | Create Database                                                | +----------+----------------------------------------------------------------+ | mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec)

查看数据库服务和客户端字符集，mysql默认编码是gbk的：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

mysql> status -------------- mysql  Ver 14.14 Distrib 5.6.17, for Win32 (x86) Connection id:          3 Current database: Current user:           root@localhost SSL:                    Not in use Using delimiter:        ; Server version:         5.6.17 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:                 6 min 13 sec Threads: 1  Questions: 481  Slow queries: 0  Opens: 260  Flush tables: 1  Open t ables: 66  Queries per second avg: 1.289 --------------

1 2	mysql> set names utf8;//临时修改编码，重启数据库还原为默认编码 Query OK, 0 rows affected (0.00 sec)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

mysql> status; -------------- mysql  Ver 14.14 Distrib 5.6.17, for Win32 (x86) Connection id:          1 Current database: Current user:           root@localhost SSL:                    Not in use Using delimiter:        ; Server version:         5.6.17 MySQL Community Server (GPL) Protocol version:       10 Connection:             localhost via TCP/IP Server characterset:    latin1 Db     characterset:    latin1 Client characterset:    utf8 Conn.  characterset:    utf8 TCP port:               3306 Uptime:                 2 min 26 sec Threads: 1  Questions: 10  Slow queries: 0  Opens: 70  Flush tables: 1  Open tab les: 63  Queries per second avg: 0.068 --------------

永久生效，修改my.ini文件
[client]
#password        = your_password
port                = 3306
socket                = /tmp/mysql.sock
default-character-set=utf8        //加上这一行，重启mysql服务

1 2 3 4 5

mysql> status Server characterset:    latin1 Db     characterset:    latin1 Client characterset:    utf8 Conn.  characterset:    utf8

修改某个数据库编码

1 2 3 4 5 6 7 8

mysql> alter database res_gul character set utf8; mysql> use res_gul mysql> status Server characterset:    latin1 Db     characterset:    utf8 Client characterset:    utf8 Conn.  characterset:    utf8

设置数据库编码 /etc/my.cnf
[mysqld]
default-character-set=utf8
...
[client]
default-character-set=utf8

重启数据库后生效

1 2 3 4 5

mysql> status Server characterset:    utf8 Db     characterset:    utf8 Client characterset:    utf8 Conn.  characterset:    utf8