akmon/doc_mall/database/VARIABLE_CONFLICT_FIX_REPORT.md

变量冲突修复报告

问题描述

问题一：PL/pgSQL 变量名冲突

在 mock_data_insert.sql 脚本的订单生成部分，PL/pgSQL 块中的变量名 merchant_id 与表字段 p.merchant_id 发生了命名冲突，导致以下错误：

ERROR: 42702: column reference "merchant_id" is ambiguous
DETAIL: It could refer to either a PL/pgSQL variable or a table column.

问题二：订单商品价格为空

在订单商品生成部分，当商品没有对应的SKU时，product_rec.price 为 NULL，导致违反 NOT NULL 约束：

ERROR: 23502: null value in column "price" of relation "ml_order_items" violates not-null constraint

问题三：配送任务重复键冲突

在配送任务生成部分，同一个订单可能被多次分配配送任务，导致违反唯一约束：

ERROR: 23505: duplicate key value violates unique constraint "ml_delivery_tasks_order_id_key"
DETAIL: Key (order_id)=(329d742f-af8b-4e0e-b4c5-d16606d23758) already exists.

问题原因

原因一：作用域冲突

在 PostgreSQL 的 PL/pgSQL 中，当局部变量与表字段同名时，会出现作用域冲突。在这种情况下：

• 声明了局部变量 merchant_id UUID
• 在 SQL 查询中使用 WHERE p.merchant_id = merchant_id 时，PostgreSQL 无法明确区分是表字段还是变量

原因二：数据完整性问题

在商品-SKU关联查询中：

• 使用了 LEFT JOIN 连接商品和SKU表
• 当商品没有SKU时，SKU相关字段（如price, image_url）为 NULL
• 直接使用 s.price 导致插入NULL值，违反数据库约束

原因三：唯一约束冲突

在配送任务生成中：

• 使用了 CROSS JOIN 将订单与配送员进行笛卡尔积连接
• 随机条件 random() < 0.5 可能让同一订单匹配多个配送员
• 没有确保每个订单只生成一个配送任务

修复方案

修复一：变量重命名

将变量名从 merchant_id 改为 selected_merchant_id，确保变量名与表字段名不冲突。

修复前

DECLARE
    merchant_id UUID;
BEGIN
    SELECT user_id INTO merchant_id FROM temp_user_ids ...
    WHERE p.merchant_id = merchant_id  -- 冲突！

修复后

DECLARE
    selected_merchant_id UUID;
BEGIN
    SELECT user_id INTO selected_merchant_id FROM temp_user_ids ...
    WHERE p.merchant_id = selected_merchant_id  -- 清晰明确

修复二：价格字段空值处理

使用 COALESCE 函数确保价格字段不为空，优先使用SKU价格，如果没有则使用商品基础价格。

修复前

SELECT p.id as product_id, s.id as sku_id, p.name, s.price, s.image_url
FROM public.ml_products p
LEFT JOIN public.ml_product_skus s ON p.id = s.product_id
-- s.price 可能为 NULL

修复后

SELECT 
    p.id as product_id, 
    s.id as sku_id, 
    p.name, 
    COALESCE(s.price, p.base_price) as price, -- 空值处理
    COALESCE(s.image_url, p.main_image_url) as image_url -- 空值处理
FROM public.ml_products p
LEFT JOIN public.ml_product_skus s ON p.id = s.product_id

修复三：配送任务唯一性保证

使用 DISTINCT ON、NOT EXISTS 和 LIMIT 确保每个订单只创建一个配送任务。

修复前

SELECT o.id, d.id, ...
FROM public.ml_orders o
JOIN public.ml_delivery_drivers d ON random() < 0.5 -- 可能重复
WHERE o.shipping_status >= 2
  AND random() < 0.8;

修复后

SELECT DISTINCT ON (o.id) -- 确保每个订单唯一
    o.id, d.id, ...
FROM public.ml_orders o
CROSS JOIN public.ml_delivery_drivers d
WHERE o.shipping_status >= 2
  AND random() < 0.8
  AND NOT EXISTS ( -- 检查是否已有配送任务
      SELECT 1 FROM public.ml_delivery_tasks dt WHERE dt.order_id = o.id
  )
