我需要重命名SQLite数据库中一些表中的一些列。 我知道以前有人在stackoverflow上问过类似的问题,但这是针对一般的SQL,没有提到SQLite的情况。

从ALTER TABLE的SQLite文档中,我收集到不可能“轻松”地做这样的事情(即一个ALTER TABLE语句)。

我想知道有人知道一个通用的SQL方法做这样的事情与SQLite。


当前回答

修改表列< id >为< _id >

 String LastId = "id";

    database.execSQL("ALTER TABLE " + PhraseContract.TABLE_NAME + " RENAME TO " + PhraseContract.TABLE_NAME + "old");
    database.execSQL("CREATE TABLE " + PhraseContract.TABLE_NAME
    +"("
            + PhraseContract.COLUMN_ID + " INTEGER PRIMARY KEY,"
            + PhraseContract.COLUMN_PHRASE + " text ,"
            + PhraseContract.COLUMN_ORDER  + " text ,"
            + PhraseContract.COLUMN_FROM_A_LANG + " text"
    +")"
    );
    database.execSQL("INSERT INTO " +
            PhraseContract.TABLE_NAME + "("+ PhraseContract.COLUMN_ID +" , "+ PhraseContract.COLUMN_PHRASE + " , "+ PhraseContract.COLUMN_ORDER +" , "+ PhraseContract.COLUMN_FROM_A_LANG +")" +
            " SELECT " + LastId +" , "+ PhraseContract.COLUMN_PHRASE + " , "+ PhraseContract.COLUMN_ORDER +" , "+ PhraseContract.COLUMN_FROM_A_LANG +
            " FROM " + PhraseContract.TABLE_NAME + "old");
    database.execSQL("DROP TABLE " + PhraseContract.TABLE_NAME + "old");

其他回答

Sqlite3 yourdb .dump > /tmp/db.txt 编辑/tmp/db.txt修改创建行的列名 Sqlite2 yourdb2 < /tmp/db.txt Mv /move yourdb2 yourdb

如前所述,有一个工具SQLite数据库浏览器,它可以做到这一点。Lyckily,这个工具保存用户或应用程序执行的所有操作的日志。执行此操作一次并查看应用程序日志,您将看到所涉及的代码。复制查询并根据需要粘贴。为我工作。希望这能有所帮助

创建一个具有所需列名的新列:COLNew。

ALTER TABLE {tableName} ADD COLUMN COLNew {type};

将旧列COLOld的内容复制到新列COLNew。

INSERT INTO {tableName} (COLNew) SELECT {COLOld} FROM {tableName}

注:以上一行必须使用括号。

修改表列< id >为< _id >

 String LastId = "id";

    database.execSQL("ALTER TABLE " + PhraseContract.TABLE_NAME + " RENAME TO " + PhraseContract.TABLE_NAME + "old");
    database.execSQL("CREATE TABLE " + PhraseContract.TABLE_NAME
    +"("
            + PhraseContract.COLUMN_ID + " INTEGER PRIMARY KEY,"
            + PhraseContract.COLUMN_PHRASE + " text ,"
            + PhraseContract.COLUMN_ORDER  + " text ,"
            + PhraseContract.COLUMN_FROM_A_LANG + " text"
    +")"
    );
    database.execSQL("INSERT INTO " +
            PhraseContract.TABLE_NAME + "("+ PhraseContract.COLUMN_ID +" , "+ PhraseContract.COLUMN_PHRASE + " , "+ PhraseContract.COLUMN_ORDER +" , "+ PhraseContract.COLUMN_FROM_A_LANG +")" +
            " SELECT " + LastId +" , "+ PhraseContract.COLUMN_PHRASE + " , "+ PhraseContract.COLUMN_ORDER +" , "+ PhraseContract.COLUMN_FROM_A_LANG +
            " FROM " + PhraseContract.TABLE_NAME + "old");
    database.execSQL("DROP TABLE " + PhraseContract.TABLE_NAME + "old");

最近我不得不在SQLite3中使用一个名为points的表,列为id lon lat。错误地,当导入表时,latitude的值存储在lon列中,反之亦然,因此一个明显的修复方法是重命名这些列。所以窍门是:

create table points_tmp as select id, lon as lat, lat as lon from points;
drop table points;
alter table points_tmp rename to points;

我希望这对你有用!