重命名列名时出现 SQLITE 语法错误代码 1 [英] SQLITE syntax error code 1 when renaming a column name

问题描述

我正在我的 Android 应用中迁移 Room 数据库.这是迁移代码:

I am migrating a Room database in my Android app. This is the migration code:

static final Migration MIGRATION_1_2 = new Migration(1, 2) {
    @Override
    public void migrate(SupportSQLiteDatabase database) {
        database.execSQL("ALTER TABLE item RENAME itemInfoId TO itemId");
    }
};

错误信息

android.database.sqlite.SQLiteException: near "itemInfoId": syntax error (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE item RENAME itemInfoId TO itemId

我也试过"ALTER TABLE item RENAME COLUMN itemInfoId TO itemId"的SQL，同样的错误

I have also tried the SQL of "ALTER TABLE item RENAME COLUMN itemInfoId TO itemId", same error

android.database.sqlite.SQLiteException: near "COLUMN": syntax error (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE item RENAME COLUMN itemInfoId TO itemId

推荐答案

Rename 关键字在 SQLite 3.25 版中可用，但不适用于最新的 android 版本.您将不得不手动升级表

Rename keyword is available in SQLite version 3.25 which is not available for the latest android version. You will have to manually upgrade the table

1. 使用正确的列值 itemId 创建 item_tmp 表

1. Create item_tmp table with correct column value itemId

CREATE TABLE item_tmp(<column1> <data_type>, itemId <data_type>,.....)

2. 将数据从 item 复制到 item_tmp

2. Copy the data from item to item_tmp

INSERT INTO item(<column1>, <column2>,..) 
    SELECT <column1>, <column1>, ... 
    FROM item_tmp;

3. 删除表格项

DROP TABLE item;

4. 重命名 item_tmp 表

4. Rename the item_tmp table

ALTER TABLE item_tmp RENAME TO item;

这篇关于重命名列名时出现 SQLITE 语法错误代码 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！