如何重命名SQLite数据库表中的列？

本文翻译自：How do I rename a column in a SQLite database table?

I would need to rename a few columns in some tables in a SQLite database. 我需要在SQLite数据库的某些表中重命名一些列。 I know that a similar question has been asked on stackoverflow previously, but it was for SQL in general, and the case of SQLite was not mentioned. 我知道以前在stackoverflow上也曾提出过类似的问题 ，但通常是针对SQL，而没有提到SQLite的情况。

From the SQLite documentation for ALTER TABLE , I gather that it's not possible to do such a thing "easily" (ie a single ALTER TABLE statement). 从ALTER TABLE的SQLite文档中，我发现不可能“轻松”地执行此操作（即，单个ALTER TABLE语句）。

I was wondering someone knew of a generic SQL way of doing such a thing with SQLite. 我想知道有人知道使用SQLite做这种事情的通用SQL方法。

---

#1楼

参考：https://stackoom.com/question/3nvJ/如何重命名SQLite数据库表中的列

---

#2楼

As mentioned before, there is a tool SQLite Database Browser, which does this. 如前所述，有一个工具SQLite数据库浏览器可以做到这一点。 Lyckily, this tool keeps a log of all operations performed by the user or the application. 幸运的是，该工具保留了用户或应用程序执行的所有操作的日志。 Doing this once and looking at the application log, you will see the code involved. 这样做一次并查看应用程序日志，您将看到所涉及的代码。 Copy the query and paste as required. 复制查询并根据需要粘贴。 Worked for me. 为我工作。 Hope this helps 希望这可以帮助

---

#3楼

First off, this is one of those things that slaps me in the face with surprise: renaming of a column requires creating an entirely new table and copying the data from the old table to the new table... 首先，这是让我大吃一惊的事情之一：重命名列需要创建一个全新的表并将数据从旧表复制到新表...

The GUI I've landed on to do SQLite operations is Base . 我登陆进行SQLite操作的GUI是Base 。 It's got a nifty Log window that shows all the commands that have been executed. 它有一个漂亮的Log窗口，显示所有已执行的命令。 Doing a rename of a column via Base populates the log window with the necessary commands: 通过Base重命名一列将使用必要的命令填充日志窗口：

These can then be easily copied and pasted where you might need them. 然后可以轻松地将它们复制并粘贴到您可能需要的位置。 For me, that's into an ActiveAndroid migration file. 对我来说，这是一个ActiveAndroid迁移文件。 A nice touch, as well, is that the copied data only includes the SQLite commands, not the timestamps, etc. 同样令人高兴的是，复制的数据仅包含SQLite命令，而不包含时间戳等。

Hopefully, that saves some people time. 希望可以节省一些时间。

---

#4楼

sqlite3 yourdb .dump > /tmp/db.txt sqlite3 yourdb .dump> /tmp/db.txt
edit /tmp/db.txt change column name in Create line 编辑/tmp/db.txt更改“创建”行中的列名称
sqlite2 yourdb2 < /tmp/db.txt sqlite2 yourdb2 </tmp/db.txt
mv/move yourdb2 yourdb mv /移动yourdb2 yourdb

---

#5楼

change table column < id > to < _id > 将表列<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");

---

#6楼

Create a new column with the desired column name: COLNew. 用所需的列名称创建一个新列：COLNew。

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

Copy contents of old column COLOld to new column COLNew. 将旧列COLOld的内容复制到新列COLNew。

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

Note: brackets are necessary in above line. 注意：以上行中必须有括号。