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

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

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

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

排查后发现 apply 中多了一个全角stu_num的记录，１１１１，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='１１１１'时将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='１１１１'返回了stu_num=1111的记录。测试后是因为字符集 COLLATE 导致的，utf8mb4 存在将全角转换为半角的性质，而 utf8 的utf8_general_ci却没有这种性质

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