关于字符集:故障分析-MySQL-convert-函数导致的字符集报错处理

作者:徐耀荣 爱可生南区交付服务部 DBA 团队成员,次要负责MySQL故障解决以及相干技术支持。喜好电影,游戏,游览以及桌球。 本文起源:原创投稿 *爱可生开源社区出品,原创内容未经受权不得随便应用,转载请分割小编并注明起源。 一、问题背景有客户之前遇到一个 mysql8.0.21 实例中排序规定的报错,是在调用视图时抛出,报错信息如下: ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=' 二、问题模仿mysql> show create table t1\G;*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `name1` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci1 row in set (0.00 sec)mysql> show create table t2\G;*************************** 1. row *************************** Table: t2Create Table: CREATE TABLE `t2` ( `name2` varchar(12) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> CREATE VIEW t3 as select * from t1,t2 where `t1`.`name1`= `t2`.`name2`;Query OK, 0 rows affected (0.06 sec)mysql> select * from t3;ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='三、问题剖析通过查看视图定义,能够发现因为视图中波及到的两张表字符集不同,所以创立视图时 MySQL 会主动应用 convert 函数转换字符集。 ...

February 9, 2023 · 4 min · jiezi

关于字符集:字符编码原理

ASCII 编码在计算机外部,所有信息最终都是一个二进制值。每一个二进制位(bit)有0和1两种状态,因而八个二进制位就能够组合出256种状态,这被称为一个字节(byte)。也就是说,一个字节一共能够用来示意256种不同的状态,每一个状态对应一个符号,就是256个符号,从00000000到11111111。 上个世纪60年代,美国制订了一套字符编码,对英语字符与二进制位之间的关系,做了对立规定。这被称为 ASCII 码,始终沿用至今。ASCII 码一共规定了128个字符的编码,比方空格SPACE是32(二进制00100000),大写的字母A是65(二进制01000001)。这128个符号(包含32个不能打印进去的管制符号),只占用了一个字节的前面7位,最后面的一位对立规定为0 非ASCII 编码英语用128个符号编码就够了,然而用来示意其余语言,128个符号是不够的。比方,在法语中,字母上方有注音符号,它就无奈用 ASCII 码示意。于是,一些欧洲国家就决定,利用字节中闲置的最高位编入新的符号。比方,法语中的é的编码为130(二进制10000010)。这样一来,这些欧洲国家应用的编码体系,能够示意最多256个符号。 然而,这里又呈现了新的问题。不同的国家有不同的字母,因而,哪怕它们都应用256个符号的编码方式,代表的字母却不一样。比方,130在法语编码中代表了é,在希伯来语编码中却代表了字母Gimel (),在俄语编码中又会代表另一个符号。然而不管怎样,所有这些编码方式中,0--127示意的符号是一样的,不一样的只是128--255的这一段。 至于亚洲国家的文字,应用的符号就更多了,汉字就多达10万左右。一个字节只能示意256种符号,必定是不够的,就必须应用多个字节表白一个符号。比方,简体中文常见的编码方式是 GB2312,应用两个字节示意一个汉字,所以实践上最多能够示意 256 x 256 = 65536 个符号。 Unicode 编码世界上存在着多种编码方式,同一个二进制数字能够被解释成不同的符号。因而,要想关上一个文本文件,就必须晓得它的编码方式,否则用谬误的编码方式解读,就会呈现乱码。为什么电子邮件经常呈现乱码?就是因为发信人和收信人应用的编码方式不一样。 如果有一种编码,将世界上所有的符号都纳入其中。每一个符号都给予一个举世无双的编码,那么乱码问题就会隐没。这就是 Unicode,就像它的名字都示意的,这是一种所有符号的编码。 Unicode 当然是一个很大的汇合,当初的规模能够包容100多万个符号。每个符号的编码都不一样,比方,U+0639示意阿拉伯字母Ain,U+0041示意英语的大写字母A,U+4E25示意汉字严。具体的符号对应表,能够查问unicode.org,或者专门的汉字对应表。 Unicode 的问题Unicode 只是一个符号集,它只规定了符号的二进制代码,却没有规定这个二进制代码应该如何存储。 比方,汉字严的 Unicode 是十六进制数4E25,转换成二进制数足足有15位(100111000100101),也就是说,这个符号的示意至多须要2个字节。示意其余更大的符号,可能须要3个字节或者4个字节,甚至更多。 这里就有两个重大的问题,第一个问题是,如何能力区别 Unicode 和 ASCII ?计算机怎么晓得三个字节示意一个符号,而不是别离示意三个符号呢?第二个问题是,咱们曾经晓得,英文字母只用一个字节示意就够了,如果 Unicode 对立规定,每个符号用三个或四个字节示意,那么每个英文字母前都必然有二到三个字节是0,这对于存储来说是极大的节约,文本文件的大小会因而大出二三倍,这是无奈承受的。 它们造成的后果是:1)呈现了 Unicode 的多种存储形式,也就是说有许多种不同的二进制格局,能够用来示意 Unicode。2)Unicode 在很长一段时间内无奈推广,直到互联网的呈现。 UTF-8 编码互联网的遍及,强烈要求呈现一种对立的编码方式。UTF-8 就是在互联网上应用最广的一种 Unicode 的实现形式。其余实现形式还包含 UTF-16(字符用两个字节或四个字节示意)和 UTF-32(字符用四个字节示意),不过在互联网上根本不必。反复一遍,这里的关系是,UTF-8 是 Unicode 的实现形式之一。 UTF-8 最大的一个特点,就是它是一种变长的编码方式。它能够应用1~4个字节示意一个符号,依据不同的符号而变动字节长度。 UTF-8 的编码规定很简略,只有二条: 1)对于单字节的符号,字节的第一位设为0,前面7位为这个符号的 Unicode 码。因而对于英语字母,UTF-8 编码和 ASCII 码是雷同的。 2)对于n字节的符号(n > 1),第一个字节的前n位都设为1,第n + 1位设为0,前面字节的前两位一律设为10。剩下的没有提及的二进制位,全副为这个符号的 Unicode 码。 ...

