doushanlv5184 2012-09-24 18:42
浏览 151
已采纳

MySQL中的匿名函数?

I wrote a query for a client that used a stored procedures. Turns out they can't grant my user write access to the database, so I can't store my procedure. Can I turn my stored procedure into an anonymous function inside my query, as to keep the functionality without using a stored procedure?

$establishFunction = mysql_query("DROP FUNCTION IF EXISTS fn_distance_cosine;
CREATE FUNCTION fn_distance_cosine (
    latitude_1 DOUBLE,
    longitude_1 DOUBLE,
    latitude_2 DOUBLE,
    longitude_2 DOUBLE
)
RETURNS DOUBLE
DETERMINISTIC
SQL SECURITY INVOKER
RETURN ACOS(
      SIN(RADIANS(latitude_1)) * SIN(RADIANS(latitude_2))
      + COS(RADIANS(latitude_1)) * COS(RADIANS(latitude_2))
      * COS(RADIANS(longitude_2 - longitude_1))
    ) * 3956.547;
");

mysql_query("SET @input_lat :=" . $lat . ";");
mysql_query("SET @input_lon :=" . $lon . ";");

mysql_query("SET @max_latitude := @input_lat + DEGREES(50.0/3956.547);");
mysql_query("SET @min_latitude := @input_lat - DEGREES(50.0/3956.547);");
mysql_query("SET @max_longitude := @input_lon + DEGREES(50.0/3956.547/COS(RADIANS(@input_lat)));");
mysql_query("SET @min_longitude := @input_lon - DEGREES(50.0/3956.547/COS(RADIANS(@input_lat)));");

if($result = mysql_query("SELECT
      *,
      fn_distance_cosine(lat, lon, @input_lat, @input_lon) AS distance_in_miles,
      COUNT(*) AS store_number_count
    FROM stores
    JOIN items_stores
    ON stores.store_number=items_stores.store_number
    WHERE `lat` BETWEEN @min_latitude AND @max_latitude
      AND `lon` BETWEEN @min_longitude AND @max_longitude
    GROUP BY stores.store_code
    HAVING store_code_count > 1
    ORDER BY distance_in_miles
    LIMIT 10;
    ")){  
        while ($r = mysql_fetch_assoc($result)){
        $rows[] = $r;
    }
}else{
    //failed.
    echo (mysql_error ());
}

print json_encode($rows);
  • 写回答

3条回答 默认 最新

  • dongzhi4470 2012-09-24 18:48
    关注

    If you can't do stored procedures, you probably can't do functions either because they won't have the security rights for that either.

    Option #1 - Have their own DBA vet the SQL statements and then make the modification to the database to house the stored procedure.

    Option #2 - Your other option is to move that code out of the database and into the PHP. So yes, you can move it into the PHP but it won't be nearly as fast or efficient as having the database engine do it.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)