MySQL无主键关联表基于ticket_id+code的批量插入更新方案咨询

刚好之前处理过类似的场景，你的核心需求是基于ticket_id和code的组合来识别重复数据，实现批量的插入或更新（也就是数据库里常说的UPSERT操作）。下面分步骤给你讲具体实现方式：

第一步：给表添加唯一约束 #

因为你的表没有主键，得先告诉数据库：ticket_id + code的组合是唯一的，不能重复。只有这样，后续的UPSERT操作才能触发更新逻辑。

MySQL语法 #

ALTER TABLE your_table_name ADD UNIQUE INDEX idx_ticket_code (ticket_id, code);

PostgreSQL语法 #

CREATE UNIQUE INDEX idx_ticket_code ON your_table_name (ticket_id, code);

SQL Server语法 #

CREATE UNIQUE NONCLUSTERED INDEX idx_ticket_code ON your_table_name (ticket_id, code);

第二步：批量执行UPSERT操作 #

不同数据库的UPSERT语法略有差异，我结合你给的示例数据分别写出来：

1. MySQL 用 INSERT ... ON DUPLICATE KEY UPDATE #

不管是插入新数据还是更新已有数据，都可以用这个语句：

插入示例数据的SQL

INSERT INTO your_table_name (ticket_id, type, value, code)
VALUES
('1', 'name', 'Ben', 'person_name'),
('1', 'phone', '0812', 'person_phone'),
('1', 'mail', 'ben@yours.com', 'person_mail'),
('2', 'name', 'Jesse', 'person_name'),
('2', 'phone', '8272', 'person_phone'),
('2', 'mail', 'jesse@mine.com', 'person_mail')
ON DUPLICATE KEY UPDATE
type = VALUES(type),
value = VALUES(value);

更新示例数据的SQL

把VALUES里的内容换成更新后的值就行，逻辑完全一样：

INSERT INTO your_table_name (ticket_id, type, value, code)
VALUES
('1', 'name', 'Joe', 'person_name'),
('1', 'phone', '9810', 'person_phone'),
('1', 'mail', 'joe@mine.com', 'person_mail'),
('2', 'name', 'Rose', 'person_name'),
('2', 'phone', '0992', 'person_phone'),
('2', 'mail', 'rose@yours.com', 'person_mail')
ON DUPLICATE KEY UPDATE
type = VALUES(type),
value = VALUES(value);

解释：当数据库检测到ticket_id+code的组合已经存在时，就会执行后面的UPDATE，用插入语句里的type和value覆盖原有值；如果不存在，就直接插入新数据。

2. PostgreSQL 用 INSERT ... ON CONFLICT #

PostgreSQL的语法是通过ON CONFLICT指定冲突的唯一键，然后执行更新：

INSERT INTO your_table_name (ticket_id, type, value, code)
VALUES
('1', 'name', 'Ben', 'person_name'),
('1', 'phone', '0812', 'person_phone'),
('1', 'mail', 'ben@yours.com', 'person_mail'),
('2', 'name', 'Jesse', 'person_name'),
('2', 'phone', '8272', 'person_phone'),
('2', 'mail', 'jesse@mine.com', 'person_mail')
ON CONFLICT (ticket_id, code) DO UPDATE SET
type = EXCLUDED.type,
value = EXCLUDED.value;

这里的EXCLUDED指的是原本要插入的那条冲突数据，用它的字段值来更新现有记录。

3. SQL Server 用 MERGE 语句 #

SQL Server用MERGE来实现UPSERT，逻辑是匹配源数据和目标表的记录，分别处理匹配和不匹配的情况：

MERGE INTO your_table_name AS target
USING (
    VALUES
    ('1', 'name', 'Ben', 'person_name'),
    ('1', 'phone', '0812', 'person_phone'),
    ('1', 'mail', 'ben@yours.com', 'person_mail'),
    ('2', 'name', 'Jesse', 'person_name'),
    ('2', 'phone', '8272', 'person_phone'),
    ('2', 'mail', 'jesse@mine.com', 'person_mail')
) AS source (ticket_id, type, value, code)
ON target.ticket_id = source.ticket_id AND target.code = source.code
WHEN MATCHED THEN
    UPDATE SET
        target.type = source.type,
        target.value = source.value
WHEN NOT MATCHED THEN
    INSERT (ticket_id, type, value, code)
    VALUES (source.ticket_id, source.type, source.value, source.code);

一些额外的注意点 #

• 唯一约束是前提：如果不加ticket_id+code的唯一约束，数据库没法识别什么是重复数据，UPSERT逻辑根本不会触发，只会一直插入新数据。
• ORM框架适配：如果是用MyBatis、Hibernate这类ORM框架，你可以用foreach标签拼接上面的批量SQL，或者用框架自带的批量UPSERT工具类，核心逻辑还是和上面的原生SQL一致。
• 批量数据量限制：如果批量数据特别大，要注意数据库的参数限制（比如MySQL的max_allowed_packet），避免因数据包过大报错，可以分批次执行。

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