异地登陆验证的简单实现

书到用时方恨少。最近遇到一个功能点：从日志表中将记录每个用户的IP信息收集统计到用户白名单项。

这个功能只执行一次（初始化），但数据量可能较大，执行java代码/python脚本较慢。正当我不知所措和我的小伙伴考虑用存储过程完成时，范总轻描淡写的说，不就是一句SQL嘛，那么复杂干啥？

满脸问号，我设想的最简单的方案都需要两个for循环的嵌套，一个SQL可以解决？

这时候明哥告诉我了一个函数：group_concat。正是这个函数，瞬间解决了我的问题。

1. 需求

想要实现一个用户白名单功能，以减少异地验证的频率，~~减少短信费用的支出，~~ 提升用户登陆体验。

2. 实现原理

由于之前没有接触过类似的功能，本次实现方法为：在用户表中新增用户IP白名单字段，用于存储用户登陆后的IP的前三位；在登陆时判断是否在一个城市中。这个办法相对而言比较笨，有些城市拥有的IP地址跨度较大。之后有空再写方法来分辨确切的省市自治区吧。也欢迎大佬们指导，让我学习下其他的验证方式。

3. 导入数据

之前的版本中，使用了较大的表来记录用户对接口的调用记录，其中包括了用户ID和用户IP信息。在这个表中，一个用户对应多行记录，一个IP也可以对应多条记录（也可能对应多个账户）。我下意识就想写一个java项目或者python脚本完成这个工作。但是java工具需要建立Java项目确定依赖情况；python原生链接数据库进行处理较慢（数据量大）。我和小林进行讨论后，觉得还是写一个存储过程解决比较简单靠谱。

就在准备动手的时候，我和明哥抱怨了一句：这么复杂的功能，一个SQL怎么可能完成？。明哥了解我的需求后，表示一个SQL足够完成数据整理导入用户表，并向我推荐了一个函数：group_concat。

group_concat，与concat类似，用于字符串的拼接。但与concat应用场景不同，group_concat主要适用于在group by语句中拼接分类后的结果字段。基本语法为：

group_concat( [distinct] 待连接字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )

将其置入查询语句的select 之后，就可以轻易的完成批量获取用户分组后的IP信息了。

select SELECT create_user_id, group_concat( DISTINCT concat( '#', SUBSTRING_INDEX   ( ip_address, '.', 3 ) ) SEPARATOR '' ) FROM sys_log GROUP BY create_user_id;

为了符合设计（设计的IP白名单格式为#xxx.xxx.xxx）：使用SUBSTRING_INDEX函数获取了第三个’.’之前的字符串；使用concat将’#’拼接到切割后的IP字段之前；使用group_concat函数中的SEPARATOR关键字将默认的逗号（’,’）分隔符替换为空。执行之后，就只有用户ID和指定格式的用户IP白名单了。

对于修改用户表，可以直接将上述的sql放置在update语句中，增加一些判断，则一个SQL就完成了IP地址的转储：

update sys_user a
set a.address_ip = (SELECT group_concat(distinct concat('#', SUBSTRING_INDEX(ip_address, '.', 3)) separator '')
                    FROM sys_log b
                    where b.create_user_id = a.id
                    group by create_user_id);

END～