查看mysql指定数据库编码
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的:
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 --------------
mysql> set names utf8;//临时修改编码,重启数据库还原为默认编码 Query OK, 0 rows affected (0.00 sec)
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服务
mysql> status Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8
修改某个数据库编码
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
重启数据库后生效
mysql> status Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8