MySQL中如何为关联订单表生成订单内商品序号？

完全可以直接在MySQL端实现需求，无需依赖PHP，分两种场景处理：

一、查询时动态生成序号（无需修改表结构） #

如果只是需要在查询结果中展示该序号，不用新增物理字段，利用MySQL 8.0+支持的ROW_NUMBER()窗口函数即可实现。按订单ID分组，组内序号从1开始递增，不同订单重新计数。

示例SQL：

SELECT
  ROW_NUMBER() OVER (PARTITION BY o.order_id ORDER BY oi.item_id) AS `OrderItemNr.`,
  o.order_id AS `Order ID`,
  oi.other_data AS `otherData`
  -- 按需添加其他字段
FROM `order` o
JOIN `order_items` oi ON o.order_id = oi.order_id
ORDER BY o.order_id, `OrderItemNr.`;

• PARTITION BY o.order_id：按订单ID分组，每个订单单独计数
• ORDER BY oi.item_id：指定组内排序规则（可替换为商品创建时间等业务字段，保证序号顺序稳定）

二、持久化序号到表中（新增物理字段） #

若需要将序号永久存储在order_items表中，可按以下步骤操作：

1. 新增字段：

ALTER TABLE `order_items` ADD COLUMN `OrderItemNr` INT UNSIGNED NOT NULL;

2. 用窗口函数生成并更新序号：

UPDATE `order_items` oi
JOIN (
  SELECT
    item_id,
    ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY item_id) AS rn
  FROM `order_items`
) temp ON oi.item_id = temp.item_id
SET oi.OrderItemNr = temp.rn;

后续新增订单商品时，可通过查询当前订单的最大序号再加1，或创建触发器自动维护序号。

低版本MySQL兼容方案（8.0以下） #

如果使用MySQL 5.x版本，可通过用户变量模拟分组计数：

SELECT
  @row_num := CASE WHEN @current_order = o.order_id THEN @row_num + 1 ELSE 1 END AS `OrderItemNr.`,
  @current_order := o.order_id AS `Order ID`,
  oi.other_data AS `otherData`
FROM `order` o
JOIN `order_items` oi ON o.order_id = oi.order_id
CROSS JOIN (SELECT @row_num := 0, @current_order := NULL) vars
ORDER BY o.order_id, oi.item_id;

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