迁移含VARCHAR、TIMESTAMP类型的SQLite表至Room数据库时遇类型错误

Got it, let's break down why this migration is failing and how to fix it. The core issue is Room's strict schema validation: when you define fields like req_type and timestamp as String in your Room entity, Room expects those columns in the SQLite database to be explicitly typed as TEXT. Even though SQLite treats VARCHAR as functionally equivalent to TEXT under the hood, Room's checker doesn't overlook the type name mismatch. Similarly, TIMESTAMP in SQLite is just an alias for storing date/time values as TEXT or INTEGER, but Room requires the explicit TEXT type here to pass validation.

Here's the step-by-step solution:

1. Confirm Your Room Entity Definition #

First, ensure your entity class matches the expected type mappings. For example:

@Entity(tableName = "api_data")
data class ApiData(
    @PrimaryKey(autoGenerate = true) val id: Int,
    val api_response_json: String?,
    val api_req: String?,
    val post_params: String?,
    val req_type: String?, // Room maps this to TEXT
    val timestamp: String? // Room maps this to TEXT
)

2. Update the Migration Code #

Modify your migration to create the new table with Room's expected TEXT types, and explicitly cast the old data to TEXT during the copy process:

static final Migration MIGRATION_1_2 = new Migration(1, 2) {
    @Override
    public void migrate(SupportSQLiteDatabase database) {
        // Create new table with Room-compliant TEXT types
        database.execSQL(
            "CREATE TABLE api_data_new (id INTEGER PRIMARY KEY AUTOINCREMENT, api_response_json TEXT, api_req TEXT, post_params TEXT, req_type TEXT, timestamp TEXT)");
        
        // Copy data, casting old types to TEXT to match Room's schema
        database.execSQL("INSERT INTO api_data_new (id, api_response_json, api_req, post_params, req_type, timestamp) " +
            "SELECT id, api_response_json, api_req, post_params, CAST(req_type AS TEXT), CAST(timestamp AS TEXT) " +
            "FROM api_data");
        
        // Drop the original table
        database.execSQL("DROP TABLE api_data");
        
        // Rename the new table to the original table name
        database.execSQL("ALTER TABLE api_data_new RENAME TO api_data");
    }
};

Key Changes Explained #

• Explicit TEXT Columns: The new table uses TEXT instead of VARCHAR and TIMESTAMP to align perfectly with Room's schema expectations.
• CAST Operations: Using CAST(column AS TEXT) ensures existing data from the old columns is converted to the exact type Room requires, even if SQLite would normally treat them as equivalent.
• Correct Table Names: I replaced the placeholder old_Table_name with api_data (the actual table name from your error message) to avoid "table not found" errors.

Additional Notes #

• Double-check that your migration version numbers (1 → 2) match the version specified in your @Database annotation.
• If your timestamp field was intended to be a Date type in the entity, you'd need to use type converters instead, but based on your error message, TEXT is the expected type here.

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