SQLite中复合主键下按订单号自动生成行号的实现问题

1. 表结构定义 #

你原本的复合主键设置是正确的，注意不要给lineNumber加AUTOINCREMENT，SQLite 仅支持单字段INTEGER PRIMARY KEY使用该属性，复合主键下会触发语法错误，建表语句参考：

CREATE TABLE order_items (
    orderNumber TEXT NOT NULL,
    lineNumber INTEGER NOT NULL,
    SKU TEXT NOT NULL,
    etc TEXT,
    PRIMARY KEY (orderNumber, lineNumber)
);

2. 实现自动生成lineNumber的两种方案 #

方案一：单条INSERT语句直接计算（无需额外配置） #

不用分开执行查询和插入操作，直接用INSERT ... SELECT语法在单条SQL里完成行号计算，操作是原子性的，不会出现并发冲突：

-- 替换语句里的具体值为你要插入的实际数据即可
INSERT INTO order_items (orderNumber, lineNumber, SKU, etc)
SELECT 
    'abc123',
    COALESCE(MAX(lineNumber), 0) + 1,
    'xyz',
    'etc'
FROM order_items
WHERE orderNumber = 'abc123';

COALESCE(MAX(lineNumber), 0) + 1的逻辑是：如果当前订单没有任何行，最大行号为NULL，就取0加1得到1，否则取最大行号加1，完全符合你要的最小可用行号需求。

方案二：用触发器自动处理（更省心，插入时不用写额外逻辑） #

你可以创建一个BEFORE INSERT触发器，数据库会在插入数据时自动补全lineNumber的值，你插入时给lineNumber传NULL即可：

触发器创建语句

CREATE TRIGGER auto_fill_lineNumber
BEFORE INSERT ON order_items
FOR EACH ROW
WHEN NEW.lineNumber IS NULL
BEGIN
    SELECT COALESCE(MAX(lineNumber), 0) + 1 INTO NEW.lineNumber
    FROM order_items
    WHERE orderNumber = NEW.orderNumber;
END;

触发器使用示例

创建完触发器后，你可以直接用下面的语句插入数据，不用再手动计算行号：

INSERT INTO order_items (orderNumber, lineNumber, SKU, etc)
VALUES ('abc123', NULL, 'xyz', 'etc');

3. 批量插入场景处理 #

如果要一次性导入多条原始数据，可以先把待导入数据存入临时表，配合窗口函数一次性生成所有行号：

-- 临时表存储待导入的原始数据（仅包含orderNumber、SKU、etc）
CREATE TEMP TABLE temp_import (orderNumber TEXT, SKU TEXT, etc TEXT);
-- 往临时表插入所有待导入数据
INSERT INTO temp_import VALUES 
('abc123','xyz','etc'),
('def456','xyz','etc'),
('abc123','wxc','etc'),
('ghi645','wxc','etc'),
('abc123','hk4','etc'),
('def456','hk4','etc');

-- 批量导入到正式表，自动生成lineNumber
INSERT INTO order_items (orderNumber, lineNumber, SKU, etc)
SELECT 
    orderNumber,
    ROW_NUMBER() OVER (PARTITION BY orderNumber ORDER BY (SELECT 1)),
    SKU,
    etc
FROM temp_import;

执行后得到的结果和你给出的期望效果完全一致。

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