利用mysql5.6 的st_distance 实现按照距离远近排序。

一、用户需求

         1、显示我附近的小区。

         2、由近到远排序。

         3、显示距离

二、数据库表结构数据：

CREATE TABLE `t_users_main_ext` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '玩家ID',
`lng` decimal(9,6) NOT NULL DEFAULT '0.000000' COMMENT '经度',
`lat` decimal(9,6) NOT NULL DEFAULT '0.000000' COMMENT '纬度',
`sex` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '性别（0：保密，1：男，2：女）',
`age` tinyint(4) NOT NULL DEFAULT '0' COMMENT '年龄',
`car` tinyint(1) NOT NULL DEFAULT '0' COMMENT '车主认证状态（1：已通过）',
`enterprise` tinyint(1) NOT NULL DEFAULT '0' COMMENT '企业认证状态（1：已通过）',
`school` tinyint(1) NOT NULL DEFAULT '0' COMMENT '名校认证状态（1：已通过）',
`finance` tinyint(1) NOT NULL DEFAULT '0' COMMENT '理财认证状态（1：已通过）',
`user_type` tinyint(1) NOT NULL DEFAULT '1' COMMENT '玩家用户类型(1游客 2微信 3支付宝 4花田机器人 5小红书机器人)',
`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '账号状态（0：正常，1：封停）',
`time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '修改时间',
`phone` varchar(20) NOT NULL DEFAULT '' COMMENT '用户手机号',
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`),
KEY `LAT` (`lng`,`lat`),
KEY `time` (`time`),
KEY `USER_PHONE` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=237584 DEFAULT CHARSET=utf8 COMMENT='用户主扩展表';

三、功能实现

我当前所处在的位置（113.858202 , 22.583819 ）,需要查询我附近10KM内的小区，并安装由近到远的顺序排列。

 

SELECT
s.id ,s.`user_id`,(st_distance (point (lng, lat),point(113.858202,22.583819) ) / 0.0111) AS distance
FROM
t_users_main_ext s
HAVING distance<10
ORDER BY distance

 

 

 

而在实际的使用中,不太可能会发生需要计算该用户与所有其他用户的距离,然后再排序的情况,当用户数量达到一个级别时,就可以在一个较小的范围里进行搜索,而非在所有用户中进行搜索.

所以对于这个例子,我增加了4个where条件,只对于经度和纬度大于或小于该用户1度(111公里)范围内的用户进行距离计算,同时对数据表中的经度和纬度两个列增加了索引来优化where语句执行时的速度.

最终的sql语句如下

SELECT
s.`user_id`,(st_distance (point (lng, lat),point(113.858202,22.583819) ) / 0.0111) AS distance
FROM
t_users_main_ext s
WHERE
lat > 22.583819-1 and
lat < 22.583819+1 and
lng > 113.858202-1 and
lng < 113.858202+1