分享一个今天踩得 MySQL 编码坑

有这样三张表,三张表主键都是 stu_num:

  • auth (utf8mb4),认证
  • info (utf8mb4),用户 basic info
  • apply (utf8),用户提交申请

起因是发现返回 apply 记录的 JSON 接口有一个用户字段重复了,该接口查询是:

SELECT ... FROM apply a LEFT JOIN info i ON a.stu_num=i.stu_num

排查后发现 apply 中多了一个全角stu_num的记录,1111,unicode \\uFF11。但插入 apply 表的stu_num是从 session 中取的,session 是在登录时设置的,登录验证逻辑:

uname, pwd = request.post_form.get();
if (hash(pwd) == sql_exec(\'SELECT pwd FROM auth WHERE uname=?\', uname))
    session.set(\'uname\', uname);

最后结合 log 发现是用户登录时提交了全角的数字 id,而由于auth表的字符集,uname=\'1111\'时将uname=\'1111\'的记录 SELECT 出来了,然后将全角字符设置进了 session 里,在 apply 时被插入了 apply 中

其实挺好奇用户用的什么浏览器 or 输入法会输入全角的数字


插曲

debug 过程中发现了一些以前没有注意到的,关于 MySQL COLLATE

排查时执行SELECT count(*) FROM apply WHERE stu_num=\'1111\';只返回了一条记录

而 bug 成因是SELECT pwd FROM auth WHERE uname=\'1111\'返回了stu_num=1111的记录。测试后是因为字符集 COLLATE 导致的,utf8mb4 存在将全角转换为半角的性质,而 utf8 的utf8_general_ci却没有这种性质

且查询 apply 的接口之所以 SELECT 出了两条记录,是因为 JOIN 操作 ON 判断的两张表中有一张表的字符集有转换全角的性质,修改 info 表字符集为 utf8 后再 join apply 表则只能 SELECT 出1111一条记录了

相关文章

2 thoughts on “分享一个今天踩得 MySQL 编码坑

发表评论

电子邮件地址不会被公开。 必填项已用*标注