再见乱码5分钟读懂MySQL字符集设置

彭洋医生 https://myyk.familydoctor.com.cn/doctor/347381/

一、内容概述

在MySQL的使用过程中,了解字符集、字符序的概念,以及不同设置对数据存储、比较的影响非常重要。不少同学在日常工作中遇到的“乱码”问题,很有可能就是因为对字符集与字符序的理解不到位、设置错误造成的。

.

本文由浅入深,分别介绍了如下内容:

字符集、字符序的基本概念及联系MySQL支持的字符集、字符序设置级,各设置级别之间的联系server、database、table、column级字符集、字符序的查看及设置应该何时设置字符集、字符序

二、字符集、字符序的概念与联系

在数据的存储上,MySQL提供了不同的字符集支持。而在数据的对比操作上,则提供了不同的字符序支持。

MySQL提供了不同级别的设置,包括server级、database级、table级、column级,可以提供非常精准的设置。

什么是字符集、字符序?简单的来说:

字符集(characterset):定义了字符以及字符的编码。字符序(collation):定义了字符的比较规则。

举个例子:

有四个字符:A、B、a、b,这四个字符的编码分别是A=0,B=1,a=2,b=3。这里的字符+编码就构成了字符集(characterset)。

如果我们想比较两个字符的大小呢?比如A、B,或者a、b,最直观的比较方式是采用它们的编码,比如因为01,所以AB。

另外,对于A、a,虽然它们编码不同,但我们觉得大小写字符应该是相等的,也就是说A==a。

这上面定义了两条比较规则,这些比较规则的集合就是collation。

同样是大写字符、小写字符,则比较他们的编码大小;如果两个字符为大小写关系,则它们相等。

三、MySQL支持的字符集、字符序

MySQL支持多种字符集与字符序。

一个字符集对应至少一种字符序(一般是1对多)。两个不同的字符集不能有相同的字符序。每个字符集都有默认的字符序。

上面说的比较抽象,我们看下后面几个小节就知道怎么回事了。

1、查看支持的字符集

可以通过以下方式查看MYSQL支持的字符集。

方式一:

.

方式二:

.

当使用SHOWCHARACTERSET查看时,也可以加上WHERE或LIKE限定条件。

例子一:使用WHERE限定条件。

.

例子二:使用LIKE限定条件。

.

2、查看支持的字符序

类似的,可以通过如下方式查看MYSQL支持的字符序。

方式一:通过SHOWCOLLATION进行查看。可以看到,utf8字符集有超过10种字符序。通过Default的值是否为Yes,判断是否默认的字符序。

.

方式二:查询information_schema.COLLATIONS。

.

3、字符序的命名规范

字符序的命名,以其对应的字符集作为前缀,如下所示。比如字符序utf8_general_ci,标明它是字符集utf8的字符序。

MariaDB[information_schema]SELECTCHARACTER_SET_NAME,COLLATION_NAMEFROMCOLLATIONSWHERECHARACTER_SET_NAME=utf8limit2;

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

CHARACTER_SET_NAME

COLLATION_NAME

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

utf8

utf8_general_ci

utf8

utf8_bin

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

2rowsinset(0.00sec)

四、server的字符集、字符序

用途:当你创建数据库,且没有指定字符集、字符序时,server字符集、server字符序就会作为该数据库的默认字符集、排序规则。

如何指定:MySQL服务启动时,可通过命令行参数指定。也可以通过配置文件的变量指定。

server默认字符集、字符序:在MySQL编译的时候,通过编译参数指定。character_set_server、collation_server分别对应server字符集、server字符序。

1、查看server字符集、字符序

分别对应character_set_server、collation_server两个系统变量。

mysqlSHOWVARIABLESLIKEcharacter_set_server;mysqlSHOWVARIABLES

LIKEcollation_server;

2、启动服务时指定

可以在MySQL服务启动时,指定server字符集、字符序。如不指定,默认的字符序分别为latin1、latin1_swedish_ci

mysqld--character-set-server=latin1--collation-server=latin1_swedish_ci

单独指定server字符集,此时,server字符序为latin1的默认字符序latin1_swedish_ci。

mysqld--character-set-server=latin1

3、配置文件指定

除了在命令行参数里指定,也可以在配置文件里指定,如下所示。

[client]default-character-set=utf8[mysql]default-character-set=utf8[mysqld]collation-server=utf8_unicode_ciinit-connect=SETNAMESutf8character-set-server=utf8

