MySQL查询- 12个月重复付款

ALTER TABLE payments ADD UNIQUE (order_id);

SELECT * FROM payments WHERE order_id = 'your_order_id' AND payment_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH);

START TRANSACTION;
SELECT * FROM payments WHERE order_id = 'your_order_id' AND payment_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) FOR UPDATE;
-- 如果没有找到重复记录，则插入新的支付记录
INSERT INTO payments (order_id, amount, payment_date) VALUES ('your_order_id', 'amount', 'payment_date');
COMMIT;

CREATE TABLE payment_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(255),
    amount DECIMAL(10, 2),
    payment_date DATETIME,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);