CREATE DATABASE IF NOT EXISTS order_management;
USE order_management;
CREATE TABLE IF NOT EXISTS orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
order_date DATE NOT NULL
)ENGINE=InnoDB;
INSERT INTO orders(user_id, product_id, quantity, order_date) VALUES
(1001, 101, 1, '2025-01-05'),
(1001, 101, 1, '2025-01-05'),
(1001, 101, 1, '2025-01-05'),
(1001, 102, 1, '2025-01-05'),
(1001, 102, 1, '2025-01-05'),
(1001, 102, 1, '2025-01-05'),
(1001, 103, 1, '2025-01-05'),
(1001, 101, 1, '2025-01-05');
SET autocommit = 0;
START TRANSACTION;
UPDATE orders
SET quantity = quantity + 1
WHERE user_id = 1001;
SELECT COUNT(*) INTO @invalid_orders
FROM orders
WHERE user_id = 1001 AND quantity <= 0;
SELECT IF(@invalid_orders = 0, 'COMMIT', 'ROLLBACK') INTO @action;
PREPARE stmt FROM @action;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT IF(@invalid_orders = 0,
'事务提交成功:1001订单数量已增加',
'失败,一会滚'
) AS RESULT;
SET autocommit = 1;
SELECT user_id
FROM orders
WHERE quantity >= 3 AND order_date > '2024-01'
SELECT * FROM orders;