4、运行时修改

例子:运行时修改(重启后会失效,如果想要重启后保持不变,需要写进配置文件里)

mysqlSETcharacter_set_server=utf8;

5、编译时指定默认字符集、字符序character_set_server、collation_server的默认值,可以在MySQL编译时,通过编译选项指定:

cmake.-DDEFAULT_CHARSET=latin1-DDEFAULT_COLLATION=latin1_german1_ci

五、database的字符集、字符序

用途:指定数据库级别的字符集、字符序。同一个MySQL服务下的数据库,可以分别指定不同的字符集/字符序。

1、设置数据的字符集/字符序

可以在创建、修改数据库的时候,通过CHARACTERSET、COLLATE指定数据库的字符集、排序规则。

创建数据库:

CREATEDATABASEdb_name[[DEFAULT]CHARACTERSETcharset_name][[DEFAULT]COLLATEcollation_name]

修改数据库:

ALTERDATABASEdb_name[[DEFAULT]CHARACTERSETcharset_name][[DEFAULT]COLLATEcollation_name]

例子:创建数据库test_schema,字符集设置为utf8,此时默认的排序规则为utf8_general_ci。

CREATEDATABASE`test_schema`DEFAULTCHARACTERSETutf8;

2、查看数据库的字符集/字符序

有3种方式可以查看数据库的字符集/字符序。

例子一:查看test_schema的字符集、排序规则。(需要切换默认数据库)

mysqlusetest_schema;DatabasechangedmysqlSELECT

character_set_database,

collation_database;+--------------------------+----------------------+

character_set_database

collation_database

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

utf8

utf8_general_ci

+--------------------------+----------------------+1rowinset(0.00sec)

例子二:也可以通过下面命令查看test_schema的字符集、数据库(不需要切换默认数据库)

mysqlSELECTSCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAMEFROMinformation_schema.SCHEMATAWHEREschema_name=test_schema;+-------------+----------------------------+------------------------+

SCHEMA_NAME

DEFAULT_CHARACTER_SET_NAME

DEFAULT_COLLATION_NAME

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

test_schema

utf8

utf8_general_ci

+-------------+----------------------------+------------------------+1rowinset(0.00sec)

例子三:也可以通过查看创建数据库的语句,来查看字符集。。

mysqlSHOWCREATEDATABASEtest_schema;+-------------+----------------------------------------------------------------------+

Database

CreateDatabase

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

test_schema

CREATEDATABASE`test_schema`/*!DEFAULTCHARACTERSETutf8*/

+-------------+----------------------------------------------------------------------+1rowinset(0.00sec)

3、database字符集、字符序是怎么确定的

创建数据库时,指定了CHARACTERSET或COLLATE,则以对应的字符集、排序规则为准。创建数据库时,如果没有指定字符集、排序规则,则以character_set_server、collation_server为准。

六、table的字符集、字符序

创建表、修改表的语法如下,可通过CHARACTERSET、COLLATE设置字符集、字符序。

CREATETABLEtbl_name(column_list)[[DEFAULT]CHARACTERSETcharset_name][COLLATEcollation_name]]ALTERTABLEtbl_name[[DEFAULT]CHARACTERSETcharset_name][COLLATEcollation_name]

1、创建table并指定字符集/字符序

例子如下,指定字符集为utf8,字符序则采用默认的。

CREATETABLE`test_schema`.`test_table`(`id`INTNOTNULLCOMMENT,PRIMARYKEY(`id`)COMMENT)DEFAULTCHARACTERSET=utf8;

2、查看table的字符集/字符序

同样,有3种方式可以查看table的字符集/字符序。

方式一:通过SHOWTABLESTATUS查看table状态,注意Collation为utf8_general_ci,对应的字符集为utf8。

MariaDB[blog]SHOWTABLESTATUSFROMtest_schemaG;***************************1.row***************************Name:test_tableEngine:InnoDBVersion:10Row_format:CompactRows:0Avg_row_length:0Data_length:Max_data_length:0Index_length:0Data_free:Auto_increment:NULLCreate_time:-01-:10:42Update_time:NULLCheck_time:NULLCollation:utf8_general_ciChecksum:NULLCreate_options:Comment:1rowinset(0.00sec)

方式二:查看information_schema.TABLES的信息。

mysqlUSEtest_schema;mysqlSELECTTABLE_COLLATIONFROMinformation_schema.TABLESWHERETABLE_SCHEMA=test_schemaANDTABLE_NAME=test_table;+-----------------+

