最近学习mysql字符集的问题,有许多困惑,通过搜索相关内容现总结在此,希望大神不吝赐教;
Mysql版本5.6.37
1、查看现在数据库的编码情况
mysql Ver 14.14 Distrib 5.6.37, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 2
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.37-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 14 min 37 sec
Threads: 1 Questions: 32 Slow queries: 0 Opens: 71 Flush tables: 1 Open tables: 64 Queries per second avg: 0.036
目前配置都是utf8格式的,修改/etc/my.cnf配置文件下的character-set-server 为gbk,重启mysql数据库后,server characterset 和db characterset 都变成了gbk格式
mysql Ver 14.14 Distrib 5.6.37, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 2
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.37-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: gbk
Db characterset: gbk
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 15 min 55 sec
Threads: 1 Questions: 43 Slow queries: 0 Opens: 71 Flush tables: 1 Open tables: 64 Queries per second avg: 0.045
2、关于client character和conn characterset(下面来自http://blog.csdn.net/mergerly/article/details/21531759大神的实验)
数据库表中字段的字符集设置 。show create table TableName 或 show full columns from tableName
总结:无论提交时候用什么显示sql语句,进入服务器后都会转换成服务器相应的编码格式。下次读取的时候服务器会翻译成连接时候的显示格式,所以说这两个格式务必要相同或兼容。
连接字符集:没有显式设置,默认为latin1
页面字符集:gbk
存入过程:
1)页面用GBK表示的SQL向服务器提交存入请求;
2)默认情况下(不用Set Names ‘??’)服务器用latin1打开连接;
3)服务器误认为当前的SQL语句是用latin1表示的;
4)服务器将GBK字符当作latin1字符,错误的运用“latin1转UTF-8函数”将mysql字符转换后存入UTF-8字段中;
5)( 错误的latin1(其实是GBK) => 错误的UTF-8)
6)如果用phpmyadmin打开该表(用utf8连接)将会看到该字段为乱码;
读取过程:
1)默认情况下(不用Set Names ‘??’)服务器用latin1打开连接;
2)服务器将UTF-8字段中的值转换为latin1返回给客户端;
3)(错误的UTF-8 => 错误的latin1(其实是GBK))该过程为存入过程5的逆过程。(刚好错错得对了)
4)将服务器误认为是latin1的GBK编码按页面字符集正常显示;
实验:
1、数据库当前的配置信息
*************************** 1. row ***************************
Database: aaa
Create Database: CREATE DATABASE `aaa` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
*************************** 1. row ***************************
Table: a
Create Table: CREATE TABLE `a` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
+------+--------+
| id | name |
+------+--------+
| 1 | 汤姆 |
+------+--------+
1 row in set (0.00 sec)
修改数据库client character和conn characterset 为latin1后再次查询,显示乱码
set names latin1;
+------+------+
| id | name |
+------+------+
| 1 | ?? |
+------+------+
1 row in set (0.00 sec)