从零搭建小型商城三级分销体系：数据库设计核心逻辑实操指南

一、环境准备

为保证零门槛复现，本次使用Node.js+Express4.x+MySQL8.0，依赖版本固定避免兼容性问题。

1.1 基础软件安装

• Node.js 18.17.0 LTS：下载地址 https://nodejs.org/dist/v18.17.0/node-v18.17.0-x64.msi（Windows）/ https://nodejs.org/dist/v18.17.0/node-v18.17.0.pkg（macOS）
• MySQL 8.0.35：下载地址 https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-installer-community-8.0.35.0.msi（Windows）/ 终端执行 `brew install mysql@8.0`（macOS）

1.2 项目初始化

新建文件夹 `mall-distribution`，进入后打开终端执行以下命令：

```bash 初始化npm项目，一路回车即可 npm init -y 安装核心依赖 npm install express@4.18.2 mysql2@3.6.5 dotenv@16.3.1 body-parser@1.20.2 cors@2.8.5 ```

二、数据库设计

本次仅涉及分销核心表，其他用户、订单、商品表简化为必要字段。

2.1 完整SQL建表脚本

打开MySQL Workbench/Navicat/终端，连接数据库后执行以下脚本（可直接复制）：

```sql -- 创建数据库 CREATE DATABASE IF NOT EXISTS mall_distribution DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE mall_distribution; -- 简化用户表 CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID', phone VARCHAR(11) NOT NULL UNIQUE COMMENT '手机号', parent_id INT UNSIGNED DEFAULT 0 COMMENT '上级分销员ID，0表示无上级', is_distributor TINYINT(1) DEFAULT 0 COMMENT '是否分销员：0否1是', created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间', INDEX idx_parent_id (parent_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表'; -- 简化订单表 CREATE TABLE IF NOT EXISTS orders ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID', user_id INT UNSIGNED NOT NULL COMMENT '下单用户ID', total_amount DECIMAL(10,2) NOT NULL COMMENT '订单总金额', order_status TINYINT(1) DEFAULT 0 COMMENT '订单状态：0待支付1已支付待结算2已结算', created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间', settled_at DATETIME DEFAULT NULL COMMENT '结算时间', INDEX idx_user_id (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表'; -- 分销佣金表 CREATE TABLE IF NOT EXISTS commissions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '佣金ID', order_id INT UNSIGNED NOT NULL COMMENT '关联订单ID', distributor_id INT UNSIGNED NOT NULL COMMENT '获得佣金的分销员ID', commission_level TINYINT(1) NOT NULL COMMENT '佣金层级：1一级2二级3三级', commission_rate DECIMAL(5,4) NOT NULL COMMENT '佣金比例（如0.15表示15%）', commission_amount DECIMAL(10,2) NOT NULL COMMENT '佣金金额', commission_status TINYINT(1) DEFAULT 0 COMMENT '佣金状态：0待结算1已到账2已退回', created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间', settled_at DATETIME DEFAULT NULL COMMENT '到账时间', INDEX idx_order_id (order_id), INDEX idx_distributor_id (distributor_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分销佣金表'; -- 佣金全局配置表 CREATE TABLE IF NOT EXISTS commission_config ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '配置ID', level1_rate DECIMAL(5,4) DEFAULT 0.15 NOT NULL COMMENT '一级佣金比例', level2_rate DECIMAL(5,4) DEFAULT 0.08 NOT NULL COMMENT '二级佣金比例', level3_rate DECIMAL(5,4) DEFAULT 0.03 NOT NULL COMMENT '三级佣金比例', updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='佣金全局配置表'; -- 插入默认佣金配置 INSERT INTO commission_config (level1_rate, level2_rate, level3_rate) VALUES (0.15, 0.08, 0.03); ```

三、核心逻辑开发

项目根目录下新建3个文件：`.env`、`db.js`、`app.js`。

3.1 数据库连接配置

新建`.env`文件（内容可直接复制，替换数据库密码）：

``` DB_HOST=localhost DB_PORT=3306 DB_USER=root DB_PASSWORD=你的MySQL密码 DB_NAME=mall_distribution PORT=3000 ```

新建`db.js`文件（内容可直接复制，使用连接池提高性能）：

```javascript const mysql = require('mysql2/promise'); require('dotenv').config(); const pool = mysql.createPool({ host: process.env.DB_HOST, port: process.env.DB_PORT, user: process.env.DB_USER, password: process.env.DB_PASSWORD, database: process.env.DB_NAME, waitForConnections: true, connectionLimit: 10, queueLimit: 0 }); module.exports = pool; ```

3.2 接口开发

新建`app.js`文件（内容可直接复制，包含4个核心接口）：

