mysql - 在 MySQL 5.0 中创建 MySQL 空间过程

Question

我发现一个过程几乎完全符合我在此页面上寻找的内容:

http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

我对它进行了一些修改，以实际使用我的数据库，但每次我去创建该函数时，它都会引发错误。我假设我实际上无法在 MySQL 5.0 中创建一个过程，但我必须创建一个函数。我只需要一点帮助。我对创建 MySQL 程序非常陌生。

DELIMITER $$
DROP FUNCTION IF EXISTS `GEODIST` $$
CREATE FUNCTION `GEODIST`( mylat float, mylong float, dist int )
BEGIN
    DECLARE lon1 float; 
    DECLARE lon2 float;
    DECLARE lat1 float; 
    DECLARE lat2 float;

set lon1 = mylon - dist/abs(cos(radians(mylat))*69);
set lon2 = mylon + dist/abs(cos(radians(mylat))*69);
set lat1 = mylat - (dist/69);
set lat2 = mylat + (dist/69);

SELECT destination.*,3956 * 2 * ASIN(SQRT( POWER(SIN((orig.lat -dest.lat) * pi()/180 / 2), 2) +COS(orig.lat * pi()/180) * COS(dest.lat * pi()/180) *POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2) )) as distance FROM users destination, users origin WHERE origin.id=userid
and destination.longitude between lon1 and lon2 and destination.latitude between lat1 and lat2 
having distance < dist ORDER BY Distance limit 10;

END $$

当我运行此代码时，会引发错误:

Error SQL query: Documentation

CREATE FUNCTION GEODIST (

mylat FLOAT, mylong FLOAT, dist INT ) BEGIN DECLARE lon1 FLOAT;

DECLARE lon2 FLOAT;

DECLARE lat1 FLOAT;

DECLARE lat2 FLOAT;

SET lon1 = mylon - dist / ABS( COS( RADIANS( mylat ) ) *69 ) ;

SET lon2 = mylon + dist / ABS( COS( RADIANS( mylat ) ) *69 ) ;

SET lat1 = mylat - ( dist /69 ) ;

SET lat2 = mylat + ( dist /69 ) ;

SELECT destination . * , 3956 *2 * ASIN( SQRT( POWER( SIN( ( orig.lat - dest.lat ) * PI( ) /180 /2 ) , 2 ) + COS( orig.lat * PI( ) /180 ) * COS( dest.lat * PI( ) /180 ) * POWER( SIN( ( orig.lon - dest.lon ) * PI( ) /180 /2 ) , 2 ) ) ) AS distance FROM users destination, users origin WHERE origin.id = userid AND destination.longitude BETWEEN lon1 AND lon2 AND destination.latitude BETWEEN lat1 AND lat2 HAVING distance < dist ORDER BY Distance LIMIT 10 ;

END $$ MySQL said: Documentation

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 'BEGIN DECLARE lon1 float; DECLARE lon2 float; DECLARE lat1 float' at line 2

编辑:下面建议我将其更改为我所拥有的程序，但它抛出错误:

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 '' at line 3

Answer 1

如果不需要从存储例程返回值，那么您需要创建一个过程，只需将CREATE FUNCTION更改为CREATE PROCEDURE即可。

否则需要在函数体中添加RETURNS子句来指定结果数据类型，并在函数体中添加RETURN子句来设置结果值。

CREATE PROCEDURE and CREATE FUNCTION Syntax .