Golang中如何更新SQLite3数据库删除后的主键

Golang中如何更新SQLite3数据库删除后的主键 我一直在为个人项目开发后端，使用 sqlite3 和 mattn/go-sqlite3 驱动。基本上有一个名为 tutors 的表，创建语句如下：

CREATE TABLE IF NOT EXISTS tutors(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    firstname VARCHAR NOT NULL,
    lastname VARCHAR NOT NULL,
    email VARCHAR NOT NULL
);

到目前为止，我已经实现了 CREATE 和 READ 功能。现在我正在处理删除导师的 DELETE 请求。目前，它确实会删除一个 tutor，但其他所有 tutors 的 id 都没有更新。删除一个导师后，我该如何更新其他导师的 id？

这是 Github 上的代码库。

另外，如果您对这个项目有任何其他一般性建议，请尽管提出！我非常开放，并且一直在不断学习。


更多关于Golang中如何更新SQLite3数据库删除后的主键的实战教程也可以访问 https://www.itying.com/category-94-b0.html

4 回复

很高兴能帮助另一位学习者！

更多关于Golang中如何更新SQLite3数据库删除后的主键的实战系列教程也可以访问 https://www.itying.com/category-94-b0.html


这并没有什么特别的原因。我完全是数据库工作的新手，所以有点想当然地认为删除后记录应该更新，没有意识到存在间隔可能是正常现象。看到您第一次暗示删除记录后出现间隔是可以接受的，这让我明白主键不需要改变。这对我来说是个很好的信息。谢谢！

我刚接触Go语言，但有SQLite的工作经验。主ID字段在删除记录时发生变化是不寻常的。这会产生两个影响：

  1. 查看表时，你会看到被删除记录留下的空缺。这很正常。
  2. 记录的ID号永远不会改变。如果现在记录5对你特别重要，你将来始终可以引用记录5，并知道它是同一条记录。如果主键字段数据在每次添加或删除数据时都发生变化，你就无法确信记录5中的数据与上次查看时相同。

有什么特别的原因让你想要在删除行时重新调整ID字段的数值吗？

在 SQLite 中，AUTOINCREMENT 字段的设计目的是自动分配唯一标识符，而不是维护连续的数值序列。删除记录后，已删除的 ID 不会被重用，其他记录的 ID 也不会自动更新。这是关系型数据库的常见行为，因为主键的主要作用是唯一标识记录，而非表示顺序。

如果你确实需要重新编号 ID（通常不推荐，因为可能破坏外键关系），可以执行以下步骤：

  1. 创建一个临时表，复制原表数据但重新分配 ID。
  2. 删除原表。
  3. 将临时表重命名为原表名。

以下是示例代码：

package main

import (
    "database/sql"
    "log"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    db, err := sql.Open("sqlite3", "./tutors.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 开始事务
    tx, err := db.Begin()
    if err != nil {
        log.Fatal(err)
    }

    // 创建临时表，不带 AUTOINCREMENT，以便重新插入时生成新 ID
    _, err = tx.Exec(`
        CREATE TABLE IF NOT EXISTS tutors_temp(
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            firstname VARCHAR NOT NULL,
            lastname VARCHAR NOT NULL,
            email VARCHAR NOT NULL
        )
    `)
    if err != nil {
        tx.Rollback()
        log.Fatal(err)
    }

    // 将数据从原表插入临时表，忽略原 ID，让 SQLite 自动分配新 ID
    _, err = tx.Exec(`
        INSERT INTO tutors_temp (firstname, lastname, email)
        SELECT firstname, lastname, email FROM tutors ORDER BY id
    `)
    if err != nil {
        tx.Rollback()
        log.Fatal(err)
    }

    // 删除原表
    _, err = tx.Exec(`DROP TABLE tutors`)
    if err != nil {
        tx.Rollback()
        log.Fatal(err)
    }

    // 将临时表重命名为原表
    _, err = tx.Exec(`ALTER TABLE tutors_temp RENAME TO tutors`)
    if err != nil {
        tx.Rollback()
        log.Fatal(err)
    }

    // 提交事务
    err = tx.Commit()
    if err != nil {
        log.Fatal(err)
    }
}

这段代码会在删除记录后重新编号所有导师的 ID，使其从 1 开始连续。但请注意，频繁进行此操作可能影响性能，且如果其他表有外键引用 tutors.id，需要先处理这些依赖。

通常，更好的做法是接受 ID 的不连续性，使用其他字段（如创建时间）排序。例如，在查询时按 id 或 created_at 排序：

rows, err := db.Query("SELECT id, firstname, lastname, email FROM tutors ORDER BY id")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

如果你在项目中需要维护顺序，考虑添加一个 position 或 order_column 字段专门用于排序。

回到顶部