美文网首页编程
Mysql 字符集(学习笔记十)

Mysql 字符集(学习笔记十)

作者: SkTj | 来源:发表于2018-05-21 15:11 被阅读0次

    show charcater set;

    show variables like '%character%';

    1.查看mysql所支持的字符集

    指令:SHOW CHARACTER SET;

    很多很多,这里就不全部放上来了,这里的charset代表字符集,就是编码对应字符的集合,后面的collation代表了字符序,字符序就是每种不同的字符集在比较时采用的不同的方法。只要表上有的字符集,就可以被我们采用。

    2.当前关于mysql的各种字符集

    指令:SHOW VARIABLES LIKE ‘character%’;

    这张图是经过我更改my.ini配置后的各个项目的字符集,我都改成了utf8,因为utf8支持汉字,几乎成了目前Web开发界的标准字符集。下面我来解释下各个项代表的含义。

    character_set_client: 代表客户端字符集,客户端最简单的来说,就是之这个命令行,或者其它操作数据库的网页,应用等等,客户端字符集就代表了用户输入的字符,用什么字符集来编码。

    character_set_connection: 代表与服务器连接层的字符集,mysql是连接mysqld服务器的客户端,两者连接层,采用的字符集。

    character_set_database: 数据库采用的字符集。

    character_set_filesystem: 文件采用的肯定是二进制最合适,不用修改。

    character_set_result: 结果字符集,返回结果时采用的字符集。

    character_set_server: mysql服务器采用的字符集,也就是操作默认的字符集。

    character_set_system: 系统字符集,比如我们输入的命令'insert ...'这些语句字符串采用的字符集。

    有一点很重要:在创建时未设置字符集的情况下,当前创建的东西会根据上一级的字符集来确定字符集,比如创建数据库未指定字符集,则会采用server的字符集来作为库的字符集,创建数据表时为指定字符集,则会采用数据库的字符集来作为库的字符集。以此类推,从下到上为 记录<表<库<服务器。

    举个例子:

    创建一个为指定字符集的数据库

    3.更改my.ini配置文件来更改默认字符集

    [mysql]

    default-character-set=utf8

    [mysqld]

    character_set_client=utf8

    character_set_server=utf8

    collation_server=utf8_bin

    4.在创建时指定字符集

    除了采用默认的字符集外,还可以在创建时设置字符集,但是要清楚是为哪个设置字符集。

    如图,在创建数据表时指定字符集:

    指令:CREATE DATABASE db2 character set gbk;

    不管创建什么,后面加一句character set <字符集>;  这样就可以指定了。

    当然还可以更改一个数据表的字符集:alter table tbname convert to charset gbk;



    MySQL字符集详解

    一、字符集和校验规则

    字符集是一套符合和编码,校验规则(collation)是在字符集内用于比较字符的一套规则,即字符集的排序规则。MySQL可以使用对种字符集和检验规则来组织字符。

    MySQL服务器可以支持多种字符集,在同一台服务器,同一个数据库,甚至同一个表的不同字段都可以指定使用不同的字符集,相比oracle等其他数据库管理系统,在同一个数据库只能使用相同的字符集,MySQL明显存在更大的灵活性。

    每种字符集都可能有多种校对规则,并且都有一个默认的校对规则,并且每个校对规则只是针对某个字符集,和其他的字符集么有关系。

    在MySQL中,字符集的概念和编码方案被看做是同义词,一个字符集是一个转换表和一个编码方案的组合。

    Unicode(Universal Code)是一种在计算机上使用的字符编码。Unicode 是为了解决传统的字符编码方案的局限而产生的,它为每种语言中的每个字符设定了统一并且唯一的二进制编码,以满足跨语言、跨平台进行文本转换、处理的要求。Unicode存在不同的编码方案,包括Utf-8,Utf-16和Utf-32。Utf表示Unicode Transformation Format。

    二、查看mysql字符集方法

    1、查看mysql服务器支持的字符集

    mysql> show character set;

    mysql> select * from information_schema.character_sets;

    mysql> select character_set_name, default_collate_name, description, maxlen from

    information_schema.character_sets;

    2、查看字符集的校对规则

    mysql> show collation;

    mysql> show collation like 'utf8';

    mysql> select * from information_schema.collations where collation_name like 'utf8%';

    3、查看当前数据库的字符集

    mysql> show variables like 'character%';

    +--------------------------+----------------------------------+

    | Variable_name | Value |

    +--------------------------+----------------------------------+

    | character_set_client | utf8 |

    | character_set_connection | utf8 |

    | character_set_database | latin1 |

    | character_set_filesystem | utf8 |

    | character_set_results | utf8 |

    | character_set_server | utf8 |

    | character_set_system | utf8 |

    | character_sets_dir | /usr/local/mysql/share/charsets/ |

    +--------------------------+----------------------------------+

    8 rows in set (0.00 sec)

    名词解释:

    character_set_client:客户端请求数据的字符集

    character_set_connection:客户机/服务器连接的字符集

    character_set_database:默认数据库的字符集,无论默认数据库如何改变,都是这个字符集;如果没有默认数据库,那就使用 character_set_server指定的字符集,这个变量建议由系统自己管理,不要人为定义。

    character_set_filesystem:把os上文件名转化成此字符集,即把 character_set_client转换character_set_filesystem, 默认binary是不做任何转换的

    character_set_results:结果集,返回给客户端的字符集

    character_set_server:数据库服务器的默认字符集

    character_set_system:系统字符集,这个值总是utf8,不需要设置。这个字符集用于数据库对象(如表和列)的名字,也用于存储在目录表中的函数的名字。

    4、查看当前数据库的校对规则

    mysql> show variables like 'collation%';

    +----------------------+-------------------+

    | Variable_name | Value |

    +----------------------+-------------------+

    | collation_connection | utf8_general_ci |

    | collation_database | latin1_swedish_ci |

    | collation_server | utf8_general_ci |

    +----------------------+-------------------+

    3 rows in set (0.01 sec)

    名词解释:

    collation_connection 当前连接的字符集。

    collation_database    当前日期的默认校对。每次用USE语句来“跳转”到另一个数据库的时候,这个变量的值就会改变。如果没有当前数据库,这个变量的值就是collation_server变量的值。

    collation_server 服务器的默认校对。

    排序方式的命名规则为:字符集名字_语言_后缀,其中各个典型后缀的含义如下:

    1)_ci:不区分大小写的排序方式

    2)_cs:区分大小写的排序方式

    3)_bin:二进制排序方式,大小比较将根据字符编码,不涉及人类语言,因此_bin的排序方式不包含人类语言

    三、MySQL字符集的设置

    1、概述

    MySQL字符集设置分为两类:

    1)创建对象的默认值。

    2)控制server和client端交互通信的配置。

    1、创建对象的默认值

    字符集合校对规则有4个级别的默认设置:

    1)服务器级别;

    2)数据库级别;

    3)表级别、列级别;

    4)连接级别。

    更低级别的设置会集成高级别的设置。

    这里有一个通用的规则:先为服务器或者数据库选择一个合理的字符集,然后根据不同的实际情况,让某个列选择自己的字符集。

    2、控制server和client端交互通信的配置

    大部分MySQL客户端都不具备同时支持多种字符集的能力,每次都只能使用一种字符集。

    客户和服务器之间的字符集转换工作是由如下几个MySQL系统变量控制的。

    1)character_set_server:mysql server默认字符集。

    2)character_set_database:数据库默认字符集。

    3)character_set_client:MySQL server假定客户端发送的查询使用的字符集。

    4)character_set_connection:MySQL Server接收客户端发布的查询请求后,将其转换为character_set_connection变量指定的字符集。

    5)character_set_results:mysql server把结果集和错误信息转换为character_set_results指定的字符集,并发送给客户端。

    6)character_set_system:系统元数据(字段名等)字符集

    还有以collation_开头的同上面对应的变量,用来描述字符序。

    注意事项:

    • my.cnf中的default_character_set设置只影响mysql命令连接服务器时的连接字符集,不会对使用libmysqlclient库的应用程序产生任何作用!

    • 对字段进行的SQL函数操作通常都是以内部操作字符集进行的,不受连接字符集设置的影响。

    • SQL语句中的裸字符串会受到连接字符集或introducer设置的影响,对于比较之类的操作可能产生完全不同的结果,需要小心!

    3、默认情况下字符集选择规则

    (1)编译MySQL 时,指定了一个默认的字符集,这个字符集是 latin1;

    (2)安装MySQL 时,可以在配置文件 (my.cnf) 中指定一个默认的的字符集,如果没指定,这个值继承自编译时指定的;

    (3)启动mysqld 时,可以在命令行参数中指定一个默认的的字符集,如果没指定,这个值继承自配置文件中的配置,此时character_set_server被设定为这个默认的字符集;

    (4)当创建一个新的数据库时,除非明确指定,这个数据库的字符集被缺省设定为character_set_server;

    (5)当选定了一个数据库时,character_set_database被设定为这个数据库默认的字符集;

    (6)在这个数据库里创建一张表时,表默认的字符集被设定为character_set_database,也就是这个数据库默认的字符集;

    (7)当在表内设置一栏时,除非明确指定,否则此栏缺省的字符集就是表默认的字符集;

    2、分述

    2.1、为列分配字符集

    属于同一个表的不同列可以有不同的字符集,如果没有为一个列显示的定义字符集就使用默认字符集。创建一个表的时候,若显示的为列指定字符集,则字符集作为数据类型选项包含在其中,要放在数据类型后面及空指定和主键前面。

    例如:

    create table column_charset(

    c1 char(10) character set utf8 not null,

    c2 char(10) char set utf8,

    c3 varchar(10) charset utf8,

    c4 varchar(10)) engine=innodb;

    注意:character set可以简写为char setcharset

    使用show create table table_name;命令查看column_charset建表语句:

    mysql> show create table column_charset\G;

    *************************** 1. row ***************************

    Table: column_charset

    Create Table: CREATE TABLE `column_charset` (

    `c1` char(10) CHARACTER SET utf8 NOT NULL,

    `c2` char(10) CHARACTER SET utf8 DEFAULT NULL,

    `c3` varchar(10) CHARACTER SET utf8 DEFAULT NULL,

    `c4` varchar(10) DEFAULT NULL

    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    1 row in set (0.01 sec)

    ERROR:

    No query specified

    插入数据,感受一下效果:

    mysql> insert into column_charset(c1,c2,c3,c4) value("图灵","图灵","图灵","chavin");

    Query OK, 1 row affected (0.01 sec)

    mysql> select * from column_charset;

    +--------+--------+--------+--------+

    | c1 | c2 | c3 | c4 |

    +--------+--------+--------+--------+

    | 图灵 | 图灵 | 图灵 | chavin |

    +--------+--------+--------+--------+

    1 row in set (0.00 sec)

    2.2、为表分配字符集

    create table table_charset(

    c1 varchar(10),

    c2 varchar(10))engine=innodb default charset=utf8;

    注意:为表指定字符集可以使用以下几种方式:

    default charset=utf8;

    charset=utf8;

    default character set=utf8;

    character set=utf8;

    default char set=utf8;

    char set=utf8;

    检查建表语句:

    mysql> show create table table_charset\G;

    *************************** 1. row ***************************

    Table: table_charset

    Create Table: CREATE TABLE `table_charset` (

    `c1` varchar(10) DEFAULT NULL,

    `c2` varchar(10) DEFAULT NULL

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    1 row in set (0.00 sec)

    测试:

    mysql> insert into table_charset(c1,c2) values('图灵','图灵');

    Query OK, 1 row affected (0.01 sec)

    mysql> select * from table_charset;

    +--------+--------+

    | c1 | c2 |

    +--------+--------+

    | 图灵 | 图灵 |

    +--------+--------+

    1 row in set (0.00 sec)

    2.3、为数据库指定字符集

    创建的每个数据库都有一个默认字符集,如果没有指定,就用latin1。

    create database dbking charset=utf8;

    注意:创建数据库分配字符集可以采用以下几种子句:

    charset=utf8;

    default charset=utf8;

    charset utf8;

    default charset utf8;

    char set=utf8;

    default char set=utf8;

    char set utf8;

    default char set utf8;

    character set=utf8;

    default character set=utf8;

    character set utf8;

    default character set utf8;

    使用show create database db_name;命令查看数据库创建语句:

    mysql> show create database dbking;

    +----------+-----------------------------------------------------------------+

    | Database | Create Database |

    +----------+-----------------------------------------------------------------+

    | dbking | CREATE DATABASE `dbking` /*!40100 DEFAULT CHARACTER SET utf8 */ |

    +----------+-----------------------------------------------------------------+

    1 row in set (0.00 sec)

    2.4、为列分配校对规则

    每个列都应该有一个校对,如果没有显示指定,MySQL就使用属于该字符集的默认校对。如果指定了一个字符集和一个校对,字符集应该放在前面。

    create table column_collate(

    c1 varchar(10) charset utf8 collate utf8_romanian_ci not null,

    c2 varchar(10) charset utf8 collate utf8_spanish_ci)engine=innodb;

    查看表的校验规则信息:

    mysql> select table_name,column_name,collation_name

    from information_schema.columns

    where table_name='column_collate';

    +----------------+-------------+------------------+

    | table_name | column_name | collation_name |

    +----------------+-------------+------------------+

    | column_collate | c1 | utf8_romanian_ci |

    | column_collate | c2 | utf8_spanish_ci |

    +----------------+-------------+------------------+

    2 rows in set (0.04 sec)

    注意:字符集和校对在处理字符表达式的过程中扮演着重要角色。我们不能比较两个属于不同校对的不同字符值。例如:

    mysql> insert into column_collate(c1,c2) values('A','A');

    Query OK, 1 row affected (0.22 sec)

    mysql> select * from column_collate;

    +----+------+

    | c1 | c2 |

    +----+------+

    | A | A |

    +----+------+

    1 row in set (0.00 sec)

    mysql> select * from column_collate where c1=c2;

    ERROR 1267 (HY000): Illegal mix of collations (utf8_romanian_ci,IMPLICIT) and (utf8_spanish_ci,IMPLICIT) for operation '='

    2.5、为表指定校对规则

    create table table_collate(

    c1 varchar(10),

    c2 varchar(10))engine=innodb default charset utf8 collate utf8_romanian_ci;

    检查表的校对规则:

    mysql> select table_name,column_name,collation_name from information_schema.columns where table_name='table_collate';

    +---------------+-------------+------------------+

    | table_name | column_name | collation_name |

    +---------------+-------------+------------------+

    | table_collate | c1 | utf8_romanian_ci |

    | table_collate | c2 | utf8_romanian_ci |

    +---------------+-------------+------------------+

    2 rows in set (0.00 sec)

    2.6、为数据库指定校对规则

    create database dbking102 default charset utf8 collate utf8_romanian_ci;

    查看数据库定义语句:

    mysql> show create database dbking102\G;

    *************************** 1. row ***************************

    Database: dbking102

    Create Database: CREATE DATABASE `dbking102` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_romanian_ci */

    1 row in set (0.00 sec)

    2.7、字符直接量字符集

    如果没有显示指定,那么字符直接量的字符集就是数据库的默认字符集。如果要显示分配另一个字符集,需要把字符集的名字放在直接量前面,并且要在字符集前面加上下划线。

    mysql> select _utf8'语言 Language 言語 язык';

    +---------------------------------+

    | 语言 Language 言語 язык     |

    +---------------------------------+

    | 语言 Language 言語 язык     |

    +---------------------------------+  

    2.8、修改和设置MySQL服务器级别字符集

    MySQL服务器支持众多不同的字符集,这类字符集可在编译时和运行时指定。

        1) 编译时指定

    编译时可指定默认字符集和默认校对规则,要想同时更改默认字符集和校对规则,要同时使用--with-charset和--with-collation选项。校对规则必须是字符集的合法校对规则。

    ./configure -- with-charset=CHARSET --with-collation=COLLATION

    通过configure选项--with-extra-charsets=LIST,可以定义在服务器中再定义增加字符集。LIST 指下面任何一项:

    a.空格间隔的一系列字符集名

    b.complex -,以包括不能动态装载的所有字符集

    c.all –,以将所有字符集包括进二进制

    ./configure -- with-charset=CHARSET --with-collation=COLLATION --with-extra-charsets=all

        2) 在参数文件my.cnf中指定

    [mysqld]

    character_set_server=utf8

        --影响参数:character_set_server 和 character_set_database

        --注意:修改后要重启数据库才能生效。

    [client]

    default-character-set=utf8

        --影响参数:character_set_client,character_set_connection 和character_set_results。

    --注意:修改后无需重启数据库。

    3) 在启动参数前指定

    ./mysqld --character-set-server=utf8 &

    --影响参数:character_set_server 和 character_set_database

    4)在mysql客户端登陆时通过--default-character-set指定

    mysql -uroot -pmysql --default-character-set=utf8

    --影响参数:set character_set_client,set character_set_connection,set character_set_results。

        5)临时指定

    a)分别指定

    mysql> SET character_set_client = utf8;

    mysql> SET character_set_connection = utf8;

    mysql> SET character_set_database = utf8;

    mysql> SET character_set_results = utf8;

    mysql> SET character_set_server = utf8;

    b)mysql客户端使用:set names utf8;

    等同于

    set character_set_client=utf8;

    set character_set_connection=utf8;

    set character_set_results=utf8;

    c)set character set utf8;

    等同于

    set character_set_client=utf8;

    set character_set_results=utf8;

    set collation_connection=@@collation_database;

    3、总结

    下面介绍下几个MYSQL命令:

    1)show character set;或show char set;

    查看数据库支持的所有字符集

    2)status;或\s;

    查看当前状态 里面包括当然的字符集设置

    3)show variables like 'char%';

    查看系统字符集设置,包括所有的字符集设置

    4)show table status from sqlstudy like '%countries%';

    查看sqlstudy数据库中表的字符集设置

    5)show full columns from countries;

    查看表列的字符集设置,关键是在同一个表中,每列可以设置成不同的字符集

    知道怎么查看字符集了,下面我来说下如何设置这些字符集

    1.修改服务器级

    a. 临时更改:

    mysql>SET GLOBAL character_set_server=utf8;

    b. 永久更改:

    修改my.cnf文件

    [mysqld]

    character-set-server=utf8

    2.修改数据库级

    a. 临时更改:

    mysql>SET GLOBAL character_set_database=utf8;

    b. 永久更改:

    改了服务器级就可以了

    3.修改表级

    mysql>ALTER TABLE table_name DEFAULT CHARSET utf8;

    更改了后永久生效

    4.修改列级

    修改示例:

    mysql>alter table `products` change `products_model` `products_model` varchar( 20 )

    character set  utf8 collate utf8_general_ci null default null;

    更改了后永久生效

    5.更改连接字符集

    a. 临时更改:

    mysql> set names utf8;

    b. 永久更改:

    修改my.cnf文件

    在[client]中增加:

    default-character-set=utf8

    执行SQL语句时信息的路径是这样的

    信息输入路径:client→connection→server;

    信息输出路径:server→connection→results.

    四、MySQL数据库中字符集转换流程

    1、MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection;

    2、进行内部操作前将请求数据从character_set_connection转换为内部操作字符集,其确定方法如下:

    使用每个数据字段的CHARACTER SET设定值;

    若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展,非SQL标准);

    若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值;

    若上述值不存在,则使用character_set_server设定值。

    3、将操作结果从内部操作字符集转换为character_set_results。

    下图源自于《高性能MySQL》中关于字符集转换的图解:

    五、MySQL数据库乱码原因解析及案例

    1、产生乱码的根本原因

    1)客户机没有正确地设置client字符集,导致原先的SQL语句被转换成connection所指字符集,而这种转换,是会丢失信息的,如果client是utf8格式,那么如果转换成gb2312格式,这其中必定会丢失信息,反之则不会丢失。一定要保证connection的字符集大于client字符集才能保证转换不丢失信息。

    2)数据库字体没有设置正确,如果数据库字体设置不正确,那么connection字符集转换成database字符集照样丢失编码,原因跟上面一样。

    2、乱码或数据丢失

    character_set_client:我们要告诉服务器,我给你发送的数据是什么编码?

    character_set_connection:告诉字符集转换器,转换成什么编码?

    character_set_results:查询的结果用什么编码?

    如果以上三者都为字符集N,可简写为set names 'N';

    2.1 乱码问题

    模拟情景1:

    向默认字符集为utf8的数据表插入utf8编码的数据前连接字符集设置为latin1,查询时设置连接字符集为utf8。

    插入时根据MySQL服务器的默认设置,character_set_client、character_set_connection和character_set_results均为latin1;

    插入操作的数据将经过latin1=>latin1=>utf8的字符集转换过程,这一过程中每个插入的汉字都会从原始的3个字节变成6个字节保存;

    查询时的结果将经过utf8=>utf8的字符集转换过程,将保存的6个字节原封不动返回,产生乱码……

    例如:

    mysql> set names latin1;

    mysql> create table temp(name varchar(10)) charset utf8;

    mysql> insert into temp values('中国');

    mysql> select * from temp;

    +--------+

    | name |

    +--------+

    | 中国 |

    +--------+

    mysql> set names utf8;

    mysql> select * from temp;

    +---------------+

    | name |

    +---------------+

    | 中国 |

    +---------------+

    注意:存储字符集编码比插入时字符集大时,如果原封不动返回数据会出现乱码,不过可通过修改查询字符集,避免乱码,即不会丢失数据。

    2.2 数据丢失问题

    模拟情景1:

    向默认字符集为latin1的数据表插入utf8编码的数据前设置了连接字符集为utf8

    插入时根据连接字符集设置,character_set_client、character_set_connection和character_set_results均为utf8;

    插入数据将经过utf8=>utf8=>latin1的字符集转换,若原始数据中含有\u0000~\u00ff范围以外的Unicode字 符,会因为无法在latin1字符集中表示而被转换为“?”(0×3F)符号,以后查询时不管连接字符集设置如何都无法恢复其内容了。

    例如:

    mysql> set names utf8;

    mysql> create table temp(name varchar(10)) charset latin1;

    mysql> insert into temp values('中国');

    mysql> select * from temp;

    +------+

    | name |

    +------+

    | ?? |

    +------+

    mysql> set names latin1;

    mysql> select * from temp;

    +------+

    | name |

    +------+

    | ?? |

    +------+

    数据不完整了,且无法恢复。

    3、 乱码终极解决方案

    1)首先要明确你的客户端时候何种编码格式,这是最重要的(IE6一般用utf8,命令行一般是gbk,一般程序是gb2312)

    2)确保你的数据库使用utf8格式,很简单,所有编码通吃。

    3)一定要保证connection字符集大于等于client字符集,不然就会信息丢失,比如: latin1 < gb2312 < gbk < utf8,若设置set character_set_client = gb2312,那么至少connection的字符集要大于等于gb2312,否则就会丢失信息

    4)以上三步做正确的话,那么所有中文都被正确地转换成utf8格式存储进了数据库,为了适应不同的浏览器,不同的客户端,你可以修改character_set_results来以不同的编码显示中文字体,由于utf8是大方向,因此web应用是我还是倾向于使用utf8格式显示中文的。

    相关文章

      网友评论

        本文标题:Mysql 字符集(学习笔记十)

        本文链接:https://www.haomeiwen.com/subject/oygfjftx.html