Room数据库主键数据类型变更的迁移方案咨询

要实现Log主键类型从String改为自增Int，同时按date排序生成新id且保留LogItem数据，需要通过临时表过渡+映射原外键关系的方式编写Room迁移脚本，避免外键CASCADE删除数据。以下是具体步骤：

1. 修改实体类 #

首先更新你的Kotlin实体类，匹配新的表结构：

@Entity
data class Log(
   @PrimaryKey(autoGenerate = true) val id: Int = 0, // 改为自增Int
   val date: Long,
   val note: String,
)

@Entity(
    foreignKeys = [
        ForeignKey(
            entity = Log::class,
            parentColumns = ["id"],
            childColumns = ["logId"],
            onDelete = CASCADE,
        )
    ],
    indices = [Index("logId")]
)
data class LogItem(
   @PrimaryKey(autoGenerate = true) val id: Int = 0,
   val logId: Int, // 同步改为Int，匹配Log的新主键类型
   val name: String,
)

2. 编写Room迁移脚本 #

假设你的数据库当前版本是1，升级到2，添加如下Migration：

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // 1. 创建临时Log表（新结构）
        database.execSQL("""
            CREATE TABLE temp_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                date INTEGER NOT NULL,
                note TEXT NOT NULL
            )
        """.trimIndent())

        // 2. 按date排序插入原Log数据到临时表，生成自增id（从1开始）
        database.execSQL("""
            INSERT INTO temp_log (id, date, note)
            SELECT ROW_NUMBER() OVER (ORDER BY date ASC), date, note FROM Log
        """.trimIndent())

        // 3. 创建原id与新id的映射表
        database.execSQL("""
            CREATE TABLE log_id_mapping (
                old_id TEXT PRIMARY KEY,
                new_id INTEGER NOT NULL
            )
        """.trimIndent())
        database.execSQL("""
            INSERT INTO log_id_mapping
            SELECT id AS old_id, ROW_NUMBER() OVER (ORDER BY date ASC) AS new_id FROM Log
        """.trimIndent())

        // 4. 创建临时LogItem表（新结构，logId改为Int）
        database.execSQL("""
            CREATE TABLE temp_logitem (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                logId INTEGER NOT NULL,
                name TEXT NOT NULL
            )
        """.trimIndent())

        // 5. 迁移LogItem数据，替换logId为新的Int类型id
        database.execSQL("""
            INSERT INTO temp_logitem (id, logId, name)
            SELECT li.id, lim.new_id, li.name
            FROM LogItem li
            JOIN log_id_mapping lim ON li.logId = lim.old_id
        """.trimIndent())

        // 6. 删除原表
        database.execSQL("DROP TABLE Log")
        database.execSQL("DROP TABLE LogItem")

        // 7. 重命名临时表为正式表名
        database.execSQL("ALTER TABLE temp_log RENAME TO Log")
        database.execSQL("ALTER TABLE temp_logitem RENAME TO LogItem")

        // 8. 重建LogItem的索引（临时表不会继承原索引）
        database.execSQL("CREATE INDEX index_LogItem_logId ON LogItem(logId)")
    }
}

3. 注意事项 #

• 确保迁移脚本中的SQL语句与SQLite语法兼容，ROW_NUMBER()需要SQLite 3.25.0及以上版本（Room默认集成的版本通常满足）。
• 若原Log表中存在date相同的记录，ROW_NUMBER()会按原表顺序分配id，若需要更精确的排序规则，可在ORDER BY date ASC后追加其他字段（如note）。
• 迁移过程中无需手动关闭外键约束，原外键会随原表删除而移除，重建后的表会带有新的外键约束。

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