DELIMITER $$

-- USE `XXXX数据库`$$

DROP FUNCTION IF EXISTS `calc_distance`$$

CREATE FUNCTION `calc_distance`(
    lat1 DECIMAL(10,6),
    lng1 DECIMAL(10,6),
    lat2 DECIMAL(10,6),
    lng2 DECIMAL(10,6)
) RETURNS DECIMAL(15,2)
    DETERMINISTIC
BEGIN
    DECLARE earth_radius DECIMAL(10,0) DEFAULT 6371;
    DECLARE rad_lat1, rad_lng1, rad_lat2, rad_lng2 DECIMAL(20,10);
    DECLARE diff_lat, diff_lng, a, c, distance DECIMAL(20,10);
    SET rad_lat1 = RADIANS(lat1);
    SET rad_lng1 = RADIANS(lng1);
    SET rad_lat2 = RADIANS(lat2);
    SET rad_lng2 = RADIANS(lng2);
    SET diff_lat = rad_lat2 - rad_lat1;
    SET diff_lng = rad_lng2 - rad_lng1;
    SET a = SIN(diff_lat / 2) * SIN(diff_lat / 2)
          + COS(rad_lat1) * COS(rad_lat2)
          * SIN(diff_lng / 2) * SIN(diff_lng / 2);
    SET c = 2 * ATAN2(SQRT(a), SQRT(1 - a));
    -- km 保留2位小数
    SET distance = ROUND(earth_radius * c, 2);
    RETURN distance;
END$$

DELIMITER ;