MySQL存储过程更新单条预订时误改所有BookingSlot值求助

你的两个存储过程版本都存在同一个核心问题：输入参数名BookingID和bookings表的列名BookingID完全重复。

MySQL在解析WHERE BookingID = BookingID时，会把等式两边的BookingID都识别为存储过程的输入参数，而非左边是表列、右边是参数。这就导致这个条件永远为真（参数等于自身），所以UPDATE语句会修改表中所有行的BookingSlot值。

修正方案一：修改参数名避免冲突 #

给输入参数添加前缀（比如p_），明确区分参数和表列：

DELIMITER //

CREATE PROCEDURE UpdateBooking (
IN p_BookingID INT, 
IN NewSlotTime DATETIME
)
BEGIN 
    DECLARE ConfirmationMessage VARCHAR (255);
    DECLARE UpdatedBookingID INT;
   
    SET UpdatedBookingID = p_BookingID;
    
    UPDATE bookings
    SET BookingSlot = NewSlotTime
    WHERE BookingID = UpdatedBookingID;
    
    SET ConfirmationMessage = CONCAT('Booking ID ', UpdatedBookingID, ' has been updated');
    
    SELECT ConfirmationMessage as Confirmation;

END; 
//
DELIMITER ;

CALL UpdateBooking (37, '2023-09-14 10:45:00');

修正方案二：使用表别名明确指定列 #

通过给表加别名，在WHERE条件里明确标注列归属：

DELIMITER //

CREATE PROCEDURE UpdateBooking (
    IN BookingID INT, 
    IN NewSlotTime DATETIME
)
BEGIN 
    DECLARE ConfirmationMessage VARCHAR (255);
    
    UPDATE bookings b
    SET b.BookingSlot = NewSlotTime
    WHERE b.BookingID = BookingID;
    
    SET ConfirmationMessage = CONCAT('Booking ID ', BookingID, ' has been updated');
    
    SELECT ConfirmationMessage as Confirmation;

END; 
//
DELIMITER ;

CALL UpdateBooking (37, '2023-09-14 10:45:00');

两种方案都能让MySQL正确区分表列和输入参数，从而只更新指定BookingID的记录。

内容的提问来源于stack exchange，提问作者Theresa Parker