mysql如何实现座位预订数据存储

CREATE TABLE seats (
  id INT AUTO_INCREMENT PRIMARY KEY,
  seat_number VARCHAR(10) NOT NULL,
  location VARCHAR(50), -- 如 A区第3排
  status ENUM('available', 'reserved') DEFAULT 'available',
  UNIQUE(seat_number, location)
);
<p>CREATE TABLE reservations (
id INT AUTO_INCREMENT PRIMARY KEY,
seat_id INT NOT NULL,
user_id INT NOT NULL,
booking_time DATETIME DEFAULT CURRENT_TIMESTAMP,
status ENUM('active', 'cancelled') DEFAULT 'active',
FOREIGN KEY (seat_id) REFERENCES seats(id),
UNIQUE(seat_id, status) -- 确保同一座位不能被多次有效预订
);</p>

START TRANSACTION;
<p>-- 检查并锁定座位（防并发）
SELECT id, status FROM seats 
WHERE id = ? AND status = 'available' FOR UPDATE;</p><p>-- 如果查到结果，继续处理
UPDATE seats SET status = 'reserved' WHERE id = ?;
INSERT INTO reservations (seat_id, user_id) VALUES (?, ?);</p><p>COMMIT;</p>

START TRANSACTION;
<p>UPDATE reservations SET status = 'cancelled' WHERE id = ? AND user_id = ?;
UPDATE seats SET status = 'available' WHERE id = (SELECT seat_id FROM reservations WHERE id = ?);</p><p>COMMIT;</p>

ALTER TABLE reservations ADD COLUMN event_session_id INT;
ALTER TABLE seats ADD COLUMN venue_id INT; -- 区分不同场馆