MySQL:在 SELECT 语句中使用变量

Question

请看下面的声明

INSERT INTO Ongoing_Fees (currentDate, Gross_Fee, Insurance_Fee, Submit_Fee)
    SELECT current_timestamp, New.Gross_Fee,
           @InsuranceFee := (New.Gross_Fee)*(p.E_Fee/100),
           @SubmitFee := @InsuranceFee-(p.s_Fee/100)
    FROM Portfolio p
    WHERE p.idPortfolio = New.idPortfolio;

这里我使用了 2 个用户定义的变量，@InsuranceFee 和 @SubmitFee。如您所见，您可以通过从@InsuranceFee 中减去一个值来获得@SubmitFee。

现在，为了让它起作用，@InsuranceFee“必须”首先被评估。然而MySQL document下面说

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1; For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...; However, the order of evaluation for expressions involving user variables is undefined.

那么，在 SELECT 中使用变量的最佳方式是什么？

更新

可能首先使用单独的 SELECT 语句将值分配给变量更好？像下面这样的东西......

 SET   @InsuranceFee :=    New.Gross_Fee * ((SELECT E_Fee FROM Portfolio WHERE idPortfolio = New.idPortfolio)/100);

 SET   @SubmitFee := @InsuranceFee - ((SELECT s_FeeFROM Portfolio WHERE idPortfolio = New.idPortfolio)/100)

    INSERT INTO Ongoing_Fees (currentDate, Gross_Fee, Insurance_Fee, Submit_Fee)
           current_timestamp, New.Gross_Fee,
                   @InsuranceFee ,
                   @SubmitFee

Answer 1

User-defined variables 不应该像这样使用。正如我在评论中提到的，没有理由不把它全部写出来。但是，如果您只是喜欢临时语法，则始终可以使用子查询:

INSERT INTO Ongoing_Fees (currentDate, Gross_Fee, Insurance_Fee, Submit_Fee)
SELECT ct, gross_fee, InsuranceFee, InsuranceFee-SubmitFee
FROM (
    SELECT current_timestamp ct, 
       New.Gross_Fee,
       New.Gross_Fee*p.E_Fee/100 as InsuranceFee,
       p.s_Fee/100 as SubmitFee
    FROM Portfolio p
    WHERE p.idPortfolio = New.idPortfolio
) T