1. 字符集#### 1.1 一些重要的字符集##### 1.1.1 ASCII共收录128个字符包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符所以可以使用1个字节来进行编码##### 1.1.2ISO-8859-1共收录 256 个字符是在 ASCII 字符集的基础上又扩充了 128个 西欧常用字符(包括德法两国的字母)也可以使用1个字节来进行编码。这个字符集也有一个别名latin1。##### 1.1.3GB2312收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个其他文字符号682个。同时这种字符集又兼容 ASCII 字符集所以有以下规则* 如果该字符在 ASCII 字符集中则采用 1 字节编码。* 否则采用 2 字节编码。##### 1.1.4GBKGBK 字符集只是在收录字符范围上对GB2312 字符集作了扩充编码方式上兼容GB2312 。##### 1.1.5UTF8收录地球上能想到的所有字符而且还在不断扩充。这种字符集兼容 ASCII 字符集采用变长编码方式编码一个字符需要使用14个字节 UTF8 只是 Unicode 字符集的一种编码方案Unicode字符集可以采用UTF8、UTF16、UTF32 这几种编码方案UTF8 使用14个字节编码一个字符UTF16 使用 2 个或 4 个字节编码一个字符UTF32使用 4 个字节编码一个字符。#### 1.2 MySQL 中的 utf8 和 utf8mb4* utf8utf8mb3阉割过的 utf8 字符集使用13个字节表示字符。* utf8mb4正宗的 utf8 字符集使用14个字节表示字符。#### 1.3 查看字符集##### 1.3.1 语法 SHOW CHARACTER SET | CHARSET[LIKE 匹配的模式]CharsetDescriptionDefault collationMaxlenbig5Big5 Traditional Chinesebig5_chinese_ci2dec8DEC West Europeandec8_swedish_ci1cp850DOS West Europeancp850_general_ci1hp8HP West Europeanhp8_english_ci1koi8rKOI8-R Relcom Russiankoi8r_general_ci1latin1cp1252 West Europeanlatin1_swedish_ci1latin2ISO 8859-2 Central Europeanlatin2_general_ci1swe77bit Swedishswe7_swedish_ci1asciiUS ASCIIascii_general_ci1ujisEUC-JP Japaneseujis_japanese_ci3sjisShift-JIS Japanesesjis_japanese_ci2hebrewISO 8859-8 Hebrewhebrew_general_ci1tis620TIS620 Thaitis620_thai_ci1euckrEUC-KR Koreaneuckr_korean_ci2koi8uKOI8-U Ukrainiankoi8u_general_ci1gb2312GB2312 Simplified Chinesegb2312_chinese_ci2greekISO 8859-7 Greekgreek_general_ci1cp1250Windows Central Europeancp1250_general_ci1gbkGBK Simplified Chinesegbk_chinese_ci2latin5ISO 8859-9 Turkishlatin5_turkish_ci1armscii8ARMSCII-8 Armenianarmscii8_general_ci1utf8UTF-8 Unicodeutf8_general_ci3ucs2UCS-2 Unicodeucs2_general_ci2cp866DOS Russiancp866_general_ci1keybcs2DOS Kamenicky Czech-Slovakkeybcs2_general_ci1macceMac Central Europeanmacce_general_ci1macromanMac West Europeanmacroman_general_ci1cp852DOS Central Europeancp852_general_ci1latin7ISO 8859-13 Balticlatin7_general_ci1utf8mb4UTF-8 Unicodeutf8mb4_general_ci4cp1251Windows Cyrilliccp1251_general_ci1utf16UTF-16 Unicodeutf16_general_ci4utf16leUTF-16LE Unicodeutf16le_general_ci4cp1256Windows Arabiccp1256_general_ci1cp1257Windows Balticcp1257_general_ci1utf32UTF-32 Unicodeutf32_general_ci4binaryBinary pseudo charsetbinary1geostd8GEOSTD8 Georgiangeostd8_general_ci1cp932SJIS for Windows Japanesecp932_japanese_ci2eucjpmsUJIS for Windows Japaneseeucjpms_japanese_ci3gb18030China National Standard GB18030gb18030_chinese_ci4我使用的 MySQL 这个版本一共支持 41 种字符集其中的 Default collation 列表示这种字符集中一种默认的比较规则。大家注意返回结果中的最后一列 Maxlen它代表该种字符集表示一个字符最多需要几个字节。以下几个字符集的 Maxlen 需要重点关注* ascii1* latin11* gb23122* gbk2* utf83* utf8mb44### 2. 比较规则一种字符集可能对应着若干种比较规则不同的比较规则对于相同字符产生的结果可能不同比如说在字符集为 latin1比较规则为 latin1_general_ci 的情况下A 和 a 是相等的如果将比较规则改为latin1_general_cs则不相等。#### 2.1 举例演示##### 2.1.1 创建一个比较规则为 latin1_general_ci 的表插入一些数据然后查询出来通过现象可以表明对于比较规则为 latin1_general_ci 的表小写和小写是不区分的##### 2.1.2 将比较规则改为 latin1_general_cs查看现象通过现象可以表明对于比较规则为 latin1_general_cs的表小写和小写是区分的#### 2.2查看比较规则##### 2.2.1 语法 SHOW COLLATION [LIKE 匹配的模式]##### 2.2.2 查看比较规则查看 utf8 相关比较规则 SHOW COLLATION LIKE ‘utf8_%’;CollationCharsetIdDefaultCompiledSortlenutf8_general_ciutf833YesYes1utf8_binutf883Yes1utf8_unicode_ciutf8192Yes8utf8_icelandic_ciutf8193Yes8utf8_latvian_ciutf8194Yes8utf8_romanian_ciutf8195Yes8utf8_slovenian_ciutf8196Yes8utf8_polish_ciutf8197Yes8utf8_estonian_ciutf8198Yes8utf8_spanish_ciutf8199Yes8utf8_swedish_ciutf8200Yes8utf8_turkish_ciutf8201Yes8utf8_czech_ciutf8202Yes8utf8_danish_ciutf8203Yes8utf8_lithuanian_ciutf8204Yes8utf8_slovak_ciutf8205Yes8utf8_spanish2_ciutf8206Yes8utf8_roman_ciutf8207Yes8utf8_persian_ciutf8208Yes8utf8_esperanto_ciutf8209Yes8utf8_hungarian_ciutf8210Yes8utf8_sinhala_ciutf8211Yes8utf8_german2_ciutf8212Yes8utf8_croatian_ciutf8213Yes8utf8_unicode_520_ciutf8214Yes8utf8_vietnamese_ciutf8215Yes8utf8_general_mysql500_ciutf8223Yes1比较规则有以下规则:* 比较规则名称以与其关联的字符集的名称开头。如上图的查询结果的比较规则名称都是以utf8开头的。* 后边紧跟着该比较规则主要作用于哪种语言比如utf8_polish_ci 表示以波兰语的规则比较utf8_spanish_ci是以西班牙语的规则比较utf8_general_ci 是一种通用的比较规则。* 名称后缀意味着该比较规则是否区分语言中的重音、大小写啥的具体可以用的值如下 后缀 英文释义 描述_aiaccent insensitive不区分重音_asaccent sensitive区分重音_cicase insensitive不区分大小写_cscase sensitive区分大小写_binbinary以二进制方式比较 ### 3. 字符集和比较规则的级别* 服务器级别* 数据库级别* 表级别* 列级别#### 3.1 服务器级别* character_set_server服务器级别字符集* collation_server服务器级别比较规则##### 3.1.1 查看服务器级别的字符集和比较规则 show variables like ‘character_set_server’; show variables like ‘collation_server’;##### 3.1.2 修改服务器的默认值在类 UNIX 操作系统中MySQL 会按照下列路径来寻找配置文件* /etc/my.cnf* /etc/mysql/my.cnf* SYSCONFDIR/my.cnf* $MYSQL_HOME/my.cnf特定于服务器的选项仅限服务器* defaults-extra-file命令行指定的额外配置文件路径* ~/.my.cnf用户特定选项* ~/.mylogin.cnf用户特定的登录路径选项仅限客户端我们以修改 /etc/mysql/my.cnf 为例 [server] character_set_serverlatin1 collation_serverlatin1_general_cs修改完重启服务再次执行查询语句PS : 有可能修改完不生效可以执行chmod 644 my.cnf修改权限#### 3.2数据库级别##### 3.2.1 语法我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则具体语法如下 CREATE DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称]; ALTER DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称];##### 3.2.2查看数据库级别的字符集和比较规则 SHOW VARIABLES LIKE ‘character_set_database’; SHOW VARIABLES LIKE ‘collation_database’;可以看到这个 demo_db 数据库的字符集和比较规则就是我们在创建语句中指定的。需要注意的一点是character_set_database 和 collation_database 这两个系统变量是只读的我们不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则。如果未指定数据库级别的字符集和比较规则则使用服务器级别的字符集和比较规则#### 3.3 表级别##### 3.3.1 语法我们也可以在创建和修改表的时候指定表的字符集和比较规则语法如下 CREATE TABLE 表名 (列的信息) [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称]] ALTER TABLE 表名 [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称]##### 3.3.2查看表级别的字符集和比较规则 SHOW TABLE STATUS LIKE ‘table_name’;如果创建表的语句中没有指明字符集和比较规则将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。#### 3.4 列级别##### 3.4.1 语法 CREATE TABLE 表名( 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称], 其他列… ); ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];3.4.2 修改列的字符集和比较规则可以看出修改完表的字符集是gb2312列的字符集是utf8如果在创建和修改的语句中没有指明字符集和比较规则将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。PS :在转换列的字符集时需要注意如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。比方说原先列使用的字符集是utf8列中存储了一些汉字现在把列的字符集转换为ascii的话就会出错因为ascii字符集并不能表示汉字字符。### 4 仅修改字符集或仅修改比较规则由于字符集和比较规则是互相有联系的如果我们只修改了字符集比较规则也会跟着变化如果只修改了比较规则字符集也会跟着变化具体规则如下* 只修改字符集则比较规则将变为修改后的字符集默认的比较规则。* 只修改比较规则则字符集将变为修改后的比较规则对应的字符集。### 5.MySQL中字符集的转换#### 5.1 相关变量系统变量描述character_set_client服务器解码请求时使用的字符集character_set_connection服务器处理请求时会把请求字符串从character_set_client转为character_set_connectioncharacter_set_results服务器向客户端返回数据时使用的字符集#### 5.2 转换过程#### 5.3修改相关变量的值 SET NAMES 字符集名;等价于 SET character_set_client 字符集名; SET character_set_connection 字符集名; SET character_set_results 字符集名;
1. 字符集#### 1.1 一些重要的字符集##### 1.1.1 ASCII共收录128个字符包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符所以可以使用1个字节来进行编码##### 1.1.2ISO-8859-1共收录 256 个字符是在 ASCII 字符集的基础上又扩充了 128个 西欧常用字符(包括德法两国的字母)也可以使用1个字节来进行编码。这个字符集也有一个别名latin1。##### 1.1.3GB2312收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个其他文字符号682个。同时这种字符集又兼容 ASCII 字符集所以有以下规则* 如果该字符在 ASCII 字符集中则采用 1 字节编码。* 否则采用 2 字节编码。##### 1.1.4GBKGBK 字符集只是在收录字符范围上对GB2312 字符集作了扩充编码方式上兼容GB2312 。##### 1.1.5UTF8收录地球上能想到的所有字符而且还在不断扩充。这种字符集兼容 ASCII 字符集采用变长编码方式编码一个字符需要使用14个字节 UTF8 只是 Unicode 字符集的一种编码方案Unicode字符集可以采用UTF8、UTF16、UTF32 这几种编码方案UTF8 使用14个字节编码一个字符UTF16 使用 2 个或 4 个字节编码一个字符UTF32使用 4 个字节编码一个字符。#### 1.2 MySQL 中的 utf8 和 utf8mb4* utf8utf8mb3阉割过的 utf8 字符集使用13个字节表示字符。* utf8mb4正宗的 utf8 字符集使用14个字节表示字符。#### 1.3 查看字符集##### 1.3.1 语法 SHOW CHARACTER SET | CHARSET[LIKE 匹配的模式]CharsetDescriptionDefault collationMaxlenbig5Big5 Traditional Chinesebig5_chinese_ci2dec8DEC West Europeandec8_swedish_ci1cp850DOS West Europeancp850_general_ci1hp8HP West Europeanhp8_english_ci1koi8rKOI8-R Relcom Russiankoi8r_general_ci1latin1cp1252 West Europeanlatin1_swedish_ci1latin2ISO 8859-2 Central Europeanlatin2_general_ci1swe77bit Swedishswe7_swedish_ci1asciiUS ASCIIascii_general_ci1ujisEUC-JP Japaneseujis_japanese_ci3sjisShift-JIS Japanesesjis_japanese_ci2hebrewISO 8859-8 Hebrewhebrew_general_ci1tis620TIS620 Thaitis620_thai_ci1euckrEUC-KR Koreaneuckr_korean_ci2koi8uKOI8-U Ukrainiankoi8u_general_ci1gb2312GB2312 Simplified Chinesegb2312_chinese_ci2greekISO 8859-7 Greekgreek_general_ci1cp1250Windows Central Europeancp1250_general_ci1gbkGBK Simplified Chinesegbk_chinese_ci2latin5ISO 8859-9 Turkishlatin5_turkish_ci1armscii8ARMSCII-8 Armenianarmscii8_general_ci1utf8UTF-8 Unicodeutf8_general_ci3ucs2UCS-2 Unicodeucs2_general_ci2cp866DOS Russiancp866_general_ci1keybcs2DOS Kamenicky Czech-Slovakkeybcs2_general_ci1macceMac Central Europeanmacce_general_ci1macromanMac West Europeanmacroman_general_ci1cp852DOS Central Europeancp852_general_ci1latin7ISO 8859-13 Balticlatin7_general_ci1utf8mb4UTF-8 Unicodeutf8mb4_general_ci4cp1251Windows Cyrilliccp1251_general_ci1utf16UTF-16 Unicodeutf16_general_ci4utf16leUTF-16LE Unicodeutf16le_general_ci4cp1256Windows Arabiccp1256_general_ci1cp1257Windows Balticcp1257_general_ci1utf32UTF-32 Unicodeutf32_general_ci4binaryBinary pseudo charsetbinary1geostd8GEOSTD8 Georgiangeostd8_general_ci1cp932SJIS for Windows Japanesecp932_japanese_ci2eucjpmsUJIS for Windows Japaneseeucjpms_japanese_ci3gb18030China National Standard GB18030gb18030_chinese_ci4我使用的 MySQL 这个版本一共支持 41 种字符集其中的 Default collation 列表示这种字符集中一种默认的比较规则。大家注意返回结果中的最后一列 Maxlen它代表该种字符集表示一个字符最多需要几个字节。以下几个字符集的 Maxlen 需要重点关注* ascii1* latin11* gb23122* gbk2* utf83* utf8mb44### 2. 比较规则一种字符集可能对应着若干种比较规则不同的比较规则对于相同字符产生的结果可能不同比如说在字符集为 latin1比较规则为 latin1_general_ci 的情况下A 和 a 是相等的如果将比较规则改为latin1_general_cs则不相等。#### 2.1 举例演示##### 2.1.1 创建一个比较规则为 latin1_general_ci 的表插入一些数据然后查询出来通过现象可以表明对于比较规则为 latin1_general_ci 的表小写和小写是不区分的##### 2.1.2 将比较规则改为 latin1_general_cs查看现象通过现象可以表明对于比较规则为 latin1_general_cs的表小写和小写是区分的#### 2.2查看比较规则##### 2.2.1 语法 SHOW COLLATION [LIKE 匹配的模式]##### 2.2.2 查看比较规则查看 utf8 相关比较规则 SHOW COLLATION LIKE ‘utf8_%’;CollationCharsetIdDefaultCompiledSortlenutf8_general_ciutf833YesYes1utf8_binutf883Yes1utf8_unicode_ciutf8192Yes8utf8_icelandic_ciutf8193Yes8utf8_latvian_ciutf8194Yes8utf8_romanian_ciutf8195Yes8utf8_slovenian_ciutf8196Yes8utf8_polish_ciutf8197Yes8utf8_estonian_ciutf8198Yes8utf8_spanish_ciutf8199Yes8utf8_swedish_ciutf8200Yes8utf8_turkish_ciutf8201Yes8utf8_czech_ciutf8202Yes8utf8_danish_ciutf8203Yes8utf8_lithuanian_ciutf8204Yes8utf8_slovak_ciutf8205Yes8utf8_spanish2_ciutf8206Yes8utf8_roman_ciutf8207Yes8utf8_persian_ciutf8208Yes8utf8_esperanto_ciutf8209Yes8utf8_hungarian_ciutf8210Yes8utf8_sinhala_ciutf8211Yes8utf8_german2_ciutf8212Yes8utf8_croatian_ciutf8213Yes8utf8_unicode_520_ciutf8214Yes8utf8_vietnamese_ciutf8215Yes8utf8_general_mysql500_ciutf8223Yes1比较规则有以下规则:* 比较规则名称以与其关联的字符集的名称开头。如上图的查询结果的比较规则名称都是以utf8开头的。* 后边紧跟着该比较规则主要作用于哪种语言比如utf8_polish_ci 表示以波兰语的规则比较utf8_spanish_ci是以西班牙语的规则比较utf8_general_ci 是一种通用的比较规则。* 名称后缀意味着该比较规则是否区分语言中的重音、大小写啥的具体可以用的值如下 后缀 英文释义 描述_aiaccent insensitive不区分重音_asaccent sensitive区分重音_cicase insensitive不区分大小写_cscase sensitive区分大小写_binbinary以二进制方式比较 ### 3. 字符集和比较规则的级别* 服务器级别* 数据库级别* 表级别* 列级别#### 3.1 服务器级别* character_set_server服务器级别字符集* collation_server服务器级别比较规则##### 3.1.1 查看服务器级别的字符集和比较规则 show variables like ‘character_set_server’; show variables like ‘collation_server’;##### 3.1.2 修改服务器的默认值在类 UNIX 操作系统中MySQL 会按照下列路径来寻找配置文件* /etc/my.cnf* /etc/mysql/my.cnf* SYSCONFDIR/my.cnf* $MYSQL_HOME/my.cnf特定于服务器的选项仅限服务器* defaults-extra-file命令行指定的额外配置文件路径* ~/.my.cnf用户特定选项* ~/.mylogin.cnf用户特定的登录路径选项仅限客户端我们以修改 /etc/mysql/my.cnf 为例 [server] character_set_serverlatin1 collation_serverlatin1_general_cs修改完重启服务再次执行查询语句PS : 有可能修改完不生效可以执行chmod 644 my.cnf修改权限#### 3.2数据库级别##### 3.2.1 语法我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则具体语法如下 CREATE DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称]; ALTER DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称];##### 3.2.2查看数据库级别的字符集和比较规则 SHOW VARIABLES LIKE ‘character_set_database’; SHOW VARIABLES LIKE ‘collation_database’;可以看到这个 demo_db 数据库的字符集和比较规则就是我们在创建语句中指定的。需要注意的一点是character_set_database 和 collation_database 这两个系统变量是只读的我们不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则。如果未指定数据库级别的字符集和比较规则则使用服务器级别的字符集和比较规则#### 3.3 表级别##### 3.3.1 语法我们也可以在创建和修改表的时候指定表的字符集和比较规则语法如下 CREATE TABLE 表名 (列的信息) [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称]] ALTER TABLE 表名 [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称]##### 3.3.2查看表级别的字符集和比较规则 SHOW TABLE STATUS LIKE ‘table_name’;如果创建表的语句中没有指明字符集和比较规则将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。#### 3.4 列级别##### 3.4.1 语法 CREATE TABLE 表名( 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称], 其他列… ); ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];3.4.2 修改列的字符集和比较规则可以看出修改完表的字符集是gb2312列的字符集是utf8如果在创建和修改的语句中没有指明字符集和比较规则将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。PS :在转换列的字符集时需要注意如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。比方说原先列使用的字符集是utf8列中存储了一些汉字现在把列的字符集转换为ascii的话就会出错因为ascii字符集并不能表示汉字字符。### 4 仅修改字符集或仅修改比较规则由于字符集和比较规则是互相有联系的如果我们只修改了字符集比较规则也会跟着变化如果只修改了比较规则字符集也会跟着变化具体规则如下* 只修改字符集则比较规则将变为修改后的字符集默认的比较规则。* 只修改比较规则则字符集将变为修改后的比较规则对应的字符集。### 5.MySQL中字符集的转换#### 5.1 相关变量系统变量描述character_set_client服务器解码请求时使用的字符集character_set_connection服务器处理请求时会把请求字符串从character_set_client转为character_set_connectioncharacter_set_results服务器向客户端返回数据时使用的字符集#### 5.2 转换过程#### 5.3修改相关变量的值 SET NAMES 字符集名;等价于 SET character_set_client 字符集名; SET character_set_connection 字符集名; SET character_set_results 字符集名;
