Android Room数据库迁移：数据类型修改与多字段变更实现咨询

原有Car实体类 #

@Entity(tableName = "Cars")
data class Car(
    @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "Id") val id: Int? = null,
    @ColumnInfo(name = "Manufacture") var manufacture: String? = null, 
    @ColumnInfo(name = "Model") var model: String? = null,
    @ColumnInfo(name = "Date_created") val dateCreated: String? = null,
    @ColumnInfo(name = "Date_modified") var dateModified: String? = null,
    @ColumnInfo(name = "Color") var color: String? = null,
    @ColumnInfo(name = "Tags") var tags: String? = null
)

升级后Car实体类 #

@Entity(tableName = "Cars")
data class Car(
    @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "Id") var id: Long = 0,
    @ColumnInfo(name = "Manufacture") var manufacture: String = "", 
    @ColumnInfo(name = "Model") var model: String = "",
    @ColumnInfo(name = "Labels") var labels: List<String> = listOf(),
    @ColumnInfo(name = "Color") var color: String? = null,
    @ColumnInfo(name = "Timestamp") var timestamp: Long = 0L,
    @ColumnInfo(name = "Type") var type: Type = Type.Sedan,
    @ColumnInfo(name = "Folder") var folder: Folder = Folder.Used
)

字段变更清单 #

• 字段Id：从可空Int改为非空Long
• 字段Manufacture：从可空String改为非空String（默认空字符串）
• 移除字段Date_created
• 字段Date_modified重命名为Timestamp，从可空String时间戳转为非空Long
• 字段Tags（逗号分隔字符串）改为Labels（List<String>类型）
• 新增枚举字段Type（默认Sedan）和Folder（默认Used）

修正后的迁移代码 #

@Database(entities = [Car::class], version = 2, exportSchema = false)
abstract class CarsDatabase : RoomDatabase() {

    abstract val carDao: CarDao

    companion object {

        @Volatile
        private var INSTANCE: CarsDatabase? = null

        private val migration = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                database.apply {
                    // 1. 创建临时表，适配新结构
                    execSQL(
                        """CREATE TABLE cars_tmp (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            Manufacture TEXT NOT NULL DEFAULT '',
                            Model TEXT NOT NULL DEFAULT '',
                            Timestamp INTEGER NOT NULL DEFAULT 0,
                            Labels TEXT NOT NULL DEFAULT '[]',
                            Color TEXT
                        )"""
                    )

                    // 2. 从原表迁移数据，处理类型转换
                    execSQL(
                        """INSERT INTO cars_tmp (Id, Manufacture, Model, Timestamp, Labels, Color)
                            SELECT 
                                Id,
                                IFNULL(Manufacture, ''),
                                IFNULL(Model, ''),
                                IFNULL(CAST(Date_modified AS INTEGER), 0),
                                CASE
                                    WHEN Tags IS NULL OR Tags = '' THEN '[]'
                                    ELSE '["' || REPLACE(Tags, ',', '","') || '"]'
                                END,
                                Color
                            FROM Cars"""
                    )

                    // 3. 删除原表，重命名临时表为原表名
                    execSQL("DROP TABLE Cars")
                    execSQL("ALTER TABLE cars_tmp RENAME TO Cars")

                    // 4. 新增枚举字段并设置默认值
                    execSQL("ALTER TABLE Cars ADD COLUMN Type TEXT NOT NULL DEFAULT 'Sedan'")
                    execSQL("ALTER TABLE Cars ADD COLUMN Folder TEXT NOT NULL DEFAULT 'Used'")
                }
            }
        }
        
        fun getInstance(context: Context): CarsDatabase = INSTANCE ?: synchronized(this) {
            val instance = Room.databaseBuilder(
                context.applicationContext,
                CarsDatabase::class.java,
                "Cars.db"
            )
                .addMigrations(migration)
                .build()
            INSTANCE = instance
            instance
        }
    }
}

关键修正说明 #

• SQLite类型适配：SQLite没有LONG和LIST类型，长整型用INTEGER存储，Room的List<String>会序列化为JSON字符串，因此Labels字段定义为TEXT。
• 时间戳转换：使用CAST(Date_modified AS INTEGER)将字符串时间戳转为整数，通过IFNULL处理空值，确保非空约束。
• Tags转Labels：将逗号分隔的字符串拼接为JSON数组格式（如"Gas,Diesel"转为["Gas","Diesel"]），匹配Room对List类型的序列化规则；空值或空字符串转为[]。
• 非空字段处理：对Manufacture、Model使用IFNULL(字段名, '')将原有空值转为默认空字符串，满足新实体类的非空要求。
• 表名一致性：原表名为Cars，修正迁移中表名大小写不一致的问题，避免表不存在错误。
• 枚举字段默认值：新增字段时直接设置默认值为枚举常量的名称（如'Sedan'），适配Room默认的枚举存储策略（存储枚举name属性）。

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