MySQL游标循环查找最小值

Question

这是我试图完成的任务：

为给定位置查找最近的可用车辆

我有一个vehicle和location的表，如下所示：

CREATE TABLE location
(location_id numeric(8,0) UNIQUE NOT NULL,
address varchar(100), 
latitude float,
longitude float,
PRIMARY KEY(location_id)
);
CREATE TABLE vehicle
(license_plate char(6) UNIQUE NOT NULL,
make varchar(30) NOT NULL,
model varchar(30) NOT NULL,
year numeric(4,0) NOT NULL CHECK(year>1990),
state char(2) NOT NULL, 
capacity int NOT NULL,
last_location numeric(8,0) DEFAULT NULL,
FOREIGN KEY(last_location) REFERENCES location(location_id) ON DELETE 
CASCADE ON UPDATE CASCADE,
PRIMARY KEY(license_plate)
);

我写了一个查询，调用一个函数来循环遍历vehicle表，计算与给定位置的距离，并返回具有最小距离的汽车的license_plate。

SELECT @locationA := 11111111;
SET @loc_lat = (SELECT latitude FROM location WHERE location_id = 
@locationA);
SET @loc_long = (SELECT longitude FROM location WHERE location_id = 
@locationA);
SELECT license_plate, make, model FROM vehicle
WHERE license_plate = find_car(@loc_lat, @loc_long); 
DELIMITER $$
CREATE FUNCTION find_car(loc_lat float, loc_long float) RETURNS char  
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DECLARE CUR_DIST float DEFAULT 1000000000; 
DECLARE car_lat NUMERIC; 
DECLARE car_long NUMERIC; 
DECLARE dist float; 
DECLARE closest_car char(6); 
DECLARE car_temp char(6);
DECLARE loc_temp numeric; 
DECLARE car_cursor CURSOR FOR SELECT license_plate, last_location FROM 
vehicle;
OPEN car_cursor; 
car_loop: LOOP
FETCH car_cursor INTO car_temp, loc_temp;
SET car_lat = (SELECT latitude FROM location WHERE location_id = 
loc_temp);
SET car_long = (SELECT longitude FROM location WHERE location_id = 
loc_temp);
SET dist = (SELECT ST_Distance_Sphere(
point(loc_lat, loc_long),
point(car_lat, car_long)
) * .000621371192);
IF dist < CUR_DIST THEN 
SET closest_car = car_temp;
SET CUR_DIST = dist; 
END IF; 
END LOOP;
CLOSE car_cursor; 
RETURN(closest_car);
END $$ 
DELIMITER ;

现在，这没有任何回报，我不太清楚为什么。我还是SQL的新手，所以提前谢谢！

Answer 1

我的建议是首先尝试一种基于集合的方法，看看它的性能如何。

在这里找到了一些关于原因的好见解。

一般策略：对于每个location_id，找到下一个最接近的location_id

一些具体策略：

• 使用jpgunter的GETDISTANCE函数(代码如下(
• 单击此处查看上下文中的代码
• 使用到location表的自联接来计算所有location_id值之间的距离
• 确定一个"太远"的任意距离，并排除该距离之外的所有距离
• 这有助于提高性能
• 从结果中选取最小距离

这是location表的自联接和"太远"标准的启动脚本。。。

SELECT l1.location_id as l1_location_id
,l1.latitude as l1_latitude
,l1.longitude as l1_longitude
,l2.location_id as l2_location_id
,l2.latitude as l2_latitude
,l2.longitude as l2_longitude
,GETDISTANCE(l1.latitude, l1.longitude, l2.latitude, l2.longitude) as l1_12_distance
FROM location AS l1
JOIN location AS l2 ON l1.location_id <> l2.location_id
WHERE GETDISTANCE(l1.latitude, l1.longitude, l2.latitude, l2.longitude) <= 1000; -- JJAUSSI: arbitrary "too far"

这是jpgunter的GETDISTANCE函数。。。

DELIMITER $$
/*
Takes two latitudes and longitudes in degrees. You could comment out the conversion if you want to pass as radians.
Calculate the distance in miles, change the radius to the earth's radius in km to get km.
*/
DROP FUNCTION IF EXISTS GETDISTANCE$$
CREATE FUNCTION GETDISTANCE 
(deg_lat1 FLOAT, deg_lng1 FLOAT, deg_lat2 FLOAT, deg_lng2 FLOAT) 
RETURNS FLOAT 
DETERMINISTIC 
BEGIN 
DECLARE distance FLOAT;
DECLARE delta_lat FLOAT; 
DECLARE delta_lng FLOAT; 
DECLARE lat1 FLOAT; 
DECLARE lat2 FLOAT;
DECLARE a FLOAT;
SET distance = 0;
/*Convert degrees to radians and get the variables I need.*/
SET delta_lat = radians(deg_lat2 - deg_lat1); 
SET delta_lng = radians(deg_lng2 - deg_lng1); 
SET lat1 = radians(deg_lat1); 
SET lat2 = radians(deg_lat2); 
/*Formula found here: http://www.movable-type.co.uk/scripts/latlong.html*/
SET a = sin(delta_lat/2.0) * sin(delta_lat/2.0) + sin(delta_lng/2.0) * sin(delta_lng/2.0) * cos(lat1) * cos(lat2); 
SET distance = 3956.6 * 2 * atan2(sqrt(a),  sqrt(1-a)); 
RETURN distance;
END$$
DELIMITER ;

您可能需要考虑修改字段名称year，因为它是一个保留字。

您可能会发现，如果/当您的数据库增长时，这些名称过于笼统：

• location
• address
• latitude
• longitude

但是，我不知道你的数据库。可能是您的表和字段名称非常适合您的需要。希望这能有所帮助！