Room迁移报错：NO SUCH COLUMN EXISTS，如何避免崩溃且不丢失数据？

崩溃根本原因 #

你在MIGRATION_13_14中创建LocationData表的SQL语句存在错误：指定LocationID为主键，但建表字段列表里完全没定义这个列，导致SQLite编译时抛出no such column: LocationID错误，直接引发应用崩溃。

避免崩溃+保留数据的解决方案 #

1. 修复迁移代码（针对未升级的用户） #

直接修改MIGRATION_13_14中创建LocationData表的SQL，替换为正确的建表语句：

private val MIGRATION_13_14 = object : Migration(13, 14) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL(
            "CREATE TABLE IF NOT EXISTS surveyReasonField (id INTEGER NOT NULL, Reason TEXT, ReasonID INTEGER, IsAccountNumberRequired INTEGER, IsMeterSerialNumberRequired INTEGER, IsCTRatioRequired INTEGER, IsMeterTypeRequired INTEGER, IsGPSCoordinateRequired INTEGER, IsCustomerNameRequired INTEGER, IsBuildingNameandNumberRequired INTEGER, IsStreetNumberRequired INTEGER, IsInstallLocationRequired INTEGER, IsBuildingTypeRequired INTEGER, IsSignalAvailabilityRequired INTEGER, IsTypeofAntennaRequired INTEGER, IsMeterPanelConditionRequired INTEGER, IsMeterPanelLockStatusRequired INTEGER, IsSafeToWorkRequired INTEGER, IsCutoutConditionRequired INTEGER, IsCableConditionRequired INTEGER, IsCustomerContactDetailsRequired INTEGER, IsReadinessofInstallationRequired INTEGER, IsMeterPanelNumberRequired INTEGER, IsMeterDataImageRequired INTEGER, IsMeterPanelImageRequired INTEGER, IsCutOutImageRequired INTEGER, IsCableConditionImageRequired INTEGER, IsCustomerEmailRequired INTEGER, IsSignalTypeRequired INTEGER, PRIMARY KEY(`id`))"
        )
        database.execSQL(
            "ALTER TABLE settingEntity ADD COLUMN NoOfDaysForImageDeletion INTEGER"
        )
        // 替换为正确的建表语句
        database.execSQL(
            "CREATE TABLE IF NOT EXISTS LocationData (`LocationID` INTEGER NOT NULL,`Latitude` INTEGER NOT NULL, `Longitude` INTEGER NOT NULL, `Accuracy` INTEGER NOT NULL, `DateTime` TEXT NOT NULL, `status` TEXT, `TechUserID` INTEGER NOT NULL, `Speed` INTEGER NOT NULL, `AndroidVersion` TEXT NOT NULL, `AppVersion` TEXT NOT NULL, PRIMARY KEY(`LocationID`))"
        )
        database.execSQL(
            "ALTER TABLE settingEntity ADD COLUMN IsLiveTrackingEnabled INTEGER"
        )
    }
}

这样后续用户升级时，迁移能正常执行，不会崩溃。

2. 处理已崩溃的用户（数据库停留在版本13且迁移未完成） #

由于部分用户可能已经触发迁移崩溃，数据库处于版本13但部分迁移步骤未执行，需要新增一个覆盖性的迁移（比如从版本13到15，同时将数据库版本号改为15），确保所有操作都能安全补全：