ORDER BY o.id, random() -- 随机选择配送员
LIMIT 50; -- 限制数量

修改详情

文件：mock_data_insert.sql

1. 变量声明部分 (第804行)

- merchant_id UUID;
+ selected_merchant_id UUID;

2. 变量赋值部分 (第819行)

- SELECT user_id INTO merchant_id FROM temp_user_ids 
+ SELECT user_id INTO selected_merchant_id FROM temp_user_ids 

3. 订单插入部分 (第833行)

- uuid_generate_v4(), order_no, customer_rec.user_id, merchant_id,
+ uuid_generate_v4(), order_no, customer_rec.user_id, selected_merchant_id,

4. 商品查询部分 (第871行)

- WHERE p.merchant_id = merchant_id
+ WHERE p.merchant_id = selected_merchant_id

5. 订单商品查询部分 (第866-885行)

-- 修复前
SELECT p.id as product_id, s.id as sku_id, p.name, s.price, s.image_url
FROM public.ml_products p
LEFT JOIN public.ml_product_skus s ON p.id = s.product_id

-- 修复后
SELECT 
    p.id as product_id, 
    s.id as sku_id, 
    p.name, 
    COALESCE(s.price, p.base_price) as price,
    COALESCE(s.image_url, p.main_image_url) as image_url
FROM public.ml_products p
LEFT JOIN public.ml_product_skus s ON p.id = s.product_id

6. 订单商品插入部分 (第886-895行)

-- 增加了局部变量声明和空值检查
DECLARE
    item_quantity INTEGER;
    item_price DECIMAL;
BEGIN
    item_quantity := FLOOR(1 + random() * 2)::INTEGER;
    item_price := product_rec.price;
    
    INSERT INTO public.ml_order_items (...)
    VALUES (
        order_id, product_rec.product_id, product_rec.sku_id, product_rec.name,
        item_price, item_quantity, item_price * item_quantity, product_rec.image_url
    );
END;

7. 配送任务生成部分 (第1150-1175行)

-- 修复前
SELECT o.id, d.id, ...
FROM public.ml_orders o
JOIN public.ml_delivery_drivers d ON random() < 0.5
WHERE o.shipping_status >= 2
  AND random() < 0.8;

-- 修复后
SELECT DISTINCT ON (o.id) o.id, d.id, ...
FROM public.ml_orders o
CROSS JOIN public.ml_delivery_drivers d
WHERE o.shipping_status >= 2
  AND random() < 0.8
  AND NOT EXISTS (
      SELECT 1 FROM public.ml_delivery_tasks dt WHERE dt.order_id = o.id
  )
ORDER BY o.id, random()
LIMIT 50;

验证方法

1. 执行修复后的脚本，确认不再出现变量冲突错误
2. 检查生成的订单数据，确认 merchant_id 字段正确关联到商家用户
3. 验证订单项能正确关联到对应商家的商品，且价格字段不为空
4. 确认订单商品的价格逻辑正确（优先使用SKU价格，否则使用基础价格）
5. 检查配送任务表，确认每个订单最多只有一个配送任务
6. 验证配送任务的订单ID没有重复

最佳实践建议

1. 变量命名规范：在 PL/pgSQL 中使用更具描述性的变量名，避免与表字段同名
2. 变量前缀：考虑为局部变量添加前缀，如 v_, l_, selected_ 等
3. 表字段引用：在复杂查询中明确使用表别名，如 p.merchant_id
4. 空值处理：在 LEFT JOIN 查询中，使用 COALESCE 处理可能的空值
5. 数据完整性：确保关键字段（如价格、数量）不为空，违反业务逻辑
6. 唯一约束处理：在生成关联数据时，使用 DISTINCT、NOT EXISTS 等确保唯一性
7. 批量插入控制：使用 LIMIT 控制批量插入的数据量，避免过度生成测试数据

状态

✅ 已修复 - 所有变量冲突、空值问题和唯一约束冲突已解决，脚本可正常执行