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基操---各类备份与索引详解                

            

            
            

                
                
相关内容

                
            

            

            
            

                

            



        
        
    
    

    
    

        
        

            
            

                
                

                    
                    

                        
                        热门资讯
                        
                    

                    
                

                
            

            
            
            


                                
                

                    
                    

                        

                    

                    
                    
                    

                        纪检监察工作高质量发展取得新进...
                        整治群众身边不正之风和腐败问题，是习近平总书记反复强调的大事，是党中央作出的重大部署。2025年，全...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        台积电利润狂飙 市值已超三星两...
                        1月15日，英伟达、苹果等人工智能芯片的主要供应商台积电（2330.TW）公布第四季度财报，显示利润...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        助老一键呼叫 安全随叫随到
                        “徐奶奶，生活上遇到问题不用慌，直接按这个呼叫器，我们10分钟之内就能赶到。”近日，洛阳市涧西区长春...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        枢纽与支点丨从全局谋划一域 以...
                        中交二航局承建的渑淅高速淅川至豫鄂省界段大观苑互通立交。河南日报记者 聂冬晗 摄推动融入服务全国统一...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        关于撤销秦建斌政协第十三届河南...
                        关于撤销秦建斌政协第十三届河南省委员会委员资格的决定（2026年1月15日政协第十三届河南省委员会常...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        关于免去阚全程政协第十三届河南...
                        关于免去阚全程政协第十三届河南省委员会常务委员职务并撤销其委员资格的决定（2026年1月15日政协第...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        中国人民政治协商会议第十三届河...
                        中国人民政治协商会议第十三届河南省委员会专门委员会主任、副主任任免名单（2026年1月15日政协第十...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        中国人民政治协商会议第十三届河...
                        中国人民政治协商会议第十三届河南省委员会第四次会议秘书长名单（2026年1月15日政协第十三届河南省...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        “死了么”活多久 全靠“孤独的...
                        深圳商报记者 任建新 2026年开年，一款名为“死了么”的APP以“8元永久付费”模式空降苹果付费榜...
                    

                    
                

                                
                

                    
                    

                        

                    

                    
                    
                    

                        新华都连续两日涨停 近日旗下公...
                        1月15日，新华都(002264)收获涨停，这已是该股连续两日涨停，最新报收12.09元/股，总市值...