Room数据库迁移添加列失败，表结构匹配异常排查

我定义了如下UserEntity数据类：

data class UserEntity(
@PrimaryKey var uid: String,
var name: String?,
var photo: String?,
var status: Int = 0,
var interaction: Long? = null,
var additional: String? = ""
)

编写了从版本1到2的数据库迁移逻辑：

private val MIGRATION_1_2 = object : Migration(1, 2) {
        override fun migrate(database: SupportSQLiteDatabase) {
            database.execSQL("ALTER TABLE UserEntity ADD COLUMN additional TEXT NOT NULL DEFAULT ''")
        }
    }

运行时抛出以下异常：

Caused by: java.lang.IllegalStateException: Migration didn't properly handle: UserEntity(com.michlindevelopment.way.mainapp.database.UserEntity).
                                                                                                 Expected:
                                                                                                TableInfo{name='UserEntity', columns={name=Column{name='name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, interaction=Column{name='interaction', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, uid=Column{name='uid', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}, photo=Column{name='photo', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, status=Column{name='status', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, additional=Column{name='additional', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}}, foreignKeys=[], indices=[]}
                                                                                                 Found:
                                                                                                TableInfo{name='UserEntity', columns={uid=Column{name='uid', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}, name=Column{name='name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, photo=Column{name='photo', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, status=Column{name='status', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, interaction=Column{name='interaction', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='undefined'}, additional=Column{name='additional', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue=''''}}, foreignKeys=[], indices=[]}

我怀疑是列顺序问题？预期中name列排在首位，但实际查询到uid列排在首位。

列顺序不是问题，Room在校验表结构时不会在意列的排列顺序。真正的冲突点在additional列的约束上：

• 数据类中additional是String?（可空类型），所以Room预期该列是notNull=false，默认值无约束（显示为undefined）
• 但你的迁移语句里给该列加了NOT NULL约束，还设置了默认值''，导致实际表结构中该列是notNull=true，和预期不匹配

有两种修复方式：

方式一：修改迁移语句，匹配数据类的可空设置 #

去掉NOT NULL约束，让列变为可空：

private val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL("ALTER TABLE UserEntity ADD COLUMN additional TEXT DEFAULT ''")
    }
}

方式二：修改数据类，匹配迁移语句的非空设置 #

把additional改为非空类型，去掉问号：

data class UserEntity(
@PrimaryKey var uid: String,
var name: String?,
var photo: String?,
var status: Int = 0,
var interaction: Long? = null,
var additional: String = "" // 改为非空类型
)

根据你的业务需求选择即可：如果业务允许additional为空，选方式一；如果要求该字段必须有值，选方式二。

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