通过代码创建MySQL自定义函数,创建一个自定义的MySQL函数？

I've just started using MySQL with PHP and I'd like to know if it's possible to create a custom function. This code snippet should illustrate what I'm trying to do.

// a somewhat complicated formula not suitable to embed into the query

function Distance($latA, $lonA, $latB, $lonB)

{

// earth's radius

$radius = 3956;

$latA = deg2rad($latA);

$lonA = deg2rad($lonA);

$latB = deg2rad($latB);

$lonB = deg2rad($lonB);

// calculate deltas

$deltaLat = $latB - $latA;

$deltaLon = $lonB - $lonA;

// calculate Great Circle distance

$result = pow(sin($deltaLatitude / 2.0), 2) + (cos($latA) * cos($latB) * pow(sin($deltaLon / 2.0), 2));

$distance = $radius * 2 * atan2(sqrt($result), sqrt(1 - $result));

return $distance;

}

// how can I call Distance() in my query?

$query = "SELECT lat, lon FROM zipcodes WHERE Distance(lat, lon, 0, 0) < 20";

mysql_query($query);

Thanks in advance for any help!

解决方案

You an declare this MySQL function in your application, and it will remain in the database until the database server is restarted.

mysql_query("CREATE FUNCTION Distance(LAT_A INT, LON_A INT, LAT_B INT, LON_B INT, )

RETURNS INT

READS SQL DATA

DETERMINISTIC

BEGIN

DECLARE radius, deltaLat, deltaLon, result, distance BIGINT;

SET radius=3956;

SET deltaLat=LAT_B-LAT_A;

SET deltaLon=LON_B-LON_A;

SET result=POW(SIN(deltaLat/2), 2) + (COS(LAT_A) * COS(LAT_B) * POW(SIN(deltaLon/2.0), 2));

SET distance=radius * 2 * ATAN2(SQRT(result), SQRT(1 - result));

RETURN distance;

END");

This uses MySQL's mathematical functions. Offloading this processing to the database is fast and efficient (the data doesn't have to travel across the wire, and you're only returned the results you want).

Once you've declared this, you can use it like so:

$query = "SELECT lat, lon FROM zipcodes WHERE Distance(lat, lon, 0, 0) < 20";

mysql_query($query);

However if your database does restart, any functions or procedures declared previously are lost. It's possible to handle MySQL error 1305 (Function functionName does not exist) gracefully at the application level.

In your database error handler:

switch (mysql_errno()):

case 1305:

if (false === $database->_declareStoredProcedureFlag) {

if ($c = preg_match_all("/FUNCTION [a-zA-Z0-9]+\." .

"([a-zA-Z0-9_]*) does not exist/is",

mysql_error(), $matches)

) {

$storedFunctionName = $matches[1][0];

$database->_declareStoredProcedureFlag = true;

if (true === $database->declareStoredFunction($storedFunctionName)) {

$result = mysql_query($query);

}

}

}

break;

...