错误日志如下
1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable), Time: 0.125000s
1、开启创建函数功能
-- 1、查看创建函数是否开启 如果Value处值为OFF,则需将其开启。
show variables like '%func%';
-- 2、开启MySQL函数功能
set global log_bin_trust_function_creators=1;
-- 3、关闭MySQL函数功能
set global log_bin_trust_function_creators=0;
-- 4、删除一个函数
drop function IF EXISTS getDistance;
-- 删除存储过程、存储函数主要使用drop语句:
-- DROP PROCEDURE IF EXISTS #{存储过程} —— 删除存储过程
-- DROP FUNCTION IF EXISTS #{函数} —— 删除存储函数
2、创建计算两个坐标之间距离的的函数
-- 第一种
CREATE FUNCTION getDistance(lng1 FLOAT,lat1 FLOAT,lng2 FLOAT,lat2 FLOAT) RETURNS DOUBLE
BEGIN
DECLARE result DOUBLE;
DECLARE radLng1 DOUBLE;
DECLARE radLat1 DOUBLE;
DECLARE radLng2 DOUBLE;
DECLARE radLat2 DOUBLE;
SET radLng1 = lng1 * pi() / 180.0;
SET radLat1 = lat1 * pi() / 180.0;
SET radLng2 = lng2 * pi() / 180.0;
SET radLat2 = lat2 * pi() / 180.0;
SET result = 2 * asin(sqrt(pow(sin((radLat1 - radLat2) / 2), 2) + cos(radLat1) * cos(radLat2) * pow(sin((radLng1 - radLng2) / 2), 2)));
SET result = result * 6378.137;
SET result = ROUND(result * 100) / 100;
RETURN result;
END
--第二种
DELIMITER $$
DROP FUNCTION IF EXISTS getDistance$$
CREATE FUNCTION getDistance(lng1 FLOAT,lat1 FLOAT,lng2 FLOAT,lat2 FLOAT) RETURNS DOUBLE
BEGIN
DECLARE result DOUBLE;
DECLARE radLng1 DOUBLE;
DECLARE radLat1 DOUBLE;
DECLARE radLng2 DOUBLE;
DECLARE radLat2 DOUBLE;
SET radLng1 = lng1 * PI() / 180.0;
SET radLat1 = lat1 * PI() / 180.0;
SET radLng2 = lng2 * PI() / 180.0;
SET radLat2 = lat2 * PI() / 180.0;
SET result = 2 * ASIN(SQRT(POW(SIN((radLat1 - radLat2) / 2), 2) + COS(radLat1) * COS(radLat2) * POW(SIN((radLng1 - radLng2) / 2), 2)));
SET result = result * 6378.137;
SET result = ROUND(result * 100) / 100;
RETURN result;
END $$
DELIMITER ;
3、使用测试
-- 上海市为例 唐镇地铁站到人民广场地铁站的直线距离 大概17.33公里 (参数坐标不区分先后顺序)
select getDistance(121.662702,31.220411,121.481923,31.238586);