Node.js操作MySQL：如何实现单订单关联多产品与单用户

你的原订单表设计确实存在问题——把订单、用户、产品直接绑定，导致一个订单只能对应一个产品，不符合实际业务逻辑。正确的做法是拆分出订单主表和订单详情表，通过一对多的关系实现单个订单关联多个产品。

1. 调整后的SQL表结构 #

核心表说明 #

• products 和 users 表保持原有结构不变
• orders 作为订单主表，存储订单的核心信息（仅关联用户）
• order_items 作为订单详情表，存储订单中的每个产品条目，关联订单和产品

CREATE TABLE IF NOT EXISTS products(
  id_product int not null AUTO_INCREMENT,
  name_product text unique,
  primary key(id_product)
);

CREATE TABLE IF NOT EXISTS users(
  id_user int not null AUTO_INCREMENT,
  name_user text,
  surname_user text,
  email_user text unique,
  primary key(id_user)
);

-- 订单主表：存储订单基础信息，一个订单对应一条记录
CREATE TABLE IF NOT EXISTS orders(
  id_order int not null AUTO_INCREMENT,
  number_order varchar(50) not null unique, -- 改用字符串存储订单号，避免数字溢出
  date_order timestamp default current_timestamp, -- 默认自动填充当前时间
  id_user int not null,
  primary key(id_order),
  FOREIGN KEY(id_user) REFERENCES users(id_user)
);

-- 订单详情表：存储订单中的产品明细，一个订单可对应多条记录
CREATE TABLE IF NOT EXISTS order_items(
  id_order_item int not null AUTO_INCREMENT,
  id_order int not null,
  id_product int not null,
  quantity int not null default 1, -- 可选：添加产品数量字段，满足多数量需求
  primary key(id_order_item),
  FOREIGN KEY(id_order) REFERENCES orders(id_order) ON DELETE CASCADE, -- 删除订单时自动删除关联明细
  FOREIGN KEY(id_product) REFERENCES products(id_product)
);

2. Node.js 实现订单创建（基于mysql2） #

第一步：安装依赖 #

npm install mysql2

第二步：实现创建订单的函数 #

使用数据库事务确保订单和明细数据的一致性（要么全部成功，要么全部回滚）：

const mysql = require('mysql2/promise');

// 数据库连接配置，替换为你的实际信息
const dbConfig = {
  host: 'localhost',
  user: 'your_db_user',
  password: 'your_db_password',
  database: 'your_db_name'
};

// 创建订单并批量添加产品
async function createOrder(userId, productList) {
  const connection = await mysql.createConnection(dbConfig);
  
  try {
    // 开启事务
    await connection.beginTransaction();

    // 1. 插入订单主表，生成订单号
    const [orderResult] = await connection.execute(
      'INSERT INTO orders (number_order, id_user) VALUES (?, ?)',
      [`ORD-${Date.now()}`, userId] // 简单生成唯一订单号
    );
    const orderId = orderResult.insertId;

    // 2. 批量插入订单明细
    const itemValues = productList.map(item => [orderId, item.id_product, item.quantity || 1]);
    await connection.query(
      'INSERT INTO order_items (id_order, id_product, quantity) VALUES ?',
      [itemValues]
    );

    // 提交事务
    await connection.commit();
    console.log(`订单创建成功，ID：${orderId}`);
    return orderId;
  } catch (err) {
    // 出错回滚事务
    await connection.rollback();
    console.error('订单创建失败：', err);
    throw err;
  } finally {
    await connection.end();
  }
}

// 调用示例：给ID为1的用户创建订单，包含2个产品1和1个产品2
createOrder(1, [
  { id_product: 1, quantity: 2 },
  { id_product: 2, quantity: 1 }
]);

3. 查询订单完整信息 #

通过JOIN语句可以一次性获取订单的用户信息和所有产品明细：

SELECT 
  o.number_order,
  o.date_order,
  u.name_user,
  u.surname_user,
  p.name_product,
  oi.quantity
FROM orders o
JOIN users u ON o.id_user = u.id_user
JOIN order_items oi ON o.id_order = oi.id_order
JOIN products p ON oi.id_product = p.id_product
WHERE o.id_order = ?;

对应的Node.js查询代码：

async function getOrderFullInfo(orderId) {
  const connection = await mysql.createConnection(dbConfig);
  try {
    const [rows] = await connection.execute(`
      SELECT 
        o.number_order,
        o.date_order,
        u.name_user,
        u.surname_user,
        p.name_product,
        oi.quantity
      FROM orders o
      JOIN users u ON o.id_user = u.id_user
      JOIN order_items oi ON o.id_order = oi.id_order
      JOIN products p ON oi.id_product = p.id_product
      WHERE o.id_order = ?;
    `, [orderId]);
    return rows;
  } catch (err) {
    console.error('查询订单失败：', err);
    throw err;
  } finally {
    await connection.end();
  }
}

// 调用示例
getOrderFullInfo(1).then(info => console.log('订单完整信息：', info));

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