本文转载自微信公众号《码农的私房话》，作者Liew。转载本文请联系码农包间公众号。由于集团内部技术栈改造，需要用Java重构一个用NodeJS编写的业务后台模块。该模块包含根据姓名模糊查询联系人标签的功能。这是一个很常见的CRUD操作，但是令人费解的问题是模糊查询并没有查出数据。项目使用MySQL数据库，配置编码为utf8。具体表结构语句如下：CREATETABLE`t_touch_label`(`id`int(11)unsignedNOTNULLAUTO_INCREMENT,`label_name`varchar(100)DEFAULTNULLCOMMENT'标签名',`state`tinyint(1)DEFAULT'1'COMMENT'已启用',`merchant_id`int(11)NOTNULLCOMMENT'用户ID',`remark`int(11)DEFAULTNULLCOMMENT'remark'PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;当客户端的编码与MySQL服务器的编码一致时，执行如下语句即可正常查询数据。selectid,label_namefromt_touch_labelwheremerchant_id=10086andlabel_namelike%B圆标签%；但是如果数据库jdbc-url配置中没有指定编码characterEncoding=utf8，会导致like模糊查询命中数据失败，因为客户端操作数据的编码与MySQLServer存储的编码格式不同引擎使用的不一致。问题复现首先通过日志定位到有问题的SQL：selectid,label_namefromt_touch_labelwheremerchant_id=10086andlabel_namelike%Broundlabel%;并验证SQL是否在测试数据库上正常查询和过滤数据。令我震惊的是，一切都很正常。接下来查看本地MyBatis生成的SQL日志，确实发现了0条数据，而且代码运行正常，没有报错，奇怪了。于是大胆猜测：可能是中文参数导致的问题。把参数改成英文就可以正常了吗？果然，当参数输入英文字符后，一切就变得那么正常了。同时也验证了我的“大胆猜测”是正确的。顺着这个思路，查看了MySQL服务器：character_set_client：客户端使用的字符集，可以通过characterEncoding进行配置。当客户端没有配置characterEncoding时，会自动检测并使用MySQL服务器的character_set_server变量的变量值。character_set_results：以客户端使用的字符集返回查询结果，包括结果数据（如列值）、结果元数据（如列名）和错误信息。character_set_system：MySQL服务器存储元数据的字符集始终是utf8。character_sets_dir：字符集的安装目录。character_set_connection：这个字符集用于没有字符集描述的常量和数字到字符串的转换。character_set_server：服务器默认字符集编码。character_set_database：默认数据库使用的字符集。每当默认数据库更改时，服务器都会设置此变量。如果没有默认数据库，该变量的值与character_set_server相同。character_set_filesystem：文件系统字符集编码，主要用于解析文件名的字符串字面值，如loaddata、loadfile和selectintooutfile等语句。在打开文件之前，文件名会从character_set_client指定的编码转换为character_set_filesystem指定的编码。默认值为二进制，即不进行转换。在项目中，配置MySQL数据库连接池：观察上面jdbc-url项的配置，不难发现url中并没有配置characterEncoding字符集编码。于是在数据库的jdbc-url中加入配置characterEncoding=utf8，再次使用中文模糊查询就恢复正常了。可以看出，MySQL在使用like模糊查询时，命中数据失败的问题是由于MySQL服务器端和客户端字符集编码集不一致导致的。编码不一致，打不中数据？MySQL在存储数据和查询数据时，数据编解码过程如下：MySQLClient根据jdbc-url中设置的characterEncoding字符编码（如果没有配置，则使用MySQLServer配置的character_set_server）转换成二进制流，并传输到MySQL服务器。MySQLServer收到请求后，将请求数据data从character_set_client转换为character_set_connection。在内部操作数据之前，将请求数据从character_set_connection转换为内部操作的字符集，其编码决定顺序：使用表字段的字符集值。当上述值不存在时，使用数据表的默认字符集值。如果上述值不存在，则使用数据库的默认字符集值。如果以上值都不存在，则使用character_set_server值。引擎层读写存储文件，涉及内部操作字符集和二进制流的相互转换；将内部操作字符集的操作结果转换为character_set_results。MySQLClient收到数据后，根据本地配置的字符编码characterEncoding渲染查询结果。数据文件到存储引擎编解码：执行selectleft(name,2)fromtable语句时，加载数据文件时存储引擎读取的name值为E4B8ADE69687，left(name,2)操作需要分词对于内容：如果按照GBK编码，则将值分成E4B8、ADE6、9687三个字符，返回给客户端的值为E4B8ADE6；如果按照UTF8编码，则分为E4B8AD、E69687，返回给客户端的值为E4B8ADE69687。可以看出，从数据文件中读取数据后，如果不进行编解码，则存储引擎内部无法进行字符维度的操作。除了上述编码不一致导致无法命中数据外，MySQL访问数据的乱码也可能导致数据访问出现乱码问题。例如，向字符集为utf8的数据表插入utf8编码的数据时，查询时设置连接字符集为utf8。另外，在MySQLServer的字符集配置中，character_set_client、character_set_results、character_set_connection等变量的默认值为latin1。插入操作的数据会经过latin1->latin1->utf8的字符集转换过程，在此过程中每个汉字会从原来的3字节存储为6字节。查询到的数据会从utf8->utf8字符集转换，返回保存的6个字节，导致乱码。当单个进程中的编码不一致，无法在两个字符集之间进行无损编码转换时，也会出现乱码。比如MySQLClient使用的编码是utf8，但是MySQLServer的character_set_client是gbk，表的字符集是utf8，那么肯定是乱码。虽然客户端的字符编码格式和最终表的字符编码格式不同，但是只要存储和查询操作的字符集编码一致，可以无损转换，就不会出现乱码。避免编码不一致的措施1、在建立数据库、表结构或数据库操作时，尽量明确指定所使用的字符集。character_set_client、character_set_result、character_set_connection等变量值与数据库表字段的字符集定义相同，不依赖于MySQL的默认设置。否则在升级MySQL的时候可能会造成很大的麻烦。2、当数据库和连接字符集都使用latin1时，大多数情况下可以解决乱码问题，但缺点是不能以字符为单位进行SQL操作。一般情况下，将数据库和连接字符集设置为utf8，避免出现编码问题。3、my.cnf文件中的default_character_set设置只影响命令连接服务器时的连接字符集。4、SQL函数对字段的操作通常在内部操作字符集中进行，不受连接字符集设置的影响。5、SQL语句中的字符串会受到连接字符集或introducer设置的影响（即在SQL中直接为查询列指定字符集），因此比较操作可能会产生不同的结果。
