如何插入数据并使用子查询获取关联表的shipping_detail_id完成插入

前提说明 #

涉及两张关联表，结构如下：

• customer表字段：first_name、last_name、phone、shipping_detail_id
• shipping_details表字段：shipping_detail_id、address

需要插入客户数据时，关联shipping_details中地址为fakeadressstreet对应的shipping_detail_id，分两种场景处理：

场景1：shipping_details已存在address = 'fakeadressstreet'的记录 #

直接用INSERT ... SELECT语法即可，查询到对应ID直接插入客户表：

INSERT INTO customer (first_name, last_name, phone, shipping_detail_id)
SELECT 'Jackson', 'mike', 12345, shipping_detail_id
FROM shipping_details
WHERE address = 'fakeadressstreet'
LIMIT 1;

注：加LIMIT 1是为了避免同一个地址对应多条物流ID时，误插入多条客户记录，仅需要插入1条客户数据时建议加上

场景2：shipping_details不存在对应地址的记录 #

需要先插入物流地址数据，再插入关联的客户数据，不同数据库的实现略有差异：

通用方案（适配所有数据库） #

1. 先插入物流地址记录：

INSERT INTO shipping_details (address) VALUES ('fakeadressstreet');

2. 再插入客户数据，用对应数据库的自增ID获取函数取刚插入的shipping_detail_id：

• MySQL 写法：

INSERT INTO customer (first_name, last_name, phone, shipping_detail_id)
VALUES ('Jackson', 'mike', 12345, 2245562);

• SQL Server 写法：

INSERT INTO customer (first_name, last_name, phone, shipping_detail_id)
VALUES ('Jackson', 'mike', 12345, SCOPE_IDENTITY());

PostgreSQL 一步式写法（支持CTE返回插入ID） #

WITH inserted_shipping AS (
    INSERT INTO shipping_details (address)
    VALUES ('fakeadressstreet')
    RETURNING shipping_detail_id
)
INSERT INTO customer (first_name, last_name, phone, shipping_detail_id)
SELECT 'Jackson', 'mike', 12345, shipping_detail_id
FROM inserted_shipping;

优化建议 #

可以给shipping_details表的address字段加唯一约束，避免同一个地址重复插入多条冗余记录。

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