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 ;
2025-12-19
