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日志分析

                

                

                    下一篇：MySQL基操---各类备份与索引详解                

            

            
            

                
                
相关内容

                
            

            

            
            

                

            



        
        
    
    

    
    

        
        

            
            

                
                

                    
                    

                        
                        热门资讯
                        
                    

                    
                

                
            

            
            
            


                                
                

                    
                    

                        

                    

                    
                    
                    

                        “一次愉快的会晤”，泽连斯基披...
                        在俄乌互相加大对对方纵深目标打击之际，乌克兰总统泽连斯基7月28日造访了白宫，与美国总统特朗普举行会...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        总统连遭“逼宫”，少帅临危受命
                        躲避着燃烧瓶和火箭弹袭击，四辆苏制BMP步兵战车快速穿行在马里乌波尔市区。即将出城的路口，迎面出现1...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        独居女子在卧室玩偶内发现摄像头...
                        租房独居女子意外发现家中玩偶内藏着偷拍设备，报警后偷拍设备竟“不翼而飞”，门锁记录显示有人在她出门的...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        伊学者：乌克兰袭击行动适得其反...
                        乌克兰袭击与伊朗有关的船只后，伊朗和俄罗斯接连作出强硬回应。伊朗外交学院教授法拉吉扎德在接受凤凰卫视...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        20万人怒火不会停！蓝议员挺蒋...
                        海峡导报综合报道 在蓝白号召台湾民众上凯道呐喊“反毒台”后，台北市长蒋万安日前透露，近日不少民代私下...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        柯文哲妻子陈佩琪畅游大陆，民进...
                        台湾民众党前主席柯文哲妻子陈佩琪27日在脸书发文证实，自己早前由儿子陪同前往上海、杭州与北京旅游一周...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        高市早苗被问如何改善中日关系
                        据日本东京新闻报道，当地时间7月27日，日本首相高市早苗在首相官邸举行记者会。当被问及如何改善中日关...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        扎哈罗娃批小泉进次郎涉核言论：...
                        【环球网报道 记者 张江平】综合俄新社等媒体报道，对于日本防卫大臣小泉进次郎涉核言论，俄罗斯外交部发...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        “特高课”再现？日本强推“国家...
                        无视反对战争正义呼声 复刻战前军国主义体制日本强推“国家情报局”引发广泛担忧和批评（国际视点）本报记...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        6G相比5G不只是速度更快 将...
                        　　6G相比5G不只是速度更快　　【6G相比5G不只是速度更快】7月28日，相比5G，6G不仅是速度...