php - 对 PROCEDURE 结果运行查询的最有效方法

Question

感谢任何帮助。

我正在尝试对 MySQL PROCEDURE 生成的结果创建查询。最好，我想将这个 PROCEDURE 修改为 JOIN 并在 1 个查询中完成这一切，但从我一直在阅读的内容来看，这是不可能的，所以我寻找最有效的方法从同一数据库但不同的表中查询信息，并通过目标 ID 链接。

例如:查询地址表并抓取由ID链接的PROCEDURE的每个结果的每个地址

如果 PROCEDURE 返回 50 个不同的“目的地”或结果，是否有任何方法可以避免执行 50 个额外查询？我假设最有效的方法是创建另一个过程。

PROCEDURE 代码 - 此过程查询数据库并根据纬度和经度抓取点。数据由 MAXMIND 提供，可在此处找到:

https://www.maxmind.com/en/geolocation_landing

CREATE PROCEDURE `GEODIST`( IN userid int, IN dist int, IN olat float, IN olon float ) DETERMINISTIC READS SQL DATA

BEGIN
DECLARE mylon DOUBLE;
DECLARE mylat DOUBLE;
DECLARE lon1 FLOAT;
DECLARE lon2 FLOAT;
DECLARE lat1 FLOAT;
DECLARE lat2 FLOAT;

SET mylon = olon;
SET mylat = olat;
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.id, destination.latitude, destination.longitude, 3956 * 2 * ASIN(SQRT( POWER(SIN((origin.latitude -destination.latitude) * pi()/180 / 2), 2) +COS(origin.latitude * pi()/180) * COS(destination.latitude * pi()/180) *POWER(SIN((origin.longitude -destination.longitude) * pi()/180 / 2), 2) )) AS distance FROM locations destination, locations 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 50;
END

调用过程:

CALL GEODIST( $userid, $distance, $latitude, $longitude )

编辑

目前，我已使用评论部分的建议编辑了代码，但在尝试创建过程时收到了错误。我尝试对目的地和本例中的照片表使用别名，但它会引发错误:

错误:

#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 'd, locations origin JOIN photos p ON d.id = p.lid WHERE origin.id = userid AND ' at line 18


CREATE PROCEDURE `GEODIST`( IN userid int, IN dist int, IN olat float, IN olon float ) DETERMINISTIC READS SQL DATA

BEGIN
DECLARE mylon DOUBLE;
DECLARE mylat DOUBLE;
DECLARE lon1 FLOAT;
DECLARE lon2 FLOAT;
DECLARE lat1 FLOAT;
DECLARE lat2 FLOAT;

SET mylon = olon;
SET mylat = olat;
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 d.id, d.name, d.address, d.latitude, d.longitude, d.category, d.type, p.lid, p.pid, 3956 * 2 * ASIN(SQRT( POWER(SIN((origin.latitude -d.latitude) * pi()/180 / 2), 2) +COS(origin.latitude * pi()/180) * COS(d.latitude * pi()/180) *POWER(SIN((origin.longitude -d.longitude) * pi()/180 / 2), 2) )) AS distance FROM locations d, locations origin JOIN photos p ON d.id = p.lid WHERE origin.id = userid
AND d.longitude BETWEEN lon1 AND lon2 AND d.latitude BETWEEN lat1 and lat2 
HAVING distance < dist ORDER BY distance LIMIT 50;
END

Answer 1

如果我正确理解你的问题，你不能直接加入地址表吗？

SELECT destination.id, destination.latitude, destination.longitude, 
3956 * 2 * ASIN(SQRT( POWER(SIN((origin.latitude -destination.latitude) * pi()/180 / 2), 2) +COS(origin.latitude * pi()/180) * COS(destination.latitude * pi()/180) *POWER(SIN((origin.longitude -destination.longitude) * pi()/180 / 2), 2) )) 
AS distance 
FROM 
locations destination, 
locations origin,
LEFT JOIN
addressTable
ON addressTable.id = destination.id
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 50;

没有数据很难进行测试，但我相信是这样的。

为作者编辑编辑

在新代码中替换以下内容:

FROM locations d, locations origin 
JOIN photos p ON d.id = p.lid 

您从中提取的表的适当名称应该如下所示:

FROM destination d, origin o,
JOIN photos p
ON d.id = p.lid

当我们使用别名时，我们只是说从表“destination”中选择一些内容，但让 nick 将其命名为“d”，这样我们就没有很长的 sql 语句。

此外，在您想要的选择中，请使用与 FROM 子句中使用的相同的别名。例如:

SELECT destination.id, destination.latitude

应该是

SELECT d.id, d.latitude