TABLE_COLLATION

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

utf8_general_ci

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

方式三:通过SHOWCREATETABLE确认。

mysqlSHOWCREATETABLEtest_table;+------------+----------------------------------------------------------------------------------------------------------------+

Table

CreateTable

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

test_table

CREATETABLE`test_table`(`id`int(11)NOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8

+------------+----------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)

3、table字符集、字符序如何确定

假设CHARACTERSET、COLLATE的值分别是charset_name、collation_name。如果创建table时:

明确了charset_name、collation_name,则采用charset_name、collation_name。只明确了charset_name,但collation_name未明确,则字符集采用charset_name,字符序采用charset_name对应的默认字符序。只明确了collation_name,但charset_name未明确,则字符序采用collation_name,字符集采用collation_name关联的字符集。charset_namecollation_name均未明确,则采用数据库的字符集、字符序设置。

七、column的字符集、排序

类型为CHAR、VARCHAR、TEXT的列,可以指定字符集/字符序,语法如下:

col_name{CHAR

VARCHAR

TEXT}(col_length)[CHARACTERSETcharset_name][COLLATEcollation_name]

1、新增column并指定字符集/排序规则

例子如下:(创建table类似)

mysqlALTERTABLEtest_tableADDCOLUMNchar_columnVARCHAR(25)CHARACTERSETutf8;

2、查看column的字符集/字符序

例子如下:

mysqlSELECTCHARACTER_SET_NAME,COLLATION_NAMEFROMinformation_schema.COLUMNSWHERETABLE_SCHEMA=test_schemaANDTABLE_NAME=test_tableANDCOLUMN_NAME=char_column;+--------------------+-----------------+

CHARACTER_SET_NAME

COLLATION_NAME

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

utf8

utf8_general_ci

+--------------------+-----------------+1rowinset(0.00sec)

3、column字符集/排序规则确定

假设CHARACTERSET、COLLATE的值分别是charset_name、collation_name:

如果charset_name、collation_name均明确,则字符集、字符序以charset_name、collation_name为准。只明确了charset_name,collation_name未明确,则字符集为charset_name,字符序为charset_name的默认字符序。只明确了collation_name,charset_name未明确,则字符序为collation_name,字符集为collation_name关联的字符集。charset_name、collation_name均未明确,则以table的字符集、字符序为准。

八、选择:何时设置字符集、字符序

一般来说,可以在三个地方进行配置:

创建数据库的时候进行配置。mysqlserver启动的时候进行配置。从源码编译mysql的时候,通过编译参数进行配置

1、方式一:创建数据库的时候进行配置

这种方式比较灵活,也比较保险,它不依赖于默认的字符集/字符序。当你创建数据库的时候指定字符集/字符序,后续创建table、column的时候,如果不特殊指定,会继承对应数据库的字符集/字符序。

CREATEDATABASEmydbDEFAULTCHARACTERSETutf8DEFAULTCOLLATEutf8_general_ci;

2、方式二:mysqlserver启动的时候进行配置

可以添加以下配置,这样mysqlserver启动的时候,会对character-set-server、collation-server进行配置。

当你通过mysqlclient创建database/table/column,且没有显示声明字符集/字符序,那么就会用character-set-server/collation-server作为默认的字符集/字符序。

另外,client、server连接时的字符集/字符序,还是需要通过SETNAMES进行设置。

[mysqld]character-set-server=utf8collation-server=utf8_general_ci

3、方式三:从源码编译mysql的时候,通过编译参数进行设置

编译的时候如果指定了-DDEFAULT_CHARSET和-DDEFAULT_COLLATION,那么:

创建database、table时,会将其作为默认的字符集/字符序。client连接server时,会将其作为默认的字符集/字符序。(不用单独SETNAMES)

shellcmake.-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci

九、写在后面

本文较为详细地介绍了MySQL中字符集、字符序相关的内容,这部分内容主要针对的是数据的存储与比较。其实还有很重要的一部分内容还没涉及:针对连接的字符集、字符序设置。

由于连接的字符集、字符序设置不当导致的乱码问题也非常多,这部分内容展开来讲内容也不少,放在文章进行讲解。

篇幅所限,有些内容没有细讲,感兴趣的同学欢迎交流,或者查看官方文档。如有错漏,敬请指出。




转载请注明:http://www.aierlanlan.com/rzfs/3381.html