Android Room数据库迁移后旧数据丢失，如何保留旧数据？

问题背景 #

将Android Room数据库从版本1迁移到版本2时，曾触发java.lang.IllegalStateException: Migration didn't properly handle:异常，修复后应用可正常启动，但旧数据全部丢失，需要实现新增列同时完整保留旧数据的迁移流程。

核心错误原因 #

1. 实体类主键配置错误：原CourseModal中@PrimaryKey(autoGenerate = true)被错误标注在EnrollmentStatus字段上，而非id字段，导致Room对表结构的校验失败，触发迁移异常。
2. 迁移SQL列名不匹配：数据复制SQL中使用了小写的middlename、lastname，但原表字段为驼峰式的middleName、lastName（SQLite虽大小写不敏感，但严格匹配可避免潜在问题）。
3. 破坏性迁移兜底风险：数据库构建时启用了fallbackToDestructiveMigration()，一旦迁移逻辑存在问题，Room会直接销毁旧数据库重建，导致数据丢失。

修复步骤与代码实现 #

1. 修正实体类主键配置 #

将@PrimaryKey(autoGenerate = true)移至id字段，确保主键逻辑正确：

package com.gtappdevelopers.gfgroomdatabase;

import android.graphics.Bitmap;
import androidx.room.ColumnInfo;
import androidx.room.Entity;
import androidx.room.PrimaryKey;

@Entity(tableName = "course_table")
public class CourseModal {

    // 正确配置主键：id自增
    @PrimaryKey(autoGenerate = true)
    private int id;
    // 新增字段：EnrollmentStatus
    private int EnrollmentStatus;
    
    private String firstName;
    private String middleName;
    private String lastName;
    private String DOB;
    private String gender;
    private String address;
    private String designation;
    private String email;
    private String phoneNumber;

    @ColumnInfo(typeAffinity = ColumnInfo.BLOB)
    byte[] image;

    // getter、setter方法
    public int getId() { return id; }
    public void setId(int id) { this.id = id; }
    public int getEnrollmentStatus() { return EnrollmentStatus; }
    public void setEnrollmentStatus(int enrollmentStatus) { EnrollmentStatus = enrollmentStatus; }
    public String getFirstName() { return firstName; }
    public void setFirstName(String firstName) { this.firstName = firstName; }
    public String getMiddleName() { return middleName; }
    public void setMiddleName(String middleName) { this.middleName = middleName; }
    public String getLastName() { return lastName; }
    public void setLastName(String lastName) { this.lastName = lastName; }
    public String getDOB() { return DOB; }
    public void setDOB(String DOB) { this.DOB = DOB; }
    public String getGender() { return gender; }
    public void setGender(String gender) { this.gender = gender; }
    public String getAddress() { return address; }
    public void setAddress(String address) { this.address = address; }
    public String getDesignation() { return designation; }
    public void setDesignation(String designation) { this.designation = designation; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }
    public String getPhoneNumber() { return phoneNumber; }
    public void setPhoneNumber(String phoneNumber) { this.phoneNumber = phoneNumber; }
    public byte[] getImage() { return image; }
    public void setImage(byte[] image) { this.image = image; }

    // 构造方法
    public CourseModal(String firstName, String middleName, String lastName,String DOB,String gender,String address,String designation,String email,String phoneNumber,Bitmap image) {
        this.firstName = firstName;
        this.middleName = middleName;
        this.lastName = lastName;
        this.DOB = DOB;
        this.gender = gender;
        this.address = address;
        this.designation = designation;
        this.email = email;
        this.phoneNumber = phoneNumber;
        this.image = DataConverter.convertImageToByteArray(image);
    }
    public CourseModal(){}
}

2. 修正数据库迁移逻辑 #

调整迁移SQL的列名匹配，移除破坏性迁移兜底，确保数据完整复制：

package com.gtappdevelopers.gfgroomdatabase;

import android.content.Context;
import androidx.annotation.NonNull;
import androidx.room.Database;
import androidx.room.Room;
import androidx.room.RoomDatabase;
import androidx.room.migration.Migration;
import androidx.sqlite.db.SupportSQLiteDatabase;

@Database(entities = {CourseModal.class}, version = 2) // 版本对应迁移1→2
public abstract class CourseDatabase extends RoomDatabase {
    private static CourseDatabase instance;
    public abstract Dao Dao();

    // 版本1→2的迁移逻辑：新增EnrollmentStatus字段
    static Migration MIGRATION_1_2 = new Migration(1,2) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            // 方案1：直接ALTER TABLE新增字段（更简洁，无需重建表）
            database.execSQL("ALTER TABLE course_table ADD COLUMN EnrollmentStatus INTEGER NOT NULL DEFAULT 0");
            
            // 若之前用重建表方案，需修正列名匹配：
            /*
            database.execSQL("CREATE TABLE course_table_new(" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                    "firstName TEXT," +
                    "middleName TEXT," +
                    "lastName TEXT," +
                    "DOB TEXT," +
                    "gender TEXT," +
                    "address TEXT," +
                    "designation TEXT," +
                    "email TEXT," +
                    "phoneNumber TEXT," +
                    "EnrollmentStatus INTEGER NOT NULL DEFAULT 0," +
                    "image BLOB)");
            // 严格匹配原表列名
            database.execSQL("INSERT INTO course_table_new(id,firstName,middleName,lastName,DOB,gender,address,designation,email,phoneNumber,image) " +
                    "SELECT id,firstName,middleName,lastName,DOB,gender,address,designation,email,phoneNumber,image FROM course_table");
            database.execSQL("DROP TABLE course_table");
            database.execSQL("ALTER TABLE course_table_new RENAME TO course_table");
            */
        }
    };

    public static synchronized CourseDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(context.getApplicationContext(),
                            CourseDatabase.class, "course_database")
                    // 移除破坏性迁移，避免意外销毁数据
                    // .fallbackToDestructiveMigration()
                    .addCallback(roomCallback)
                    .addMigrations(MIGRATION_1_2)
                    .build();
        }
        return instance;
    }

    private static RoomDatabase.Callback roomCallback = new RoomDatabase.Callback() {
        @Override
        public void onCreate(@NonNull SupportSQLiteDatabase db) {
            super.onCreate(db);
            // 初始化逻辑保持不变
        }
    };
}

关键说明 #

• 新增字段优先用ALTER TABLE：仅新增字段时，无需重建表，直接用ALTER TABLE ADD COLUMN更高效且不易出错。
• 禁用破坏性迁移：移除fallbackToDestructiveMigration()，避免迁移失败时自动销毁旧数据，强制排查迁移逻辑问题。
• 版本号对应：数据库版本号需与迁移规则的版本范围匹配，若已升级到版本3，需补充MIGRATION_2_3规则或回退版本后重新迁移。

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