数据库存储新年优惠活动

CREATE TABLE PromotionActivities (
    ActivityID INT PRIMARY KEY AUTO_INCREMENT,
    ActivityName VARCHAR(255) NOT NULL,
    StartTime DATETIME NOT NULL,
    EndTime DATETIME NOT NULL,
    DiscountRate DECIMAL(5, 2),
    ApplicableProducts TEXT,
    ActivityDescription TEXT,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO PromotionActivities (ActivityName, StartTime, EndTime, DiscountRate, ApplicableProducts, ActivityDescription)
VALUES ('新年大促', '2024-01-01 00:00:00', '2024-01-31 23:59:59', 0.10, '所有商品', '全场商品9折')
ON DUPLICATE KEY UPDATE ActivityName=VALUES(ActivityName), StartTime=VALUES(StartTime), EndTime=VALUES(EndTime), DiscountRate=VALUES(DiscountRate), ApplicableProducts=VALUES(ApplicableProducts), ActivityDescription=VALUES(ActivityDescription);

CREATE INDEX idx_activity_name ON PromotionActivities(ActivityName);
CREATE INDEX idx_start_end_time ON PromotionActivities(StartTime, EndTime);

START TRANSACTION;
-- 执行插入或更新操作
COMMIT;