无唯一标识的销售数据插入MySQL时如何识别唯一记录？

我有一份商品销售数据，仅包含item（商品）、date_time（销售时间）、price（价格）三个字段，示例数据如下：

现在需要将这些数据导入MySQL数据库，但原始数据没有唯一的saleID标识。核心需求是：如果存在两件red shirt在7/19/22 18:48以20元售出的真实销售场景，要将这两笔记录都插入，同时避免导入重复的记录（比如误操作重复导入同一批数据）。

我曾尝试新增sales_before字段汇总此前销售记录ID来识别，示例表结构如下：

这个方法虽然能实现需求，但维护sales_before字段会带来冗余和更新成本，想了解更优的解决方案。

方案1：自增主键 + 分组序列字段（推荐） #

放弃维护sales_before这种冗余字段，改为给表添加自增主键sale_id，同时新增sale_sequence字段标记同一商品、同一时间、同一价格下的销售顺序，通过唯一约束确保不重复插入。

创建表结构 #

CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    item VARCHAR(100) NOT NULL,
    date_time DATETIME NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    sale_sequence INT NOT NULL,
    -- 唯一约束：确保同条件下的序列值唯一，避免重复插入
    UNIQUE KEY unique_sale_identifier (item, date_time, price, sale_sequence)
);

批量插入逻辑（MySQL 8.0+） #

假设原始数据先导入临时表temp_sales，用窗口函数生成序列值后插入正式表，同时处理重复：

INSERT INTO sales (item, date_time, price, sale_sequence)
SELECT 
    item,
    STR_TO_DATE(date_time, '%m/%d/%y %H:%i') AS date_time, -- 转换为标准DATETIME格式
    price,
    -- 按商品、时间、价格分组，生成从1开始的序列
    ROW_NUMBER() OVER (PARTITION BY item, date_time, price ORDER BY (SELECT NULL)) AS sale_sequence
FROM temp_sales
ON DUPLICATE KEY UPDATE sale_id = sale_id; -- 遇到重复记录时不执行任何操作

这种方案既保留了每笔销售的独立记录，又能通过唯一约束防止重复导入，同时没有冗余字段，维护成本低。

方案2：UUID主键 + 索引优化（适合无序列需求场景） #

如果不需要记录同一条件下的销售顺序，可以用UUID作为主键，每次插入自动生成唯一标识，同时给(item, date_time, price)加索引优化查询：

创建表结构 #

CREATE TABLE sales (
    sale_id CHAR(36) PRIMARY KEY DEFAULT UUID(),
    item VARCHAR(100) NOT NULL,
    date_time DATETIME NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    -- 索引优化同条件下的查询
    INDEX idx_item_datetime_price (item, date_time, price)
);

插入逻辑 #

直接插入数据即可，若要避免误导入重复的真实销售记录，可以先统计现有记录中同组的数量，对比待导入数据的数量后再插入，或者用临时表去重后批量插入。

方案3：聚合式计数表（仅适合销量统计场景） #

如果不需要保留每笔销售的独立记录，只需要统计销量，可以用聚合表，通过ON DUPLICATE KEY更新计数：

创建表结构 #

CREATE TABLE sales_summary (
    item VARCHAR(100) NOT NULL,
    date_time DATETIME NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    sales_count INT DEFAULT 1,
    PRIMARY KEY (item, date_time, price)
);

插入逻辑 #

INSERT INTO sales_summary (item, date_time, price)
VALUES ('red shirt', '2022-07-19 18:48:00', 20)
ON DUPLICATE KEY UPDATE sales_count = sales_count + 1;

这种方案适合只需要汇总数据的场景，但无法保留单条销售记录，不符合你要保留每笔真实销售的需求，仅作补充参考。

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