SQL数据库规范化后，如何向Cars表插入带关联ID的数据？

1. 前置准备 #

先将CSV格式的原始sales数据导入临时表（例如temp_sales），临时表需包含原始字段：品牌名、车型名、油耗、颜色名、国家名，方便与维度表做关联匹配。

2. 核心关联插入SQL #

使用INSERT INTO ... SELECT语句直接关联维度表获取对应ID，完成批量插入：

INSERT INTO Cars (brand_id, model_name, fuel_consumption, color_id, country_id)
SELECT 
    b.brand_id,
    ts.model_name,
    ts.fuel_consumption,
    c.color_id,
    cnt.country_id
FROM temp_sales ts
-- 关联维度表匹配ID
LEFT JOIN Brand b ON ts.brand_name = b.brand_name
LEFT JOIN Color c ON ts.color_name = c.color_name
LEFT JOIN Country cnt ON ts.country_name = cnt.country_name
-- 过滤维度表无匹配的无效数据（可选）
WHERE b.brand_id IS NOT NULL 
  AND c.color_id IS NOT NULL 
  AND cnt.country_id IS NOT NULL
-- 限制插入1000条数据
LIMIT 1000;

3. 关键注意事项 #

• 匹配规则优化：若维度表名称字段存在大小写、空格差异，需统一格式后匹配，例如用LOWER(ts.brand_name) = LOWER(b.brand_name)避免匹配失败。
• 数据校验：插入前单独执行SELECT部分语句，确认关联后的ID与数据是否正确，避免无效数据入库。
• 无匹配数据处理：若原始数据存在维度表未收录的品牌/颜色/国家，需先补全维度表数据，或用COALESCE设置默认ID（需符合业务规则）。
• 性能优化：1000条数据量级下普通INSERT...SELECT足够，若需更快速度，可调整数据库批量插入参数（如MySQL的bulk_insert_buffer_size）。

4. 直接读取CSV的替代方案（部分数据库支持） #

若数据库支持直接读取CSV文件（如PostgreSQL的COPY、MySQL的LOAD DATA INFILE），可结合CTE直接处理：

-- PostgreSQL示例：直接读取CSV并关联插入
WITH temp_sales AS (
    SELECT brand_name, model_name, fuel_consumption, color_name, country_name
    FROM '/path/to/your/sales.csv'
    DELIMITER ',' CSV HEADER
)
INSERT INTO Cars (brand_id, model_name, fuel_consumption, color_id, country_id)
SELECT b.brand_id, ts.model_name, ts.fuel_consumption, c.color_id, cnt.country_id
FROM temp_sales ts
JOIN Brand b ON ts.brand_name = b.brand_name
JOIN Color c ON ts.color_name = c.color_name
JOIN Country cnt ON ts.country_name = cnt.country_name
LIMIT 1000;

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