October 15, 2021 · 1 min · jiezi

关于字符集:字符集和字符编码什么关系

严格来说,字符集和字符编码不是一个概念:字符集定义了字符和二进制的对应关系,为每个字符调配了惟一的编号。能够将字符集了解成一个很大的表格,它列出了所有字符和二进制的对应关系,计算机显示文字或者存储文字,就是一个查表的过程。 字符编码规定了如何将字符的编号存储到计算机中。如果应用了相似 GB2312 和 GBK 的变长存储计划(不同的字符占用的字节数不一样),那么为了辨别一个字符到底应用了几个字节,就不能将字符的编号间接存储到计算机中,字符编号在存储之前必须要通过转换,在读取时还要再逆向转换一次,这套转换计划就叫做字符编码。 有的字符集在制订时就思考到了编码的问题,是和编码联合在一起的,例如ASCII、GB2312、GBK、BIG5 等,所以无论称作字符集还是字符编码都无所谓,也不好辨别两者的概念。而有的字符集只管制订字符的编号,至于怎么存储,那是字符编码的事件,Unicode 就是一个典型的例子,它只是定义了寰球文字的惟一编号,咱们还须要 UTF-8、UTF-16、UTF-32 这几种编码方案将 Unicode 存储到计算机中。 Unicode 能够应用的编码方案有三种,别离是: UTF-8:一种变长的编码方案,应用 1~6 个字节来存储;UTF-32:一种固定长度的编码方案,不论字符编号大小,始终应用 4 个字节来存储;UTF-16:介于 UTF-8 和 UTF-32 之间,应用 2 个或者 4 个字节来存储,长度既固定又可变。UTF 是 Unicode Transformation Format 的缩写,意思是“Unicode转换格局”,前面的数字表明至多应用多少个比特位(Bit)来存储字符。 1) UTF-8UTF-8 的编码规定很简略: 如果只有一个字节,那么最高的比特位为 0,这样能够兼容 ASCII;如果有多个字节,那么第一个字节从最高位开始,间断有几个比特位的值为 1,就应用几个字节编码,剩下的字节均以 10 结尾。具体的表现形式为: 0xxxxxxx:单字节编码模式,这和 ASCII 编码齐全一样,因而 UTF-8 是兼容 ASCII 的;110xxxxx 10xxxxxx:双字节编码模式(第一个字节有两个间断的 1);1110xxxx 10xxxxxx 10xxxxxx:三字节编码模式(第一个字节有三个间断的 1);11110xxx 10xxxxxx 10xxxxxx 10xxxxxx:四字节编码模式(第一个字节有四个间断的 1)。xxx 就用来存储 Unicode 中的字符编号。 上面是一些字符的 UTF-8 编码实例(着色局部示意原本的 Unicode 编号): 字符字母N符号æ中文⻬Unicode 编号(二进制)010011101110011000101110 11101100Unicode 编号(十六进制)4EE62E ECUTF-8 编码(二进制)0100111011000011 1010011011100010 10111011 10101100UTF-8 编码(十六进制)4EC3 A6E2 BB AC对于罕用的字符,它的 Unicode 编号范畴是 0 ~ FFFF,用 1~3 个字节足以存储,只有及其常见,或者只有多数地区应用的字符才须要 4~6个字节存储。 ...

