MySQL的字符集
admin
2023-04-23 08:42:48
0次
#1， 字符集相关的参数名和概念
MySQL的字符集设置比较自由。可以设置很多种组合，相关的变量和参数有：
(root@localhost)[sample3]> show global variables like '%cha%'; +-------------------------------+----------------------------+ | 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 | /opt/mysql/share/charsets/ | | innodb_change_buffer_max_size | 25 | | innodb_change_buffering | all | +-------------------------------+----------------------------+ 10 rows in set (0.00 sec) (root@localhost)[sample3]> show variables like '%cha%'; +-------------------------------+----------------------------+ | Variable_name | Value | +-------------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /opt/mysql/share/charsets/ | | innodb_change_buffer_max_size | 25 | | innodb_change_buffering | all | +-------------------------------+----------------------------+ 10 rows in set (0.00 sec)
其中global variables表示全局变量。也就是默认情况下，新建立的数据库如果不显式的指定字符集相关参数，将使用这些参数。也叫做全局字符集变量
其中variables没带global，表示当前session生效的的参数。所谓当前session,表示如果更改过相关的参数，离开这个session以后，就会恢复默认的参数。也叫做连接时字符集变量
各个变量的概念：
1，character_set_client:客户端字符集，即数据在client端时字符集状态。
2，character_set_connection:连接时转换字符集，即客户端和服务端连接时，字符集装换成的字符集
3，character_set_server:服务端处理时候使用的字符集
4，character_set_database:数据库层面存储默认使用的字符集
5，character_set_results:数据返回时所用的字符集
指定字符集参数有多种方式，
1，在编译时指定，主要是：
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8-general_ci \
其中CHARSET是指字符集，COLLATION是指相关的校对规则（也称排序规则）
2，参数文件，也就是my.cnf中设定
character_set_server=utf8
collation_server=utf8_general_ci
参数人间的设定将会覆盖编译时设定的字符集和校对规则。
3，启动MySQL服务的时候指定：
--character_set_server: 指定全局粒度的默认字符集
--collation_server：指定全局粒度的默认校对规则
启动时指定的参数将覆盖参数文件以及编译时指定的字符集和校对规则。
这些参数，如果从大方向分的话，可以分为两类：
1，连接时使用的字符集，即为show variable like '%character%' 显示的那些字符集
2，存储时使用的字符集，分为4个级别
1）SERVER，全局级别
2）DATABASE，数据库级别
3）TABLE，表级别
4）column，列级别
2# MySQL查询的基本过程和乱码的形成以及如何避免乱码
1）查询的基本过程
MySQL查询基本过程如下：
1， 程序将字符转换成二进制格式
2，MySQL 客户端发出查询（client端字符集）====>到达server端连接器（connection字符集）====>
内部转换并查询（列字符集、表字符集、数据库字符集、server端字符集,转换优先级逐级递减）=====>
查询结果返回给result（result字符集）
这里先要回答一个latin字符集为何能存放汉字的问题（汉字每个字符占用2个字节长度，latin不支持双字节长度）。
实际上是因为OS/APP层已经将汉字转换为单字符串的形式。一般来说，为了正常显示和处理汉字，
OS层面和程序层面也一定设定了字符集，这个字符集就会将汉字先一步处理成二进制。比如OS和程序层面设置的是UTF8，
那么实际上输入一个汉字，我们在mysql client端实际上是获得3个单字节，而非双字节，这样latin字符集就可以处理了。
这些字符集的转换，实际上是字符长度的转换。比如如果CLIENT端时LATIN1，connection是UTF8，
那么就会发生3个字节长度转换成6个字节长度。每个单字符后面都会被填0，这样变长了自然是没事的，
顶多显示的时候再转回来，把后面填的零都切掉。但是反过来，如果是client段是utf8，connection是latin1，
那么3个字节长度的utf8就会被转成1个字节长度的latin1，后两位丢失，以后是怎么都变不回来的。
这种丢失非零位的过程是不可逆的。所以我们必须得保证设定字符集的时候
（列字符集、表字符集、数据库字符集、server段字符集）>= connection字符集 >= client字符集来避免字符编码丢失问题。
一种可能发生的状况： client(latin1)==>connection(utf8)==>服务端内部的存储时字符集（latin1），
并不会发生字符编码丢失，因为connection转换到存储时字符集时，只是切掉了client转connection时后面填的零，相当于转回来了。
3，实验：
1）client字符集为utf8,connection 为latin1，存储字符集为latin1
(root@localhost)[sample3]> show create table test2;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
`id` int(11) DEFAULT NULL,
`name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(root@localhost)[sample3]> show variables like '%cha%';
+-------------------------------+----------------------------+
| Variable_name | Value |
+-------------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /opt/mysql/share/charsets/ |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
+-------------------------------+----------------------------+
10 rows in set (0.00 sec)
(root@localhost)[sample3]> insert into test2 values (1,'中国');
Query OK, 1 row affected, 1 warning (0.00 sec)
(root@localhost)[sample3]> select * from test2;
+------+------+
| id | name |
+------+------+
| 1 | ?? |
+------+------+
1 row in set (0.00 sec)
这种情况，已经丢失了字符，转换result是没有用的。
(root@localhost)[sample3]> set character_set_results=latin1;
Query OK, 0 rows affected (0.00 sec)
(root@localhost)[sample3]> select * from test2;
+------+------+
| id | name |
+------+------+
| 1 | ?? |
+------+------+
1 row in set (0.00 sec)
2）client字符集为utf8,connection为utf8，存储字符集为latin1
(root@localhost)[sample3]> show variables like '%cha%';
+-------------------------------+----------------------------+
| Variable_name | Value |
+-------------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /opt/mysql/share/charsets/ |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
+-------------------------------+----------------------------+
10 rows in set (0.00 sec)
(root@localhost)[sample3]>
(root@localhost)[sample3]> insert into test2 values (1,'中国');
ERROR 1366 (HY000): Incorrect string value: '\xE4\xB8\xAD\xE5\x9B\xBD' for column 'name' at row 1
(root@localhost)[sample3]>
这里直接报错，5.6以后加强了数据库数据的安全性，因为会丢失数据，所以不允许插入。
3）client字符集为latin1，connection为utf8，存储字符集为latin1
(root@localhost)[sample3]> show variables like '%cha%';
+-------------------------------+----------------------------+
| Variable_name | Value |
+-------------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /opt/mysql/share/charsets/ |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
+-------------------------------+----------------------------+
10 rows in set (0.00 sec)
(root@localhost)[sample3]> select * from test2;
+------+---------------+
| id | name |
+------+---------------+
| 1 | ä¸å›½ |
+------+---------------+
1 row in set (0.00 sec)
乱码了，这里为何会乱码呢？按理说转换过程中只会切掉填充的零才对。实际上是result的问题。这个result是utf8，
而client存的时候就是3位，到connection转到6位，到存储时转回3位，这时候到result又转到6位，自然是乱码的。
只要result转回latin1，就可以了。
(root@localhost)[sample3]> set character_set_results=latin1;
Query OK, 0 rows affected (0.00 sec)
(root@localhost)[sample3]> select * from test2;
+------+--------+
| id | name |
+------+--------+
| 1 | 中国 |
+------+--------+
1 row in set (0.00 sec)
4)不丢失字符，但是字符集不同的乱码.
(root@localhost)[sample2]> show create table test;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(root@localhost)[sample2]> show variables like '%cha%';
+-------------------------------+----------------------------+
| Variable_name | Value |
+-------------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /opt/mysql/share/charsets/ |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
+-------------------------------+----------------------------+
10 rows in set (0.00 sec)
(root@localhost)[sample2]> insert into test values('中国');
Query OK, 1 row affected (0.01 sec)
(root@localhost)[sample2]>
(root@localhost)[sample2]> select * from test;
+------+
| name |
+------+
| |
+------+
1 row in set (0.00 sec)
(root@localhost)[sample2]> set character_set_results=utf8;
Query OK, 0 rows affected (0.00 sec)
(root@localhost)[sample2]> select * from test;
+--------+
| name |
+--------+
| 中国 |
+--------+
1 row in set (0.00 sec)
(root@localhost)[sample2]>
修改客户端字符集的5中方法：
1、 运行，set names <字符集>；
2、 在SQL文件中指定set names <字符集>；,用source命令导入sql文件
如： mysql> source test.sql
3、 在SQL文件中指定set names <字符集>；,然后通过重定向符，或者-e参数来执行
[root@mysql01 3307]# vi test.sql
set names utf8;
select * from mysql.user;
[root@mysql01 3307]# mysql -uroot -p -S /data/3307/mysql.sock -S /data/3307/mysql.sock -e "set names <字符集>； select * from .;"
4、 通过指定mysql命令的字符集参数实现 --default-character-set=<字符集>
[root@mysql01 3307]# mysql -uroot -p -S /data/3307/mysql.sock --default-chratacter-set=utf8
上一篇：mysql日志分析
相关内容
热门资讯
“台独”顽固分子沈伯洋参选台北...
【环球时报特约记者 陈立非】台湾今年年底举行“九合一”选举，国民党很早就确定由现任台北市市长蒋万安争...
伊拉克和巴基斯坦据称已分别同伊...
总台记者当地时间5月12日获悉，伊拉克和巴基斯坦已分别同伊朗签订协议，以从海湾地区运输石油和液化天然...
京沪高铁“涨价”，调价背后有何...
昨天（11日），京沪高铁发布公告称，决定对京沪高速线、合蚌高速线动车组列车公布票价进行优化调整，时速...
第三方样品A厂家生产支架样品测...
第三方样品A厂家生产支架样品测试实验报告 一、检测范围 本次检测对象为A厂家生产的XX型医用金属...
伊朗德黑兰地区发生4.6级地震
总台记者获悉，当地时间5月12日23时47分左右，伊朗德黑兰地区发生4.6级地震。震中位于德黑兰省和...
被科威特指控“武装渗透”布比延...
新华社科威特城/德黑兰5月12日电（记者尹炣 陈霄）科威特政府12日指认，伊朗伊斯兰革命卫队多名武装...
酒吧办护士制服派对被指低俗，当...
据媒体报道，5月12日国际护士节，浙江衢州有网友发帖称，当地APK·ELITE CLUB酒吧举办所谓...
珠海冠宇获得发明专利授权：“一...
证券之星消息，根据天眼查APP数据显示珠海冠宇（688772）新获得一项发明专利授权，专利名为“一种...
大华申请数据写入方法专利，提高...
国家知识产权局信息显示，浙江大华技术股份有限公司申请一项名为“数据写入方法、电子设备及计算机可读存储...
现在，赖清德更焦虑了
执笔/月半刀&宝刀刀&胡一刀5月13日，美国总统特朗普将开启访华行程。外界高度关注此次会晤中双方将如...