MySQL视频租赁系统：如何实现视频不可重复租赁及状态跟踪咨询

Hey there! Let's work through your video rental system problem step by step—you're already off to a great start with your three tables, so we just need to tie in the rental logic and status tracking.

First, the key requirement is making sure a video can't be rented out if it's already checked out to someone else. There are two solid approaches here, each with tradeoffs:

Approach 1: Use Existing Rental Table Data (No New Fields Needed) #

This method relies on the return_date field in your Rental table to track if a video is currently rented. A video is "taken" if there's a record in Rental where idVideo matches the target video and return_date IS NULL (meaning it hasn't been returned yet).

How to Implement: #

When a customer tries to rent a video, first run this check:

SELECT COUNT(*) AS active_rentals 
FROM Rental 
WHERE idVideo = ? -- Replace with the video ID being rented
AND return_date IS NULL;

If active_rentals returns 1 or more, reject the rental request (the video is already out). If it returns 0, proceed to insert a new record into Rental.

Bonus: Database-Level Constraint (For Extra Safety) #

To avoid race conditions (e.g., two customers trying to rent the same video at the exact same time), add a partial unique index to the Rental table. This will make MySQL block any duplicate unreturned rentals automatically:

CREATE UNIQUE INDEX idx_unique_unreturned_video 
ON Rental (idVideo) 
WHERE return_date IS NULL;

Now, if someone tries to insert a second unreturned record for the same video, MySQL will throw an error—no need to rely solely on app-level checks!

Approach 2: Add a Status Field to the Video Table #

If you want a quick way to check a video's rental status without querying the Rental table every time, you can add an is_rented field to your Video table.

Step 1: Modify the Video Table #

ALTER TABLE Video 
ADD COLUMN is_rented TINYINT(1) NOT NULL DEFAULT 0;

• 0 = Available, 1 = Rented out

Step 2: Maintain the Status with Transactions #

To keep this field in sync with the Rental table, you must use transactions to ensure both the rental record and status update happen together (no partial changes!).

When Renting a Video:

START TRANSACTION;

-- Lock the video row to prevent concurrent updates
SELECT is_rented FROM Video WHERE idVideo = ? FOR UPDATE;

-- Only proceed if the video is available
INSERT INTO Rental (idCustomer, idVideo, rent_date, due_date)
VALUES (?, ?, NOW(), DATE_ADD(NOW(), INTERVAL 7 DAY)); -- Adjust due date as needed

UPDATE Video SET is_rented = 1 WHERE idVideo = ?;

COMMIT;

When Returning a Video:

START TRANSACTION;

UPDATE Rental SET return_date = NOW() WHERE idRental = ?;

UPDATE Video SET is_rented = 0 WHERE idVideo = ?;

COMMIT;

Pros & Cons of Each Approach #

I'd suggest starting with Approach 1 plus the partial unique index—it's the most reliable and avoids data redundancy. If you later find you need faster status checks (e.g., for a video catalog page showing availability), you can add the is_rented field and use triggers to auto-update it when Rental records are inserted or updated. For example:

Trigger to set is_rented when a rental is created:

DELIMITER //
CREATE TRIGGER after_rental_insert
AFTER INSERT ON Rental
FOR EACH ROW
BEGIN
  UPDATE Video SET is_rented = 1 WHERE idVideo = NEW.idVideo;
END //
DELIMITER ;

Trigger to reset is_rented when a video is returned:

DELIMITER //
CREATE TRIGGER after_rental_update
AFTER UPDATE ON Rental
FOR EACH ROW
BEGIN
  IF NEW.return_date IS NOT NULL AND OLD.return_date IS NULL THEN
    UPDATE Video SET is_rented = 0 WHERE idVideo = NEW.idVideo;
  END IF;
END //
DELIMITER ;

Triggers take care of maintaining the status automatically, so you don't have to remember to update it in your application code.

内容的提问来源于stack exchange，提问作者Devin Cavallero