mysql 1064 存储过程_存储过程1064错误Mysql

我第一次使用其中的一个，我很确定这是有效的，因为我已经在Stackoverflow上找到了它，当我将它作为SQL命令运行时它失败了。这似乎是一个常见的问题，并尝试了几个不同的代码集我似乎得到同样的问题....

MySQL said:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

near '$$ CREATE FUNCTION GetDistance(coordinate1 VARCHAR(120),

coordinate2 VARCHAR(' at line 1

DROP FUNCTION IF EXISTS `GetDistance`$$

CREATE FUNCTION `GetDistance`(coordinate1 VARCHAR(120), coordinate2 VARCHAR(120))

RETURNS VARCHAR(120)

BEGIN

DECLARE pos_comma1, pos_comma2 INT;

DECLARE lon1, lon2, lat1, lat2, distance DECIMAL(18,12);

select locate(',', coordinate1) into pos_comma1;

select locate(',', coordinate1, pos_comma1+1) into pos_comma2;

select CAST(substring(coordinate1, 1, pos_comma1-1) as DECIMAL(18,12)) into lon1;

select CAST(substring(coordinate1, pos_comma1+1, pos_comma2-pos_comma1-1) as DECIMAL(18,12)) into lat1;

select locate(',', coordinate2) into pos_comma1;

select locate(',', coordinate2, pos_comma1+1) into pos_comma2;

select CAST(substring(coordinate2, 1, pos_comma1-1) as DECIMAL(18,12)) into lon2;

select CAST(substring(coordinate2, pos_comma1+1, pos_comma2-pos_comma1-1) as DECIMAL(18,12)) into lat2;

select ((ACOS(SIN(lat1 * PI() / 180) * SIN(lat2 * PI() / 180) + COS(lat1 * PI() / 180) * COS(lat2 * PI() / 180) * COS((lon1 - lon2) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) into distance;

RETURN distance;

END$$谢谢 - 人类

编辑

试了..........

DROP FUNCTION IF EXISTS `GetDistance`;

delimiter $$

CREATE FUNCTION `GetDistance`(coordinate1 VARCHAR(120), coordinate2 VARCHAR(120))

RETURNS VARCHAR(120)

BEGIN

DECLARE pos_comma1, pos_comma2 INT;

DECLARE lon1, lon2, lat1, lat2, distance DECIMAL(18,12);

select locate(',', coordinate1) into pos_comma1;

select locate(',', coordinate1, pos_comma1+1) into pos_comma2;

select CAST(substring(coordinate1, 1, pos_comma1-1) as DECIMAL(18,12)) into lon1;

select CAST(substring(coordinate1, pos_comma1+1, pos_comma2-pos_comma1-1) as DECIMAL(18,12)) into lat1;

select locate(',', coordinate2) into pos_comma1;

select locate(',', coordinate2, pos_comma1+1) into pos_comma2;

select CAST(substring(coordinate2, 1, pos_comma1-1) as DECIMAL(18,12)) into lon2;

select CAST(substring(coordinate2, pos_comma1+1, pos_comma2-pos_comma1-1) as DECIMAL(18,12)) into lat2;

select ((ACOS(SIN(lat1 * PI() / 180) * SIN(lat2 * PI() / 180) + COS(lat1 * PI() / 180) * COS(lat2 * PI() / 180) * COS((lon1 - lon2) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) into distance;

RETURN distance;

END;

$$和..........

DROP FUNCTION IF EXISTS `GetDistance`;

$$

CREATE FUNCTION `GetDistance`(coordinate1 VARCHAR(120), coordinate2 VARCHAR(120))

RETURNS VARCHAR(120)

BEGIN

DECLARE pos_comma1, pos_comma2 INT;

DECLARE lon1, lon2, lat1, lat2, distance DECIMAL(18,12);

select locate(',', coordinate1) into pos_comma1;

select locate(',', coordinate1, pos_comma1+1) into pos_comma2;

select CAST(substring(coordinate1, 1, pos_comma1-1) as DECIMAL(18,12)) into lon1;

select CAST(substring(coordinate1, pos_comma1+1, pos_comma2-pos_comma1-1) as DECIMAL(18,12)) into lat1;

select locate(',', coordinate2) into pos_comma1;

select locate(',', coordinate2, pos_comma1+1) into pos_comma2;

select CAST(substring(coordinate2, 1, pos_comma1-1) as DECIMAL(18,12)) into lon2;

select CAST(substring(coordinate2, pos_comma1+1, pos_comma2-pos_comma1-1) as DECIMAL(18,12)) into lat2;

select ((ACOS(SIN(lat1 * PI() / 180) * SIN(lat2 * PI() / 180) + COS(lat1 * PI() / 180) * COS(lat2 * PI() / 180) * COS((lon1 - lon2) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) into distance;

RETURN distance;

END;

$$都失败....

人族

编辑 - 找到这个工作脚本.... LINK：http://datamoil.blogspot.co.uk/2011/09/calculate-distance-between-two-points.html

CREATE FUNCTION distance_between (from_lat DECIMAL(6, 3), from_lng DECIMAL(6, 3), to_lat DECIMAL(6, 3), to_lng DECIMAL(6, 3)) RETURNS DECIMAL(11, 3)

RETURN 6371 * 2 * ATAN2(SQRT(POW(SIN(RADIANS(to_lat - from_lat)/2), 2) + POW(SIN(RADIANS(to_lng - from_lng)/2), 2) * COS(RADIANS(from_lat)) * COS(RADIANS(to_lat))), SQRT(1 - POW(SIN(RADIANS(to_lat - from_lat)/2), 2) + POW(SIN(RADIANS(to_lng - from_lng)/2), 2) * COS(RADIANS(from_lat)) * COS(RADIANS(to_lat))));