本地零售小程序怎么搭建,先给结论:先写清门店数量、商品销售单位(按份、按箱或按重量)和顾客如何交付——到店自提还是附近配送,再在模板化SaaS与自建或定制之间选路线;配置完成后用一笔真实订单把下单、支付、拣货、交付和退款走通,才算完成可上线验收。技术侧最容易被低估的是订单与库存的一致性:支付回调可能重复到达,取消与接单可能并发,如果只在页面按钮上做判断,就会出现订单显示已付、库存却扣了两次的情况。下面以一个常见故障为线索,给出表结构、核心SQL、异常反例与业务验收表。
单店便利店、生鲜店和社区超市通常是商家自营:商品、库存和交付都由自己门店负责,订单状态只需围绕门店备货与配送。同品牌直营多店仍是自营,但要额外确认商品、库存、会员、权限和收款是否按门店拆分。若业务是招募多个独立商家入驻、由平台抽成结算和统一配送,则属于平台型系统,订单模型需要增加商户维度、分账与结算,应按平台产品单独评估,不宜直接复用单店模型。
经营结构 | 订单模型要点 | 库存归属 | 结算方式 |
|---|---|---|---|
单店自营 | 订单表可不带商户字段,状态机围绕门店备货 | 单店库存 | 门店自己的收款账户 |
同品牌直营多店 | 订单增加门店字段,商品与库存按门店维度 | 按门店分批管理 | 可统一收款或分门店收款,需先确认 |
平台型多商户 | 订单增加商户与分账字段,需处理结算与退款归属 | 按商户隔离 | 平台抽成与分账,需单独的结算模型 |
路线选择上,标准零售流程(分类、规格、下单、拣货、自提交付)优先比较模板化SaaS,用现成后台配置完成;确有称重补差、多仓、批次、原有收银或ERP同步需求,再评估半定制或自建,并把接口、数据字段、失败重试与维护责任写进方案。
零售与餐饮的差别在商品单位。便利店整箱饮料和单瓶饮料要分开规格,生鲜店按份和按重量要标清计量方式,否则店员拣货与顾客预期会对不上。交付方式分到店自提和附近配送:自提要保留取货门店与取货信息,配送要保留地址、配送范围与费用,这两类在订单表里宜用 fulfillment_type 区分,并把必填信息用约束绑定。
库存时点也需要先定规则:下单锁定可售数量,支付确认转为已售,取消或超时释放锁定数量。生鲜现制、整箱批发和社区配送的履约节奏不同,锁定时点应写进业务规则和验收单,避免“支付成功但无法交付”。多仓、批次、称重补差和原收银同步属于需要演示确认的范围,不能由普通零售页面直接推定。
以下示例依赖 MySQL 8.0.16 及以上版本(CHECK 约束生效、JSON_TABLE 可用)。复现步骤:建库后依次执行三张核心表的 DDL、种子数据与三个存储过程,再按“正常输入输出”和“异常反例”逐项调用。示例为通用工程设计,不是某套现成SaaS的内部实现,输出为预期结果,需在本地测试库核对。
三个唯一键分别挡住不同重复:shop_order.order_no 防重复订单,payment_record.provider_txn_id 防同一笔支付重复入账,inventory_log(order_no, sku_id, change_type) 防同一订单同一商品重复锁定、释放或扣减。
CREATE TABLE product_sku (
sku_id BIGINT UNSIGNED PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
spec_name VARCHAR(100) NOT NULL,
sale_price DECIMAL(10,2) NOT NULL,
sale_status ENUM('ON_SALE','OFF_SALE') NOT NULL DEFAULT 'ON_SALE',
CONSTRAINT chk_sale_price CHECK (sale_price >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE sku_inventory (
sku_id BIGINT UNSIGNED PRIMARY KEY,
available_qty INT UNSIGNED NOT NULL,
locked_qty INT UNSIGNED NOT NULL DEFAULT 0,
version INT UNSIGNED NOT NULL DEFAULT 0,
CONSTRAINT fk_inv_sku FOREIGN KEY (sku_id) REFERENCES product_sku(sku_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE shop_order (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(32) NOT NULL,
buyer_id BIGINT UNSIGNED NOT NULL,
fulfillment_type ENUM('PICKUP','DELIVERY') NOT NULL,
pickup_store VARCHAR(64) NULL,
receiver_address VARCHAR(255) NULL,
status ENUM('CREATED','PAYING','PAID','ACCEPTED','FULFILLING','COMPLETED','CANCELLED','REFUNDING','REFUNDED') NOT NULL DEFAULT 'CREATED',
total_amount DECIMAL(10,2) NOT NULL,
paid_amount DECIMAL(10,2) NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
paid_at DATETIME NULL,
version INT UNSIGNED NOT NULL DEFAULT 0,
UNIQUE KEY uk_order_no (order_no),
CONSTRAINT chk_total_amount CHECK (total_amount >= 0),
CONSTRAINT chk_fulfillment CHECK (
(fulfillment_type = 'PICKUP' AND pickup_store IS NOT NULL)
OR (fulfillment_type = 'DELIVERY' AND receiver_address IS NOT NULL)
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE order_item (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(32) NOT NULL,
sku_id BIGINT UNSIGNED NOT NULL,
sku_name_snapshot VARCHAR(100) NOT NULL,
spec_snapshot VARCHAR(100) NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
quantity INT UNSIGNED NOT NULL,
UNIQUE KEY uk_order_sku (order_no, sku_id),
CONSTRAINT chk_item_qty CHECK (quantity > 0),
KEY idx_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE payment_record (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(32) NOT NULL,
provider_txn_id VARCHAR(64) NOT NULL,
paid_amount DECIMAL(10,2) NOT NULL,
status ENUM('SUCCESS','AMOUNT_MISMATCH','REFUNDED') NOT NULL DEFAULT 'SUCCESS',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_provider_txn (provider_txn_id),
KEY idx_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE inventory_log (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(32) NOT NULL,
sku_id BIGINT UNSIGNED NOT NULL,
change_type ENUM('LOCK','RELEASE','DEDUCT') NOT NULL,
quantity INT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_order_sku_type (order_no, sku_id, change_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;order_item 保存名称、规格和单价快照,菜单或商品改价不会改写历史订单。chk_fulfillment 约束把自提门店与配送地址的必填关系写进数据库,入口校验与约束保持一致。
INSERT INTO product_sku(sku_id,product_name,spec_name,sale_price,sale_status) VALUES
(2001,'整箱矿泉水','550ml*24瓶',34.00,'ON_SALE'),
(2002,'苹果','按份约1kg',12.00,'ON_SALE');
INSERT INTO sku_inventory(sku_id,available_qty,locked_qty) VALUES
(2001,10,0),
(2002,1,0);SKU2001 可售 10 件,SKU2002 可售 1 件,用来演示正常单与库存不足反例。
DELIMITER //
CREATE PROCEDURE place_order(
IN p_order_no VARCHAR(32),
IN p_buyer_id BIGINT UNSIGNED,
IN p_fulfillment VARCHAR(16),
IN p_pickup_store VARCHAR(64),
IN p_address VARCHAR(255),
IN p_items JSON
)
BEGIN
DECLARE v_total DECIMAL(10,2) DEFAULT 0;
DECLARE v_bad INT DEFAULT 0;
DECLARE v_lines INT DEFAULT 0;
DECLARE v_distinct INT DEFAULT 0;
DECLARE v_changed INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'ERROR' AS code, p_order_no AS order_no, '事务回滚' AS message;
END;
START TRANSACTION;
SELECT COUNT(*), COUNT(DISTINCT jt.sku_id) INTO v_lines, v_distinct
FROM JSON_TABLE(p_items, '$[*]' COLUMNS (
sku_id BIGINT PATH '$.skuId',
quantity INT PATH '$.quantity'
)) AS jt;
IF v_lines = 0 OR v_lines <> v_distinct THEN
ROLLBACK;
SELECT 'INVALID_ITEM' AS code, p_order_no AS order_no, '商品行为空或同一SKU重复' AS message;
ELSE
SELECT COUNT(*) INTO v_bad
FROM JSON_TABLE(p_items, '$[*]' COLUMNS (
sku_id BIGINT PATH '$.skuId',
quantity INT PATH '$.quantity'
)) AS jt
LEFT JOIN product_sku s ON s.sku_id = jt.sku_id
WHERE jt.quantity IS NULL OR jt.quantity <= 0
OR s.sku_id IS NULL OR s.sale_status <> 'ON_SALE';
IF v_bad > 0 THEN
ROLLBACK;
SELECT 'INVALID_ITEM' AS code, p_order_no AS order_no, '商品不存在、已下架或数量非法' AS message;
ELSE
SELECT COALESCE(SUM(s.sale_price * jt.quantity),0) INTO v_total
FROM JSON_TABLE(p_items, '$[*]' COLUMNS (
sku_id BIGINT PATH '$.skuId',
quantity INT PATH '$.quantity'
)) AS jt
JOIN product_sku s ON s.sku_id = jt.sku_id;
INSERT INTO shop_order(order_no,buyer_id,fulfillment_type,pickup_store,receiver_address,status,total_amount)
VALUES (p_order_no,p_buyer_id,p_fulfillment,p_pickup_store,p_address,'CREATED',v_total);
INSERT INTO order_item(order_no,sku_id,sku_name_snapshot,spec_snapshot,unit_price,quantity)
SELECT p_order_no, s.sku_id, s.product_name, s.spec_name, s.sale_price, jt.quantity
FROM JSON_TABLE(p_items, '$[*]' COLUMNS (
sku_id BIGINT PATH '$.skuId',
quantity INT PATH '$.quantity'
)) AS jt
JOIN product_sku s ON s.sku_id = jt.sku_id;
UPDATE sku_inventory i
JOIN JSON_TABLE(p_items, '$[*]' COLUMNS (
sku_id BIGINT PATH '$.skuId',
quantity INT PATH '$.quantity'
)) AS jt ON i.sku_id = jt.sku_id
SET i.available_qty = i.available_qty - jt.quantity,
i.locked_qty = i.locked_qty + jt.quantity,
i.version = i.version + 1
WHERE i.available_qty >= jt.quantity;
SET v_changed = ROW_COUNT();
IF v_changed <> v_lines THEN
ROLLBACK;
SELECT 'OUT_OF_STOCK' AS code, p_order_no AS order_no, '库存不足,订单已回滚' AS message;
ELSE
INSERT INTO inventory_log(order_no,sku_id,change_type,quantity)
SELECT p_order_no, i.sku_id, 'LOCK', jt.quantity
FROM sku_inventory i
JOIN JSON_TABLE(p_items, '$[*]' COLUMNS (
sku_id BIGINT PATH '$.skuId',
quantity INT PATH '$.quantity'
)) AS jt ON i.sku_id = jt.sku_id;
COMMIT;
SELECT 'CREATED' AS code, p_order_no AS order_no, '订单已创建,库存已锁定' AS message;
END IF;
END IF;
END IF;
END//
DELIMITER ;客户端只传 skuId 和数量,单价与总额由服务端从 product_sku 读取;UPDATE 把库存校验与扣减放在同一条语句,避免“先查后改”的并发窗口;ROW_COUNT() 与 JSON 行数不一致时回滚,防止部分锁定。
DELIMITER //
CREATE PROCEDURE confirm_payment(
IN p_order_no VARCHAR(32),
IN p_provider_txn VARCHAR(64),
IN p_paid_amount DECIMAL(10,2)
)
BEGIN
DECLARE v_inserted INT DEFAULT 0;
DECLARE v_status VARCHAR(16) DEFAULT NULL;
DECLARE v_total DECIMAL(10,2) DEFAULT 0;
DECLARE v_expected INT DEFAULT 0;
DECLARE v_changed INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'ERROR' AS code, p_order_no AS order_no, '事务回滚' AS message;
END;
START TRANSACTION;
INSERT IGNORE INTO payment_record(order_no,provider_txn_id,paid_amount,status)
VALUES (p_order_no,p_provider_txn,p_paid_amount,'SUCCESS');
SET v_inserted = ROW_COUNT();
IF v_inserted = 0 THEN
COMMIT;
SELECT 'DUPLICATE' AS code, p_order_no AS order_no, '重复回调,已忽略' AS message;
ELSE
SELECT status,total_amount INTO v_status,v_total
FROM shop_order WHERE order_no = p_order_no FOR UPDATE;
IF v_status IS NULL THEN
ROLLBACK;
SELECT 'ORDER_NOT_FOUND' AS code, p_order_no AS order_no, '订单不存在' AS message;
ELSEIF v_status NOT IN ('CREATED','PAYING') THEN
COMMIT;
SELECT 'STATE_CONFLICT' AS code, p_order_no AS order_no, '当前状态不允许支付确认' AS message;
ELSEIF v_total <> p_paid_amount THEN
UPDATE payment_record SET status = 'AMOUNT_MISMATCH' WHERE provider_txn_id = p_provider_txn;
COMMIT;
SELECT 'AMOUNT_MISMATCH' AS code, p_order_no AS order_no, '金额不一致,待人工核对' AS message;
ELSE
UPDATE shop_order
SET status = 'PAID', paid_amount = p_paid_amount, paid_at = NOW(), version = version + 1
WHERE order_no = p_order_no AND status IN ('CREATED','PAYING');
SELECT COUNT(*) INTO v_expected FROM order_item WHERE order_no = p_order_no;
UPDATE sku_inventory i
JOIN order_item oi ON i.sku_id = oi.sku_id
SET i.locked_qty = i.locked_qty - oi.quantity,
i.version = i.version + 1
WHERE oi.order_no = p_order_no AND i.locked_qty >= oi.quantity;
SET v_changed = ROW_COUNT();
IF v_changed <> v_expected THEN
ROLLBACK;
SELECT 'LOCK_MISMATCH' AS code, p_order_no AS order_no, '锁定库存与订单明细不一致' AS message;
ELSE
INSERT INTO inventory_log(order_no,sku_id,change_type,quantity)
SELECT p_order_no, sku_id, 'DEDUCT', quantity FROM order_item WHERE order_no = p_order_no;
COMMIT;
SELECT 'PAID' AS code, p_order_no AS order_no, '支付确认完成,锁定库存转为已售' AS message;
END IF;
END IF;
END IF;
END//
DELIMITER ;provider_txn_id 唯一键让重复回调在数据库层被挡住;随后对订单行加锁,校验状态与金额;最后把锁定库存转为已售并写日志。订单已取消或已完成时返回 STATE_CONFLICT,不重复扣库存。
DELIMITER //
CREATE PROCEDURE cancel_order(IN p_order_no VARCHAR(32))
BEGIN
DECLARE v_status VARCHAR(16) DEFAULT NULL;
DECLARE v_expected INT DEFAULT 0;
DECLARE v_changed INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'ERROR' AS code, p_order_no AS order_no, '事务回滚' AS message;
END;
START TRANSACTION;
SELECT status INTO v_status FROM shop_order WHERE order_no = p_order_no FOR UPDATE;
IF v_status IS NULL THEN
ROLLBACK;
SELECT 'ORDER_NOT_FOUND' AS code, p_order_no AS order_no, '订单不存在' AS message;
ELSEIF v_status NOT IN ('CREATED','PAYING') THEN
COMMIT;
SELECT 'STATE_CONFLICT' AS code, p_order_no AS order_no, '当前状态不允许取消' AS message;
ELSE
UPDATE shop_order SET status = 'CANCELLED', version = version + 1
WHERE order_no = p_order_no AND status IN ('CREATED','PAYING');
SELECT COUNT(*) INTO v_expected FROM order_item WHERE order_no = p_order_no;
UPDATE sku_inventory i
JOIN order_item oi ON i.sku_id = oi.sku_id
SET i.available_qty = i.available_qty + oi.quantity,
i.locked_qty = i.locked_qty - oi.quantity,
i.version = i.version + 1
WHERE oi.order_no = p_order_no AND i.locked_qty >= oi.quantity;
SET v_changed = ROW_COUNT();
IF v_changed <> v_expected THEN
ROLLBACK;
SELECT 'LOCK_MISMATCH' AS code, p_order_no AS order_no, '锁定库存与订单明细不一致' AS message;
ELSE
INSERT INTO inventory_log(order_no,sku_id,change_type,quantity)
SELECT p_order_no, sku_id, 'RELEASE', quantity FROM order_item WHERE order_no = p_order_no;
COMMIT;
SELECT 'CANCELLED' AS code, p_order_no AS order_no, '订单已取消,锁定库存已释放' AS message;
END IF;
END IF;
END//
DELIMITER ;取消只允许 CREATED、PAYING 状态;重复取消在状态检查处返回 STATE_CONFLICT,不会再次加库存。
使用同一组种子数据,按下表逐项调用。每个用例从种子数据重置后单独执行,避免状态互相影响。正常单先下单再支付;重复回调使用同一个 provider_txn_id;库存不足用 SKU2002 下单 2 件;重复取消对同一订单调用两次 cancel_order。
用例 | 输入 | 预期输出 | 库存与日志核对 |
|---|---|---|---|
正常首单 | SO101,SKU2001 数量 2,支付 WX101 金额 68.00 | 下单 CREATED;支付 PAID | available 10→8;locked 0→2→0;payment_record 1 条 SUCCESS;inventory_log 有 LOCK 与 DEDUCT 各 1 条 |
重复回调 | 对 SO101 再次提交 WX101 | DUPLICATE,订单仍 PAID | payment_record 仍 1 条;不新增 DEDUCT |
库存不足 | SO102,SKU2002 数量 2 | OUT_OF_STOCK,订单回滚 | available 仍 1;locked 仍 0;无 SO102 订单;无日志 |
取消后重复取消 | SO103,SKU2001 数量 1,取消两次 | 第一次 CANCELLED;第二次 STATE_CONFLICT | available 10→9→10;locked 0→1→0;RELEASE 日志仅 1 条 |
已支付后取消 | 对已支付 SO101 调用取消 | STATE_CONFLICT | available 保持 8;不写 RELEASE 日志 |
金额不一致 | SO104,SKU2001 数量 1,支付 WX102 金额 30.00,订单应付 34.00 | AMOUNT_MISMATCH | 订单仍 CREATED;payment_record 状态 AMOUNT_MISMATCH;available 9、locked 1;无 DEDUCT |
执行后可用三条 SQL 核对:
SELECT order_no,status,total_amount,paid_amount FROM shop_order WHERE order_no IN ('SO101','SO103','SO104');
SELECT sku_id,available_qty,locked_qty FROM sku_inventory WHERE sku_id IN (2001,2002);
SELECT order_no,provider_txn_id,status,paid_amount FROM payment_record ORDER BY id;
SELECT order_no,sku_id,change_type,quantity FROM inventory_log ORDER BY id;若结果不同,先查三个唯一键是否建对、存储过程是否在事务内、ROW_COUNT() 是否符合预期,再核对调用参数是否同一订单或同一支付流水。这里给出的是预期结果,不是生产环境实测。
技术表通过不等于门店能用。验收应把顾客端、商家端、库存与订单、票据与交付、异常与退款列成五列,无论采用模板化SaaS还是自建,每一列都要能追到同一个订单号。模板化SaaS由服务方维护后台逻辑,商家仍应要求演示重复回调、库存不足、取消与退款;自建或定制可直接以本文 DDL 与状态机作为开发基线。
验收列 | 顾客端看什么 | 商家端核对什么 | 技术记录 |
|---|---|---|---|
下单 | 商品、规格、数量、应付金额、自提门店或配送地址 | 同一订单的商品快照与金额 | shop_order、order_item、inventory_log 的 LOCK 记录 |
支付 | 支付结果与订单状态 | payment_record 与订单金额一致 | provider_txn_id 唯一键、订单 PAID、DEDUCT 日志 |
接单与拣货 | 接单状态、取货信息或配送地址 | 自提门店或配送地址完整 | chk_fulfillment 约束、状态转换记录 |
自提或配送交接 | 取货凭证或送达确认 | 首次交付记录与订单一致 | 业务单据号与 order_no 对应 |
异常与退款 | 取消原因、退款进度 | 原订单状态、退款金额与处理人 | STATE_CONFLICT 或 AMOUNT_MISMATCH 记录 |
零售重点看销售单位、可售数量、拣货、缺货联系和自提或配送交接;生鲜店先定按份或按重量的计量方式,缺货或替换先与顾客确认再处理原订单。把本店最常见的异常写进验收单,比只检查首页和商品列表更能判断链路是否可上线。
先定订单模型。页面可以后调,但商品单位、库存、支付、自提或配送与退款的关系一旦上线再改,历史订单和库存记录很难对齐。先用一笔订单把状态跑通,再回头装修页面。
不需要。模板化SaaS由服务方维护数据库和后台逻辑,商家要用同一组异常用例验收:重复支付回调、库存不足、取消、退款和票据。要求服务方在测试环境演示,而不是只看功能清单。
常见做法是下单锁定可售数量,支付确认转为已售,取消或超时释放。零售备货与生鲜称重的履约差异较大,具体时点要写进业务规则和验收单,避免支付成功但无法交付。
如果只查订单状态再更新,两个并发回调可能同时读到旧状态。把幂等落在 provider_txn_id 唯一键和带状态条件的 UPDATE 上,重复请求在数据库层被挡住,才不会重复扣库存或重复生成交付任务。
下一步,准备一笔本店最常见的订单,带上现有设备型号、交付规则和退款规则,在测试环境走完顾客下单、商家接单、拣货、自提或配送交接与售后,把订单号、金额、状态、小票和处理人留在同一张验收表里。正常单与异常单都通过后,再提交审核并培训接单人员。
作者:小艾普(上海逊柯计算机科技有限公司)。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。