mysql - 分配变量时需要用表名引用表属性吗？

Question

在以下存储过程中，我执行一条语句:

select @subscriptionId := SubscriptionId from subscriptioninfo 
                               where subscriptioninfo.UserId = userId;

但是如果我将语句更改为

select @subscriptionId := SubscriptionId from subscriptioninfo 
                               where UserId = userId;

它返回多个结果，尽管它应该只返回单个结果。

当很明显正在引用名为 subscriptioninfo 的表时，subscriptioninfo.UserId 在这里创建什么区别。

DELIMITER $$

    DROP PROCEDURE IF EXISTS `portaldb`.`update_user_credit_limit`$$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `update_user_credit_limit`(userId int,planId int, newCreditLimit int)
    begin
            if newCreditLimit is not null then 
                /**
                 * Get the subscription id required to update the max credit allocated into the 
                 * planallocation table
                 * The following query creates a local variable @subscriptionId set to the user's
                 * subscription id
                 */
                select @subscriptionId := SubscriptionId from subscriptioninfo where subscriptioninfo.UserId = userId;
                if @subscriptionId is not null then
                    /**
                     * Now update the user's MaxCreditPulseAllocated attribute
                     */
                    update planallocation set MaxCreditPulseAllocated = newCreditLimit where
                        CurrentPlanId = planId and SubscriptionId = @subscriptionId;
                end if;
            end if;
        end$$

    DELIMITER ;

Answer 1

原因是因为你的变量名与表列相同(MySQL中变量名和列名不区分大小写)。当您编写不带表前缀的名称时，它会将其解释为变量，而不是列。因此，WHERE UserId = userId 始终为 true，因为您将变量与其自身进行比较，因此它会返回表中的所有列。

更改您的过程以对参数使用不同的名称，您就不会遇到此问题。

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_user_credit_limit`(p_userId int,planId int, newCreditLimit int)
begin
    if newCreditLimit is not null then 
        /**
         * Get the subscription id required to update the max credit allocated into the 
         * planallocation table
         * The following query creates a local variable @subscriptionId set to the user's
         * subscription id
         */
        select @subscriptionId := SubscriptionId from subscriptioninfo where UserId = p_userId;
        if @subscriptionId is not null then
            /**
             * Now update the user's MaxCreditPulseAllocated attribute
             */
            update planallocation set MaxCreditPulseAllocated = newCreditLimit where
                CurrentPlanId = planId and SubscriptionId = @subscriptionId;
        end if;
    end if;
end$$

顺便说一句，您不需要执行两个查询，您可以使用 JOIN 执行单个查询。

UPDATE planallocation AS p
JOIN subscriptioninfo AS s ON p.SubscriptionId = s.SubscriptionId
SET p.MaxCreditPulseAllocated = newCreditLimit
WHERE p.CurrentPlanId = planId
AND s.UserId = p_userId