May 14, 2021 · 2 min · jiezi

关于字符集:自动根据文本文件的字符集编码加载文件内容字符串

主动依据文本文件的字符集编码加载文件内容字符串,并反对按原始编码格局再次写入。 using System;using System.IO;using System.Linq;using System.Text;/// <summary> /// 获取文件的编码格局 /// </summary> public class TextEncode{ public Encoding Encoding { get; private set; } public byte[] BOM { get; private set; } public string ReadText(string fileName, out Encoding enc) { var r = ReadText(fileName); enc = Encoding; return r; } public string ReadText(string fileName) { Encoding encoding = null; byte[] bytes = File.ReadAllBytes(fileName); int bomLen = 0; if (bytes.Length > 1) { if (bytes[0] == 0xFE && bytes[1] == 0xFF) //UTF-16(大端序) { encoding = new UnicodeEncoding(true, true); bomLen = 2; } else if (bytes[0] == 0xFF && bytes[1] == 0xFE) //UTF-16(小端序) { encoding = new UnicodeEncoding(false, true); bomLen = 2; } } if (encoding == null && bytes.Length > 2) { if ((bytes[0] == 0xEF && bytes[1] == 0xBB && bytes[2] == 0xBF)) //UTF-8 { encoding = new UTF8Encoding(true); bomLen = 3; } } if (encoding == null && bytes.Length > 3) { if (bytes[0] == 0x00 && bytes[1] == 0x00 && bytes[2] == 0xFE && bytes[3] == 0xFF) //UTF-32(大端序) { encoding = new UTF32Encoding(true, true); bomLen = 4; } else if (bytes[0] == 0xFF && bytes[1] == 0xFE && bytes[2] == 0x00 && bytes[3] == 0x00) //UTF-32(小端序) { encoding = new UTF32Encoding(false, true); bomLen = 4; } } if (encoding == null && IsUTF8Bytes(bytes)) { encoding = Encoding.UTF8; //UTF8无BOM } if (encoding == null) encoding = Encoding.Default; Encoding = encoding; BOM = new byte[bomLen]; Array.Copy(bytes, BOM, bomLen); return encoding.GetString(bytes, bomLen, bytes.Length - bomLen); } /// <summary> /// 判断是否是不带 BOM 的 UTF8 格局 /// </summary> /// <param name="data"></param> /// <returns></returns> private bool IsUTF8Bytes(byte[] data) { int charByteCounter = 1; //计算以后正剖析的字符应还有的字节数 byte curByte; //以后剖析的字节. for (int i = 0; i < data.Length; i++) { curByte = data[i]; if (curByte == 0) throw new FormatException("非预期的byte格局"); if (charByteCounter == 1) { if (curByte >= 0x80) { //判断以后 while (((curByte <<= 1) & 0x80) != 0) { charByteCounter++; } //标记位首位若为非0 则至多以2个1开始 如:110XXXXX...........1111110X if (charByteCounter == 1 || charByteCounter > 6) { return false; } } } else { //若是UTF-8 此时第一位必须为1 if ((curByte & 0xC0) != 0x80) { return false; } charByteCounter--; } } if (charByteCounter > 1) { throw new FormatException("非预期的byte格局"); } return true; } /// <summary> /// 已雷同的Encoding和BOM再次写入 /// </summary> public void WriteBySameEncoding(string fileName, string content) { if (BOM == null) { throw new Exception("须要先调用ReadText办法"); } using (var file = File.Create(fileName)) { file.Write(BOM, 0, BOM.Length); var bytes = Encoding.GetBytes(content); file.Write(bytes, 0, bytes.Length); } }}

