基于project_user_id实现SQLite中id列按用户自增的方法

表结构说明 #

• project_user_id：对应用户登录ID
• name：对应项目名称
• id：基于用户条目自增的列，对应项目ID

需求描述 #

现有一张表，id列未按用户分组实现独立自增。需要达成的效果是：针对不同的project_user_id（不同用户），id列从1开始单独递增，最终每个用户的项目ID按条目顺序依次从1开始计数。

实现方案（Flask + SQLite） #

SQLite本身没有内置的"分区自增"功能，可通过以下两种方式实现需求：

1. 批量更新现有数据 #

如果需要修正已存在的数据，可利用窗口函数ROW_NUMBER()按用户分组排序后更新id字段：

UPDATE your_table_name
SET id = (
    SELECT rn FROM (
        SELECT project_user_id, name, 
               ROW_NUMBER() OVER (PARTITION BY project_user_id ORDER BY name) AS rn
        FROM your_table_name
    ) AS sub
    WHERE sub.project_user_id = your_table_name.project_user_id 
      AND sub.name = your_table_name.name
);

在Flask中通过db.execute执行：

db.execute("""
    UPDATE your_table_name
    SET id = (
        SELECT rn FROM (
            SELECT project_user_id, name, 
                   ROW_NUMBER() OVER (PARTITION BY project_user_id ORDER BY name) AS rn
            FROM your_table_name
        ) AS sub
        WHERE sub.project_user_id = your_table_name.project_user_id 
          AND sub.name = your_table_name.name
    );
""")
db.commit()

注意：替换your_table_name为你的实际表名；ORDER BY name可根据实际需求替换为其他排序字段（如创建时间），保证每个用户的条目顺序符合预期。

2. 插入新数据时自动生成ID #

每次插入新项目时，先查询该用户已有的最大id，再加1作为新的项目ID：

def add_project(user_id, project_name):
    # 查询该用户当前最大的项目ID，无数据则返回0
    max_id = db.execute("""
        SELECT COALESCE(MAX(id), 0) FROM your_table_name 
        WHERE project_user_id = ?
    """, (user_id,)).fetchone()[0]
    # 插入新数据
    db.execute("""
        INSERT INTO your_table_name (project_user_id, name, id)
        VALUES (?, ?, ?)
    """, (user_id, project_name, max_id + 1))
    db.commit()

注意：Flask搭配SQLite通常为单线程场景，该方式不会出现ID重复问题；若涉及高并发，可将查询与插入操作包裹在事务中以保证严谨性。

补充提示 #

• SQLite的ROW_NUMBER()函数需要3.25.0及以上版本支持，若版本过低需先升级。
• 如果不需要持久化存储id，也可在查询时通过窗口函数动态生成，但需要持久化的话，优先选择上述两种方式。

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