MySQL CASE 无法在存储过程中工作

Question

这很好用。如果我更改 @unit_type 变量。计算中会得出正确的单位类型。

set @unit_type = 'kilometers';
set @lat = 50;
set @lon = 100;
set @lat2 = 51;
set @lon2 = 101;

select 
(CASE @unit_type 
    WHEN 'feet' THEN 20000000
    WHEN 'miles' THEN 3696.1
    WHEN 'meters' THEN 6979100
    WHEN 'kilometers' THEN 6979
    END
* acos( cos( radians(@lat) ) 
    * cos( radians( @lat2 ) ) 
    * cos( radians( @lon2 ) 
    - radians(@lon) ) 
    + sin( radians(@lat) ) 
    * sin( radians( @lat2 ) ) 
) ) AS distance;

然而，事实并非如此。当我尝试运行这个存储过程时，它每次都会以公里为单位返回。如果我删除公里的情况，结果为空。

CREATE PROCEDURE `anotherTest`(IN `lat` BIGINT, IN `lon` BIGINT, IN `lat2` BIGINT, IN `lon2` BIGINT, IN `unit_type` TEXT)
        LANGUAGE SQL
        NOT DETERMINISTIC
        CONTAINS SQL
        SQL SECURITY DEFINER
        COMMENT ''
    BEGIN

    select 
    (CASE @unit_type 
        WHEN 'feet' THEN 20000000
        WHEN 'miles' THEN 3696.1
        WHEN 'meters' THEN 6979100
        WHEN 'kilometers' THEN 6979
        END
    * acos( cos( radians(@lat) ) 
        * cos( radians( @lat2 ) ) 
        * cos( radians( @lon2 ) 
        - radians(@lon) ) 
        + sin( radians(@lat) ) 
        * sin( radians( @lat2 ) ) 
    ) ) AS distance;
    END

我只是想了解这是为什么，以及如何更好地编写它来解决这个问题。

大家有什么想法吗？提前致谢。

更新:

解决方案是从我的过程的 SELECT 语句中删除 @。因为他们不需要访问参数。

CREATE PROCEDURE `anotherTest`(IN `lat` BIGINT, IN `lon` BIGINT, IN `lat2` BIGINT, IN `lon2` BIGINT, IN `unit_type` TEXT)
        LANGUAGE SQL
        NOT DETERMINISTIC
        CONTAINS SQL
        SQL SECURITY DEFINER
        COMMENT ''
    BEGIN

    select 
    (CASE unit_type 
        WHEN 'feet' THEN 20000000
        WHEN 'miles' THEN 3696.1
        WHEN 'meters' THEN 6979100
        WHEN 'kilometers' THEN 6979
        END
    * acos( cos( radians(lat) ) 
        * cos( radians( lat2 ) ) 
        * cos( radians( lon2 ) 
        - radians(lon) ) 
        + sin( radians(lat) ) 
        * sin( radians( lat2 ) ) 
    ) ) AS distance;
    END

Answer 1

@unit_type 是一个用户变量。我们在程序中看不到任何正在设置的地方。 (它可以在调用之前的 session 中设置；它将具有 session 中最后分配给它的任何值。)

我们看到传递给过程的参数名为unit_type。但我们在程序中没有看到任何对此的引用。

一个快速修复方法是在 SELECT 之前将 @unit_type 用户变量设置为参数中的值:

SET @unit_type = unit_type;

所有参数也存在同样的问题，也没有引用它们。

<小时/>

解决此问题的另一个选项是引用 SQL 中的过程参数，替换对 user_variables 的引用。 (看起来快速编辑只需删除 @ 字符即可。)

<小时/>

用户定义变量和过程变量之间存在显着差异。

User-defined variables http://dev.mysql.com/doc/refman/5.5/en/user-variables.html