后端大部分时候在玩儿MySQL（或者其他存储），而针对MySQL我们碰到过各种各样的问题，在此一并整理记录。

背景信息

假设我们的问题都出在t_biz表上，这是他的表定义：

1
2
3
4
5
6
7
8

CREATE TABLE `t_biz` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL DEFAULT '' COMMENT '编码',
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT '名称',
  `status` tinyint(2) NOT NULL DEFAULT '1' COMMENT '状态,1可用 0不可用',
  PRIMARY KEY (`id`),
  KEY `idx_code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

Terms：

SQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- 每个问题对应`MySQL`版本可能不一样，在看问题前需要看下版本信息：
show variables like '%version%';
-- 查看服务端字符集设置：
SHOW VARIABLES LIKE 'character_set_server';
-- 查看校验集：
SHOW VARIABLES LIKE 'collation_server';
-- 服务器解码请求时使用的字符集
SHOW VARIABLES LIKE 'character_set_client';
-- 服务器处理请求时会把请求字符串从 character_set_client 转为 character_set_connection
SHOW VARIABLES LIKE 'character_set_connection';
-- 服务器向客户端返回数据时使用的字符集
SHOW VARIABLES LIKE 'character_set_results';
-- 统一设置上述三个character_set：字符集 指的是某个字符范围的编码规则
SET NAMES 'utf8mb4';
-- 查看校验集：比较规则是针对某个字符集中的字符比较大小的一种规则。
SHOW COLLATION LIKE 'utf8\_%';

类型

uint8  : 0 to 255 
uint16 : 0 to 65535 
uint32 : 0 to 4294967295 
uint64 : 0 to 18446744073709551615 
int8   : -128 to 127 
int16  : -32768 to 32767 
int32  : -2147483648 to 2147483647 
int64  : -9223372036854775808 to 9223372036854775807

查询数字不符合预期，数字字符串转型并溢出

MySQL版本5.7.26-29-log。

过程

  1. 我们的sql： select * from t_biz where code=43050172683600000019。
  2. 发现查出了code为43050172683600000214的数据。 字段类型为varchar。

分析解决

  1. 查询条件使用'‘包裹变量即用字符串匹配时没有问题。
  2. 说明此时在数据库层面发生了cast类型转换，而且此时的查询条件超出了int64 long的长度。判断此时的隐式转型出现了截断。
  3. 验证：
    1. select cast(43050172683600000019 as signed integer),cast(43050172683600000214 as signed integer)from dual;，结果均为9223372036854775807；
    2. select cast(43050172683600000019 as unsigned integer),cast(43050172683600000214 as unsigned integer)from dual;，结果均为18446744073709551615；
  4. MySQL本身是强schema的存储系统，因此指定了字段类型时，查询要确保类型一致。

启发

对类型的边界值要敏感。

varchar字段应用端判断不相等，写入时报Duplicate entry

MySQL版本5.6.16-log。

过程

  1. 针对code增加uniq索引；
1

create unique index uniq_code on t_biz (code);
  1. 插入数据；
1

INSERT INTO t_biz (code, name, status) VALUES ('a', 'aName', 1);
  1. 插入第二条数据； 此时我们java端认为 'a'.equals('A') == false，也就是说这是条新的数据。
1

INSERT INTO t_biz (code, name, status) VALUES ('A', 'AName', 1);
  1. 报错； [23000][1062] Duplicate entry ‘A’ for key ‘uniq_code’。

分析解决

  1. 首先我们在对应写数据的代码处打了断点debug。 确认了字符串的真实值。
  2. 建表时指定了CHARSET=utf8mb4，对应的校验集collation默认为utf8mb4_general_ci。 官网相关文档：5.6/en/charset-general 说明了校验集的作用之一是：Compare strings using a variety of collations.，而ci全称是case ignore。既然是忽略大小写的，那 'a'=='A' 就说得通了！
  3. 根据业务指定需要的collation，程序中与存储设置逻辑保持一致。

启发

对字符集、校验集要敏感。

用户输入自己的名字搜不到结果，入参输入有误

发生在2017年，排查几分钟，最后结果不是个技术问题。但是有点意思。

过程

  1. 背景； 用户名我们暂且叫小国吧，我们的t_biz存储了用户的信息，提供一个基本的查询功能，通过名字，查到自己的数据。
  2. 提供线索、入参； 用户输入了小囯。

解决

小囯 != 小国。

就这么简单。但是对我们的启示之一：索要线索、入参时一定要保证准确性，否则线索失真，排查成本就变高。

启发

对入参要敏感。