private val MIGRATION_13_15 = object : Migration(13, 15) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // 检查surveyReasonField表是否存在，不存在则创建
        val cursor = database.query("SELECT name FROM sqlite_master WHERE type='table' AND name='surveyReasonField'")
        if (!cursor.moveToFirst()) {
            database.execSQL(
                "CREATE TABLE IF NOT EXISTS surveyReasonField (id INTEGER NOT NULL, Reason TEXT, ReasonID INTEGER, IsAccountNumberRequired INTEGER, IsMeterSerialNumberRequired INTEGER, IsCTRatioRequired INTEGER, IsMeterTypeRequired INTEGER, IsGPSCoordinateRequired INTEGER, IsCustomerNameRequired INTEGER, IsBuildingNameandNumberRequired INTEGER, IsStreetNumberRequired INTEGER, IsInstallLocationRequired INTEGER, IsBuildingTypeRequired INTEGER, IsSignalAvailabilityRequired INTEGER, IsTypeofAntennaRequired INTEGER, IsMeterPanelConditionRequired INTEGER, IsMeterPanelLockStatusRequired INTEGER, IsSafeToWorkRequired INTEGER, IsCutoutConditionRequired INTEGER, IsCableConditionRequired INTEGER, IsCustomerContactDetailsRequired INTEGER, IsReadinessofInstallationRequired INTEGER, IsMeterPanelNumberRequired INTEGER, IsMeterDataImageRequired INTEGER, IsMeterPanelImageRequired INTEGER, IsCutOutImageRequired INTEGER, IsCableConditionImageRequired INTEGER, IsCustomerEmailRequired INTEGER, IsSignalTypeRequired INTEGER, PRIMARY KEY(`id`))"
            )
        }
        cursor.close()

        // 检查settingEntity是否已有NoOfDaysForImageDeletion字段，没有则添加
        val columnCursor = database.query("PRAGMA table_info(settingEntity)")
        var hasNoOfDaysColumn = false
        while (columnCursor.moveToNext()) {
            if (columnCursor.getString(columnCursor.getColumnIndexOrThrow("name")) == "NoOfDaysForImageDeletion") {
                hasNoOfDaysColumn = true
                break
            }
        }
        if (!hasNoOfDaysColumn) {
            database.execSQL("ALTER TABLE settingEntity ADD COLUMN NoOfDaysForImageDeletion INTEGER")
        }
        columnCursor.close()

        // 检查LocationData表是否存在，不存在则用正确语句创建
        val locationCursor = database.query("SELECT name FROM sqlite_master WHERE type='table' AND name='LocationData'")
        if (!locationCursor.moveToFirst()) {
            database.execSQL(
                "CREATE TABLE IF NOT EXISTS LocationData (`LocationID` INTEGER NOT NULL,`Latitude` INTEGER NOT NULL, `Longitude` INTEGER NOT NULL, `Accuracy` INTEGER NOT NULL, `DateTime` TEXT NOT NULL, `status` TEXT, `TechUserID` INTEGER NOT NULL, `Speed` INTEGER NOT NULL, `AndroidVersion` TEXT NOT NULL, `AppVersion` TEXT NOT NULL, PRIMARY KEY(`LocationID`))"
            )
        }
        locationCursor.close()

        // 检查settingEntity是否已有IsLiveTrackingEnabled字段，没有则添加
        val liveTrackCursor = database.query("PRAGMA table_info(settingEntity)")
        var hasLiveTrackColumn = false
        while (liveTrackCursor.moveToNext()) {
            if (liveTrackCursor.getString(liveTrackCursor.getColumnIndexOrThrow("name")) == "IsLiveTrackingEnabled") {
                hasLiveTrackColumn = true
                break
            }
        }
        if (!hasLiveTrackColumn) {
            database.execSQL("ALTER TABLE settingEntity ADD COLUMN IsLiveTrackingEnabled INTEGER")
        }
        liveTrackCursor.close()
    }
}

然后在Room数据库构建器中更新版本号并添加这个迁移：

Room.databaseBuilder(context, YourDatabase::class.java, "your-db-name")
    .addMigrations(MIGRATION_13_14, MIGRATION_13_15)
    // 可选：作为最后兜底，避免极端情况崩溃，但会丢失数据，谨慎使用
    // .fallbackToDestructiveMigration()
    .build()

3. 关于fallback方法的选择 #

• fallbackToDestructiveMigrationFrom()：指定从特定旧版本迁移失败时，销毁并重建数据库，会丢失所有现有数据，完全不符合你保留数据的目标，不建议使用。
• fallbackToDestructiveMigrationOnDowngrade()：仅在用户安装旧版本APP（数据库降级）时触发破坏性重建，和当前的升级迁移崩溃问题无关，对你的场景没用。

所以这两个方法都不适合你的需求，优先通过修复迁移代码和补全迁移步骤来解决问题。

未来预防措施 #

• 编写迁移SQL时，严格对照Entity类的字段定义，确保字段名、类型、约束（NOT NULL、主键等）完全一致。
• 测试迁移逻辑时，覆盖从旧版本到新版本的完整流程，模拟真实用户的升级场景。
• 可以在迁移中添加必要的存在性检查（如上述代码中的表、字段检查），避免重复执行操作导致错误。

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