```javascript const express = require('express'); const bodyParser = require('body-parser'); const cors = require('cors'); require('dotenv').config(); const pool = require('./db'); const app = express(); app.use(bodyParser.json()); app.use(cors()); // 1. 用户注册并绑定上级分销员 app.post('/api/register', async (req, res) => { const { phone, parentId } = req.body; if (!/^1[3-9]\d{9}$/.test(phone)) { return res.status(400).json({ code: 400, msg: '手机号格式错误' }); } try { // 检查上级是否存在且是分销员 if (parentId && parentId !== 0) { const [parentRows] = await pool.query('SELECT is_distributor FROM users WHERE id = ?', [parentId]); if (parentRows.length === 0 || parentRows[0].is_distributor !== 1) { return res.status(400).json({ code: 400, msg: '上级分销员不存在或未激活' }); } } // 插入新用户 const [result] = await pool.query('INSERT INTO users (phone, parent_id) VALUES (?, ?)', [phone, parentId || 0]); res.status(200).json({ code: 200, msg: '注册成功', data: { userId: result.insertId } }); } catch (err) { if (err.code === 'ER_DUP_ENTRY') { return res.status(400).json({ code: 400, msg: '手机号已注册' }); } console.error(err); res.status(500).json({ code: 500, msg: '服务器错误' }); } }); // 2. 激活分销员 app.post('/api/activate-distributor', async (req, res) => { const { userId } = req.body; if (!userId) { return res.status(400).json({ code: 400, msg: '用户ID不能为空' }); } try { const [result] = await pool.query('UPDATE users SET is_distributor = 1 WHERE id = ?', [userId]); if (result.affectedRows === 0) { return res.status(400).json({ code: 400, msg: '用户不存在' }); } res.status(200).json({ code: 200, msg: '激活成功' }); } catch (err) { console.error(err); res.status(500).json({ code: 500, msg: '服务器错误' }); } }); // 3. 模拟支付成功并生成佣金 app.post('/api/pay-order', async (req, res) => { const { orderId } = req.body; if (!orderId) { return res.status(400).json({ code: 400, msg: '订单ID不能为空' }); } const connection = await pool.getConnection(); try { await connection.beginTransaction(); // 查询订单信息 const [orderRows] = await connection.query('SELECT FROM orders WHERE id = ? AND order_status = 0 FOR UPDATE', [orderId]); if (orderRows.length === 0) { await connection.rollback(); return res.status(400).json({ code: 400, msg: '订单不存在或已支付' }); } const order = orderRows[0]; // 更新订单状态为已支付待结算 await connection.query('UPDATE orders SET order_status = 1 WHERE id = ?', [orderId]); // 查询佣金配置 const [configRows] = await connection.query('SELECT FROM commission_config LIMIT 1'); const config = configRows[0]; // 查询下单用户的三级上级分销员 let currentParentId = order.user_id; const distributors = []; for (let i = 1; i <= 3; i++) { const [parentRows] = await connection.query('SELECT id, parent_id FROM users WHERE id = ?', [currentParentId]); if (parentRows.length === 0) break; currentParentId = parentRows[0].parent_id; if (currentParentId === 0) break; // 检查是否是分销员 const [checkRows] = await connection.query('SELECT id FROM users WHERE id = ? AND is_distributor = 1', [currentParentId]); if (checkRows.length === 0) break; distributors.push({ id: currentParentId, level: i }); } // 插入佣金记录 for (const dist of distributors) { let rate; switch (dist.level) { case 1: rate = config.level1_rate; break; case 2: rate = config.level2_rate; break; case 3: rate = config.level3_rate; break; } const amount = (order.total_amount rate).toFixed(2); await connection.query( 'INSERT INTO commissions (order_id, distributor_id, commission_level, commission_rate, commission_amount) VALUES (?, ?, ?, ?, ?)', [orderId, dist.id, dist.level, rate, amount] ); } await connection.commit(); res.status(200).json({ code: 200, msg: '支付成功，佣金已生成' }); } catch (err) { await connection.rollback(); console.error(err); res.status(500).json({ code: 500, msg: '服务器错误' }); } finally { connection.release(); } }); // 4. 模拟订单结算并发放佣金 app.post('/api/settle-order', async (req, res) => { const { orderId } = req.body; if (!orderId) { return res.status(400).json({ code: 400, msg: '订单ID不能为空' }); } const connection = await pool.getConnection(); try { await connection.beginTransaction(); // 查询订单信息 const [orderRows] = await connection.query('SELECT FROM orders WHERE id = ? AND order_status = 1 FOR UPDATE', [orderId]); if (orderRows.length === 0) { await connection.rollback(); return res.status(400).json({ code: 400, msg: '订单不存在或已结算' }); } // 更新订单状态为已结算 await connection.query('UPDATE orders SET order_status = 2, settled_at = NOW() WHERE id = ?', [orderId]); // 更新佣金状态为已到账 await connection.query('UPDATE commissions SET commission_status = 1, settled_at = NOW() WHERE order_id = ?', [orderId]); await connection.commit(); res.status(200).json({ code: 200, msg: '结算成功，佣金已到账' }); } catch (err) { await connection.rollback(); console.error(err); res.status(500).json({ code: 500, msg: '服务器错误' }); } finally { connection.release(); } }); app.listen(process.env.PORT, () => { console.log(`服务器已启动，端口：${process.env.PORT}`); }); ```

四、接口测试

使用Postman/ApiPost/浏览器插件测试，服务启动命令：`node app.js`。

• 注册接口：POST `http://localhost:3000/api/register`，Body选JSON：`{"phone":"13800138001","parentId":0}`
• 激活接口：POST `http://localhost:3000/api/activate-distributor`，Body选JSON：`{"userId":1}`
• 插入测试订单：手动在数据库执行 `INSERT INTO orders (user_id, total_amount) VALUES (1, 100.00);`
• 支付接口：POST `http://localhost:3000/api/pay-order`，Body选JSON：`{"orderId":1}`
• 结算接口：POST `http://localhost:3000/api/settle-order`，Body选JSON：`{"orderId":1}`

测试完成后可在数据库查看orders、commissions表的状态变化。