mysql8.0创建函数错误代码1418解决方法和原因

错误日志如下

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);