如何通过SQL实现将特定两个值作为组同时插入两行数据？

表结构说明 #

-- data_type表：存储类型定义
id | type
---------
 1 | a
 2 | b
 3 | c

-- data表：存储各类型的统计数据
type_id | count
-------------------
 1      | 50
 2      | 100
 3      | 30

约束规则 #

类型a（id=1）和b（id=2）属于同一组，插入data表时必须同时存在，禁止以下操作：

• 只插入组内单个类型
• 将组内类型和其他类型（如c）混合插入

合法/不合法示例 #

-- 合法：同时插入a和b
INSERT INTO data (type_id, count) VALUES (1,50), (2,90);

-- 不合法：a和c混合插入
INSERT INTO data (type_id, count) VALUES (1,50), (3,100);

-- 不合法：仅插入a
INSERT INTO data (type_id, count) VALUES (1,50);

SQL层面的约束实现方案 #

第一步：给类型表添加分组标识 #

先给data_type表新增group_id字段，用来标记哪些类型属于同一组：

ALTER TABLE data_type ADD COLUMN group_id INT;
-- 将a和b归为组1，c单独为组2
UPDATE data_type SET group_id = 1 WHERE type IN ('a', 'b');
UPDATE data_type SET group_id = 2 WHERE type = 'c';

方案1：检查约束（仅适用于支持子查询的数据库，如PostgreSQL 12+） #

直接给data表添加检查约束，确保同一a_id下的插入记录满足分组要求：

ALTER TABLE data ADD CONSTRAINT chk_group_insert CHECK (
  NOT EXISTS (
    SELECT 1
    FROM data_type dt1
    JOIN data_type dt2 ON dt1.group_id = dt2.group_id AND dt1.id != dt2.id
    WHERE dt1.id = data.type_id
    AND NOT EXISTS (
      SELECT 1 FROM data d WHERE d.type_id = dt2.id AND d.a_id = data.a_id
    )
  )
);

方案2：触发器（通用所有关系型数据库） #

如果你的数据库不支持带复杂子查询的检查约束（比如MySQL），用触发器来实现校验：

MySQL版本

-- 创建触发器函数
DELIMITER //
CREATE TRIGGER trg_check_group_insert BEFORE INSERT ON data
FOR EACH ROW
BEGIN
  DECLARE group_total INT;
  DECLARE inserted_total INT;
  
  -- 获取当前类型所在组的成员总数
  SELECT COUNT(*) INTO group_total
  FROM data_type
  WHERE group_id = (SELECT group_id FROM data_type WHERE id = NEW.type_id);
  
  -- 获取当前a_id下已插入的该组成员数量（包括本次要插入的）
  SELECT COUNT(*) INTO inserted_total
  FROM data
  WHERE a_id = NEW.a_id
  AND type_id IN (SELECT id FROM data_type WHERE group_id = (SELECT group_id FROM data_type WHERE id = NEW.type_id))
  UNION ALL SELECT 1 WHERE NEW.type_id IN (SELECT id FROM data_type WHERE group_id = (SELECT group_id FROM data_type WHERE id = NEW.type_id));
  
  -- 校验：如果插入的成员数不等于组内总成员数，抛出错误
  IF inserted_total != group_total THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '必须插入同一组的所有类型，不能单独或混合插入';
  END IF;
END //
DELIMITER ;

PostgreSQL版本

-- 创建触发器函数
CREATE OR REPLACE FUNCTION check_group_insert()
RETURNS TRIGGER AS $$
DECLARE
  group_total INT;
  inserted_total INT;
BEGIN
  -- 获取当前类型所在组的成员总数
  SELECT COUNT(*) INTO group_total
  FROM data_type
  WHERE group_id = (SELECT group_id FROM data_type WHERE id = NEW.type_id);
  
  -- 获取当前a_id下已插入的该组成员数量（包括本次）
  SELECT COUNT(*) INTO inserted_total
  FROM data
  WHERE a_id = NEW.a_id
  AND type_id IN (SELECT id FROM data_type WHERE group_id = (SELECT group_id FROM data_type WHERE id = NEW.type_id))
  UNION ALL SELECT 1 WHERE NEW.type_id IN (SELECT id FROM data_type WHERE group_id = (SELECT group_id FROM data_type WHERE id = NEW.type_id));
  
  -- 校验不通过则抛出异常
  IF inserted_total != group_total THEN
    RAISE EXCEPTION '必须插入同一组的所有类型，不能单独或混合插入';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trg_check_group_insert
BEFORE INSERT ON data
FOR EACH ROW
EXECUTE FUNCTION check_group_insert();

修正后的DAO代码（避免SQL注入） #

原来的代码直接拼接SQL存在注入风险，改成参数化查询：

const createData = async(userId, a1, a2, typeIds, counts) => {
  // 插入a表并获取自增的a_id
  const [aResult] = await myDataSource.query(
    `INSERT INTO a (user_id, a1, a2) VALUES(?,?,?) RETURNING id`,
    [userId, a1, a2]
  );
  const aId = aResult[0].id;

  // 准备批量插入的参数
  const values = typeIds.map((typeId, index) => [aId, typeId, counts[index]]);
  // 根据数据库类型生成占位符：MySQL用(?,?,?)，PostgreSQL用($1,$2,$3)格式
  const placeholders = values.map(() => '(?,?,?)').join(',');

  // 执行批量插入
  const data = await myDataSource.query(
    `INSERT INTO data (a_id, type_id, count) VALUES ${placeholders}`,
    values.flat()
  );
  return data;
};

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