mysql 根据地址经纬度查询距离

SELECT *,
    round(
        (
        6371 * acos (
        cos ( radians(34.72468) )
        * cos( radians( job_lat ) )
        * cos( radians( job_lng ) - radians(113.6401) )
        + sin ( radians(34.72468) )
        * sin( radians( job_lat ) )
        )
        )
    ,2)AS distance
FROM
    test_1
WHERE 1=1 HAVING distance < 50
                ORDER BY distance

郑州：经度：113.6401 纬度：34.72468

查询的表中必须要含有经纬度。

创建数据信息：

CREATE TABLE `test_1`  (
  `job_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'job表的id',
  `job_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工作地址',
  `job_lng` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '经度',
  `job_lat` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '纬度',
  PRIMARY KEY (`job_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '兼友圈兼职工作' ROW_FORMAT = Dynamic;

INSERT INTO `test_1` VALUES ('02b591d465414eda941fcf634ecbaa3120200818145124', '郑州市二七万达广场', '113.642998', '34.718029'); INSERT INTO `test_1` VALUES ('0b7ade3b3b0c4574814a7bbdca2cb88420200818145124', '郑州市火车站', '113.661537', '34.744843'); INSERT INTO `test_1` VALUES ('103fc835d64843fa8715ced59dfb423b20200818145125', '郑州市郑州东站', '113.779694', '34.759293'); INSERT INTO `test_1` VALUES ('5956b3f5e7b44eb9a55f72b89e8d62a420200818145125', '郑州会展中心', '113.730286', '34.773014'); INSERT INTO `test_1` VALUES ('b196ee36d13448d490c839e6a1149d2820200818145121', '郑州市河南省人民医院', '113.694672', '34.773144'); INSERT INTO `test_1` VALUES ('d9daa69cbbb246218537cb53fdf241d120200818145124', '郑州市河南省体育场', '113.665611', '34.768169'); INSERT INTO `test_1` VALUES ('f0ca5818936245ca8b965e18b4ce65a120200818145125', '郑州市郑州南站', '113.684013', '34.710991');