112 lines
2.5 KiB
SQL
112 lines
2.5 KiB
SQL
-- ============================================
|
||
-- 同步产品表数据到代理产品配置表
|
||
-- 说明：为现有产品创建对应的代理产品配置记录
|
||
-- 执行时间：2025-01-XX
|
||
-- ============================================
|
||
|
||
-- 方式1：使用 INSERT IGNORE（如果记录已存在则忽略）
|
||
-- 注意：product_name 字段已移除，改为通过 product_id 关联查询 product 表获取
|
||
INSERT IGNORE INTO
|
||
`agent_product_config` (
|
||
`product_id`,
|
||
`base_price`,
|
||
`system_max_price`,
|
||
`price_threshold`,
|
||
`price_fee_rate`,
|
||
`del_state`,
|
||
`version`
|
||
)
|
||
VALUES (
|
||
1,
|
||
0.00,
|
||
9999.99,
|
||
NULL,
|
||
NULL,
|
||
0,
|
||
0
|
||
),
|
||
(
|
||
2,
|
||
0.00,
|
||
9999.99,
|
||
NULL,
|
||
NULL,
|
||
0,
|
||
0
|
||
),
|
||
(
|
||
3,
|
||
0.00,
|
||
9999.99,
|
||
NULL,
|
||
NULL,
|
||
0,
|
||
0
|
||
),
|
||
(
|
||
4,
|
||
0.00,
|
||
9999.99,
|
||
NULL,
|
||
NULL,
|
||
0,
|
||
0
|
||
),
|
||
(
|
||
5,
|
||
0.00,
|
||
9999.99,
|
||
NULL,
|
||
NULL,
|
||
0,
|
||
0
|
||
),
|
||
(
|
||
6,
|
||
0.00,
|
||
9999.99,
|
||
NULL,
|
||
NULL,
|
||
0,
|
||
0
|
||
),
|
||
(
|
||
7,
|
||
0.00,
|
||
9999.99,
|
||
NULL,
|
||
NULL,
|
||
0,
|
||
0
|
||
);
|
||
|
||
-- ============================================
|
||
-- 方式2：使用 INSERT ... ON DUPLICATE KEY UPDATE（如果记录已存在则忽略）
|
||
-- 注意：product_name 字段已移除，不再需要更新
|
||
-- ============================================
|
||
/*
|
||
INSERT INTO `agent_product_config`
|
||
(`product_id`, `base_price`, `system_max_price`, `price_threshold`, `price_fee_rate`, `del_state`, `version`)
|
||
VALUES
|
||
(1, 0.00, 9999.99, NULL, NULL, 0, 0),
|
||
(2, 0.00, 9999.99, NULL, NULL, 0, 0),
|
||
(3, 0.00, 9999.99, NULL, NULL, 0, 0),
|
||
(4, 0.00, 9999.99, NULL, NULL, 0, 0),
|
||
(5, 0.00, 9999.99, NULL, NULL, 0, 0),
|
||
(6, 0.00, 9999.99, NULL, NULL, 0, 0),
|
||
(7, 0.00, 9999.99, NULL, NULL, 0, 0)
|
||
ON DUPLICATE KEY UPDATE
|
||
`product_id` = VALUES(`product_id`);
|
||
*/
|
||
|
||
-- ============================================
|
||
-- 验证查询：检查同步结果
|
||
-- ============================================
|
||
SELECT apc.id, apc.product_id, p.product_name, apc.base_price, apc.system_max_price, apc.price_threshold, apc.price_fee_rate, apc.del_state, apc.create_time
|
||
FROM
|
||
`agent_product_config` apc
|
||
LEFT JOIN `product` p ON apc.product_id = p.id
|
||
AND p.del_state = 0
|
||
WHERE
|
||
apc.del_state = 0
|
||
ORDER BY apc.product_id; |