代码之家 › 专栏 › 技术社区 › MTK

在不带子查询的where子句中使用用户变量

mysql

MTK · 技术社区 · 6 年前

我有以下场景:

+-----------+
| my_column |
+-----------+
| A         |
| B         |
| C         |
| D         |
| E         |
+-----------+

DROP FUNCTION IF EXISTS my_function;
CREATE FUNCTION my_function(
    phrase VARCHAR(255), 
    column_value VARCHAR(255)
)
RETURNS FLOAT(20,10)
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
    IF(column_value = 'A') THEN RETURN 1.0000000000;
    ELSEIF(column_value = 'B') THEN RETURN 0.7500000000;
    ELSEIF(column_value = 'C') THEN RETURN 0.7500000000;
    ELSEIF(column_value = 'D') THEN RETURN 0.5000000000;
    ELSEIF(column_value = 'E') THEN RETURN 0.0000000000;
    END IF;
END;

以下是我的主存储过程:

DROP PROCEDURE IF EXISTS my_procedure;
CREATE PROCEDURE my_procedure(  
    IN phrase VARCHAR(255)
)
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
    SET @phrase = phrase;
    SET @query = "
        SELECT  
            my_column, 
            @score_var := my_function(?,my_column) as score, 
            @score_var
        FROM my_table
        ORDER BY score DESC;
    ";
    PREPARE stmt FROM @query;
    EXECUTE stmt USING @phrase; 
    DEALLOCATE PREPARE stmt;
END;

现在如果我调用我的程序

call my_procedure('anything');

+-----------+--------------+------------+
| my_column | score        | @score_var |
+-----------+--------------+------------+
| A         | 1.0000000000 |          1 |
| B         | 0.7500000000 |       0.75 |
| C         | 0.7500000000 |       0.75 |
| D         | 0.5000000000 |        0.5 |
| E         | 0.0000000000 |          0 |
+-----------+--------------+------------+

但如果我加上 WHERE @score_var > 0.5 my_procedure ,结果是:

+-----------+--------------+------------+
| my_column | score        | @score_var |
+-----------+--------------+------------+
| A         | 1.0000000000 |          1 |
| C         | 0.7500000000 |       0.75 |
| E         | 0.0000000000 |          0 |
+-----------+--------------+------------+

预期结果>0.5:

+-----------+--------------+------------+
| my_column | score        | @score_var |
+-----------+--------------+------------+
| A         | 1.0000000000 |          1 |
| B         | 0.7500000000 |       0.75 |
| C         | 0.7500000000 |       0.75 |
+-----------+--------------+------------+

我见过一些使用子查询的答案,但是 我的问题是:我可以不使用子查询吗?

也欢迎采取其他办法。

0 回复 | 直到 6 年前

Paul Spiegel 6 年前

在同一语句中读写用户变量时,行为记录为“未记录”。换句话说,除非您阅读并理解MySQL版本的源代码,否则结果是不可预测的。

不过,我认为你把事情搞复杂了。我看不出使用准备好的语句的原因,也看不到使用u user变量的原因。你的手术身体可能只是:

SELECT  
    my_column, 
    my_function(phrase, my_column) as score, 
FROM my_table
HAVING score > 0.5
ORDER BY score DESC

RETURN
    CASE column_value
        WHEN 'A' THEN 1.0000000000
        WHEN 'B' THEN 0.7500000000
        WHEN 'C' THEN 0.7500000000
        WHEN 'D' THEN 0.5000000000
        WHEN 'E' THEN 0.0000000000
    END