September 21, 2020 · 2 min · jiezi

MySQL乱码的原因和设置UTF8数据格式

MySQL使用时,有一件很痛苦的事情肯定是结果乱码。将编码格式都设置为UTF8可以解决这个问题,我们今天来说下为什么要这么设置,以及怎么设置。MySQL字符格式字符集在编程语言中,我们为了防止中文乱码,会使用unicode对中文字符做处理,而为了降低网络带宽和节省存储空间,我们使用UTF8进行编码。对这两者有什么不同不够了解的同学,可以参考Unicode字符集和UTF8编码编码的前世今生这篇文章。同样在MySQL中,我们也会有这样的处理,我们可以查看当前数据库设置的编码方式(字符集):mysql> show variables like ‘%char%’;+————————–+———————————-+| 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 | /usr/local/mysql/share/charsets/ | +————————–+———————————-+8 rows in set (0.00 sec)表中就是当前设置的字符集,先看不用关注的几个值:character_set_filesystem | binary:文件系统上的存储格式,默认为binary(二进制)character_set_system | utf8:系统的存储格式,默认为utf8character_sets_dir | /usr/local/mysql/share/charsets/:可以使用的字符集的文件路径剩下的几个就是日常影响读写乱码的参数了:- character_set_client:客户端请求数据的字符集- character_set_connection:从客户端接收到数据,然后传输的字符集- character_set_database:默认数据库的字符集;如果没有默认数据库,使用character_set_server字段- character_set_results:结果集的字符集- character_set_server:数据库服务器的默认字符集字符集的转换流程分为3步:客户端请求数据库数据,发送的数据使用character_set_client字符集MySQL实例收到客户端发送的数据后,将其转换为character_set_connection字符集进行内部操作时,将数据字符集转换为内部操作字符集:使用每个数据字段的character set设定值若不存在,使用对应数据表的default character set设定值若不存在,使用对应数据库的default character set设定值若不存在,使用character_set_server设定值将操作结果值从内部操作字符集转换为character_set_results字符序说字符序之前,我们需要了解一点基础知识:字符(Character)是指人类语言中最小的表义符号。例如’A’、’B’等;给定一系列字符,对每个字符赋予一个数值,用数值来代表对应的字符,这一数值就是字符的编码(Encoding)。例如,我们给字符’A’赋予数值0,给字符’B’赋予数值1,则0就是字符’A’的编码;给定一系列字符并赋予对应的编码后,所有这些字符和编码对组成的集合就是字符集(Character Set)。例如,给定字符列表为{‘A’,’B’}时,{‘A’=>0, ‘B’=>1}就是一个字符集;字符序(Collation)是指在同一字符集内字符之间的比较规则;确定字符序后,才能在一个字符集上定义什么是等价的字符,以及字符之间的大小关系;每个字符序唯一对应一种字符集,但一个字符集可以对应多种字符序,其中有一个是默认字符序(Default Collation);MySQL中的字符序名称遵从命名惯例:以字符序对应的字符集名称开头;以_ci(表示大小写不敏感,case insensitive)、_cs(表示大小写敏感,case sensitive)或_bin(表示按编码值比较,binary)结尾。例如:在字符序“utf8_general_ci”下,字符“a”和“A”是等价的;因此字符序不同于字符集,用于数据库字段的相等或大小比较。我们查看MySQL实例设置的字符序:mysql> show variables like ‘collation%’;+———————-+——————-+| Variable_name | Value |+———————-+——————-+| collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +———————-+——————-+3 rows in set (0.00 sec)跟utf8对应的常用字符序是:utf8_unicode_ci/utf8_general_ci和utf8_bin等,那么他们的区别是什么呢?_bin是用二进制存储并比较,区别大小写,存储二进制内容时使用utf8_general_ci:校对速度快,但准确度稍差,使用中英文时使用utf8_unicode_ci:准确度高,但校对速度稍慢,使用德法俄等外语时使用详细的区别可以参考 Mysql中的排序规则utf8_unicode_ci、utf8_general_ci的区别总结。修改字符集和字符序如果在MySQL连接时,出现了乱码的问题,那么基本可以确定是各个字符集/序设置不统一的原因。MySQL默认的latin1格式不支持中文,由于我们在中国,所以选择对中文和各语言支持都非常完善的utf8格式。所以,我们需要将需要关注的字符集和字符序都修改为utf8格式。你也可以选择utf8mb4格式,这个格式支持保存emoji????表情。我们需要修改Mysql的配置文件,查看配置文件的位置有两种方式:1. $ mysql –help | grep ‘my.cnf’ /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 2. ps aux | grep mysql在(1)中,/etc/my.cnf, /etc/mysql/my.cnf, ~/.my.cnf 这些就是mysql默认会搜寻my.cnf的目录,优先级依次升高。可以在各个配置文件里都使用long_query_time 来测试一下。然后,我们修改或新增下面的配置项:# 下面注释的几行可以不设置,但如果你的没有生效,也可以试试看[mysqld]character_set_server=utf8collation-server=utf8_general_ciskip-character-set-client-handshake#init_connect=‘SET NAMES utf8’#[client]#default-character-set=utf8这三行配置就可以解决问题,最关键的是最后一行,参考mysql文档,使用该参数会忽略客户端传递的字符集信息,而直接使用服务端的设定;再加上我们设定服务端的字符集和字符序均为utf8,这样就保证了字符格式的统一,解决乱码的问题。重启mysql服务,如果提示找不到服务,请参考用service命令管理mysql启停:$ service mysqld restartShutting down MySQL.. [ OK ]Starting MySQL. [ OK ]连接到mysql,查看当前编码:mysql> show variables like ‘%char%’;+————————–+———————————-+| 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 | /usr/local/mysql/share/charsets/ | +————————–+———————————-+8 rows in set (0.01 sec)mysql> show variables like ‘collation%’;+———————-+—————–+| Variable_name | Value |+———————-+—————–+| collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +———————-+—————–+3 rows in set (0.01 sec)可以看到一切都符合预期,请求和存储的数据也不再是乱码了。遇到的问题unknown variable ‘default-character-set=utf8’参考官方文档,该参数自5.5.3版本废弃,改为了character-set-server,改为这个参数即可。但这个是在[mysqld]下的配置,在[client]下的配置依然使用default-character-set参数。参考资料MySQL连接校对:utf8_general_ci与utf8_unicode_ci有什么区别呢 :https://segmentfault.com/q/10…Unicode 和 UTF-8 有什么区别?:https://www.zhihu.com/questio…深入Mysql字符集设置:http://www.laruence.com/2008/…10.4 Connection Character Sets and Collations:https://dev.mysql.com/doc/ref…ISO/IEC 8859-1:https://zh.wikipedia.org/wiki…5.1.6 Server Command Options:https://dev.mysql.com/doc/ref…用service命令管理mysql启停:https://segmentfault.com/a/11…Unicode字符集和UTF8编码编码的前世今生:https://segmentfault.com/a/11…Mysql中的排序规则utf8_unicode_ci、utf8_general_ci的区别总结:https://www.jb51.net/article/… ...

March 26, 2019 · 2 min · jiezi