MySQL5存储过程语法正确吗？

Question

这是MySQL5.x存储过程的正确语法吗？

DELIMITER $$

CREATE PROCEDURE GetNearbyPhotogsByMapCenter(
  lat1 decimal (7,3),
  long1 decimal (7,3),
  range  numeric (15)
)
BEGIN
DECLARE  rangeFactor  decimal (7,6);
 SET  rangeFactor = 0.014457;
 select * from (
  SELECT B.cb_plug_lat, B.cb_plug_lng, B.cb_photostudio , B.city, B.State,
  B.country, B.website, B.cb_basesserved, B.phone,
  B.cb_isrc,B.cb_isavailableforsessions
   FROM  jos_comprofiler AS  B, jos_users as JU
  WHERE
  B.cb_plug_lat  BETWEEN  lat1-(range*rangeFactor)  AND
   lat1+(range*rangeFactor)
  AND  B.cb_plug_lng  BETWEEN  long1-(range*rangeFactor)  AND
   long1+(range*rangeFactor)
  AND  GetDistance(lat1,long1,B.cb_plug_lat,B.cb_plug_lng)  <= range
  AND B.approved = 1
  AND B.confirmed = 1
  AND B.user_id = JU.id
  ORDER BY B.cb_isrc desc) as D
  WHERE D.cb_isavailableforsessions = 'Yes' or  D.cb_isavailableforsessions is null;
END
$$

我正尝试使用phpMyAdmin上的SQL选项卡将此存储过程加载到我的MySQL数据库中。根据这篇文章，我确实在该选项卡底部将分隔符设置为$$：How do I write an SP in phpMyAdmin (MySQL)?

所以我很好奇为什么我一直收到这个错误：

#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 'lat1 decimal (7,3), long1 decimal (7,3), range numeric (15) BEGIN ' at line 2

Answer 1

不要将过程标识符括在引号中。此外，range是MySQL保留字(从5.1开始)，因此您需要在每次使用它时将其放在反引号(`)中，或者将其更改为非保留字。

下面是在我的MySQL 5.2服务器上注册过程的版本

CREATE PROCEDURE GetNearbyPhotogsByMapCenter(
  lat1 decimal (7,3),
  long1 decimal (7,3),
  `range`  numeric (15)
)
BEGIN
DECLARE  rangeFactor  decimal (7,6);
 SET  rangeFactor = 0.014457;
 select * from (
  SELECT B.cb_plug_lat, B.cb_plug_lng, B.cb_photostudio , B.city, B.State,
  B.country, B.website, B.cb_basesserved, B.phone,
  B.cb_isrc,B.cb_isavailableforsessions
   FROM  jos_comprofiler AS  B, jos_users as JU
  WHERE
  B.cb_plug_lat  BETWEEN  lat1-(`range`*rangeFactor)  AND
   lat1+(`range`*rangeFactor)
  AND  B.cb_plug_lng  BETWEEN  long1-(`range`*rangeFactor)  AND
   long1+(`range`*rangeFactor)
  AND  GetDistance(lat1,long1,B.cb_plug_lat,B.cb_plug_lng)  <= `range`
  AND B.approved = 1
  AND B.confirmed = 1
  AND B.user_id = JU.id
  ORDER BY B.cb_isrc desc) as D
  WHERE D.cb_isavailableforsessions = 'Yes' or  D.cb_isavailableforsessions is null;
END
$$