我希望能够在SQLite数据库中添加或删除列。
我正在使用以下查询来删除列。
ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME
但是它会报错
System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error
如何在SQLITE中删除或添加列？
334
- Sandip
2
3可能是从SQLite表中删除列的重复问题。 - MeirDayan
11接受的答案已过时，请考虑接受Lukasz Szozda的答案，网址为https://dev59.com/UGoy5IYBdhLWcg3wl_Mx#66399224。只看接受的排名第一的答案，我差点错过了这个方法。 - Magnus
23个回答
408
SQLite仅支持有限的ALTER TABLE子集。在SQLite中，ALTER TABLE命令允许用户重命名表或向现有表添加新列。无法重命名列、删除列或向表添加或删除约束。
您可以：
- 创建一个新表，与您想要更改的表相同,
- 复制所有数据,
- 删除旧表,
- 重命名新表。
- Michał Powaga
10
62https://dev59.com/pW025IYBdhLWcg3wjGtO#5987838 给出了执行该任务的基本示例。 - bikram990
6在执行这个序列之前，如果有外部表引用了这个表，必须先调用
PRAGMA foreign_keys=OFF。在执行完这个序列后，为了重新启用外键，必须调用PRAGMA foreign_keys=ON。 - PazO2在更新的SQLite版本中，支持“RENAME COLUMN”。https://www.sqlite.org/releaselog/3_25_0.html - Grogs
3这个答案已经不正确了，应该被编辑或删除。 - Wojciech Domalewski
显示剩余5条评论
90
SQLite 3.35.0引入了ALTER TABLE DROP COLUMN支持。
DROP COLUMN语法用于从表中删除现有列。 DROP COLUMN命令从表中删除指定的列，并重写整个表以清除与该列相关联的数据。 DROP COLUMN命令仅在该列未被模式的任何其他部分引用且不是PRIMARY KEY并且没有UNIQUE约束时才起作用。
以下语法将是有效的：
ALTER TABLE <TABLENAME> DROP COLUMN <COLUMNNAME>;
ALTER TABLE <TABLENAME> DROP <COLUMNNAME>;
- Lukasz Szozda
5
4酷，但是这种语法什么时候能在安卓上被接受？ - Sergio
3谢谢Lukasz。我们将不得不等待安卓赶上来...也许需要十年时间。 - Sergio
2在此处，您可以了解Android API级别使用的Sqlite版本：https://developer.android.com/reference/android/database/sqlite/package-summary - Homayoon Ahmadi
2根据文档中提到的sqlite版本，我们不能在Android设备上使用这个语法！！ - Homayoon Ahmadi
48
我已经按照Sqlite推荐的方式编写了一个基于Java的实现：
private void dropColumn(SQLiteDatabase db,
ConnectionSource connectionSource,
String createTableCmd,
String tableName,
String[] colsToRemove) throws java.sql.SQLException {
List<String> updatedTableColumns = getTableColumns(tableName);
// Remove the columns we don't want anymore from the table's list of columns
updatedTableColumns.removeAll(Arrays.asList(colsToRemove));
String columnsSeperated = TextUtils.join(",", updatedTableColumns);
db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
// Creating the table on its new format (no redundant columns)
db.execSQL(createTableCmd);
// Populating the table with the data
db.execSQL("INSERT INTO " + tableName + "(" + columnsSeperated + ") SELECT "
+ columnsSeperated + " FROM " + tableName + "_old;");
db.execSQL("DROP TABLE " + tableName + "_old;");
}
为了获取表格的列，我使用了 "PRAGMA table_info"：
public List<String> getTableColumns(String tableName) {
ArrayList<String> columns = new ArrayList<String>();
String cmd = "pragma table_info(" + tableName + ");";
Cursor cur = getDB().rawQuery(cmd, null);
while (cur.moveToNext()) {
columns.add(cur.getString(cur.getColumnIndex("name")));
}
cur.close();
return columns;
}
我在我的博客上写了关于它的内容，您可以在这里看到更多解释：
http://udinic.wordpress.com/2012/05/09/sqlite-drop-column-support/
- Udinic
7
1这个速度对于大数据表来说还是相当慢的，不是吗？ - Joran Beasley
2最好在单个事务中完成此操作，而不是允许其他代码在过渡状态下查看事物。 - Donal Fellows
通常我会在升级数据库时运行这段代码，以确保其他代码不会同时运行。您可以创建一个事务，并在其中执行所有这些命令。 - Udinic
3我相信如果你使用这个解决方案，结果表中的列将完全裸露 - 没有类型信息、主键、外键、默认值、唯一约束或检查约束将保留。 它导入到新表中的只有列名。此外，由于它在运行之前不会禁用外键，因此其他表中的数据也可能会出问题。 - ACK_stoverflow
4除了使用
INSERT语句插入数据之外，你还可以通过执行"CREAT TABLE" + tableName + "AS SELECT " + columnsSeperated + " FROM " + tableName + "_old;"的方式创建新表。 - Robert显示剩余2条评论
36
正如其他人所指出的那样
无法重命名列、删除列或添加/删除表的约束。
来源：http://www.sqlite.org/lang_altertable.html
虽然您可以始终创建一个新表，然后删除旧表。我将尝试通过一个示例来说明这个解决方法。
sqlite> .schema
CREATE TABLE person(
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER,
height INTEGER
);
sqlite> select * from person ;
id first_name last_name age height
---------- ---------- ---------- ---------- ----------
0 john doe 20 170
1 foo bar 25 171
现在您想从这张表中删除列height。
创建另一张名为new_person的表。
sqlite> CREATE TABLE new_person(
...> id INTEGER PRIMARY KEY,
...> first_name TEXT,
...> last_name TEXT,
...> age INTEGER
...> ) ;
sqlite>
sqlite> INSERT INTO new_person
...> SELECT id, first_name, last_name, age FROM person ;
sqlite> select * from new_person ;
id first_name last_name age
---------- ---------- ---------- ----------
0 john doe 20
1 foo bar 25
sqlite>
现在删除 person 表，并将 new_person 重命名为 person
sqlite> DROP TABLE IF EXISTS person ;
sqlite> ALTER TABLE new_person RENAME TO person ;
sqlite>
现在，如果您执行.schema命令，您将看到
sqlite>.schema
CREATE TABLE "person"(
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER
);
- Tasdik Rahman
4
外部引用怎么办？如果您删除了某个表，而其他表正在使用它，Oracle会发出警告。 - Leos Literak
7我可以看出你是一位真正的程序员。在使用了"John Doe"之后，你直接使用了"foo bar" :) - msouth
3创建一个名为new_person的表，其中包含person表中id、first_name、last_name和age列的数据。 - Clay
24
SQLite数据库浏览器 允许您添加或删除列。
在主视图中的 Database Structure 选项卡中，单击表名。一个名为 Modify Table 的按钮将会启用，点击它会打开一个新窗口，您可以在其中选择要删除的列/字段。
- rams
4
我必须说，如果您只使用单个数据库并且可以直接复制它，则这是最简单的方法。+1 - Lionel Yeo
1运行得非常好，没有任何麻烦。只需记住在最后点击“文件”->“写入更改”即可。 - Marcin
3如果底层的sqlite3引擎不支持“ALTER TABLE table_name DROP COLUMN column_name”，我想知道它会如何运作。 - robertspierre
2在幕后，DB Browser for SQLite使用与原始表相同的创建临时表/插入到临时表/删除原始表的技术。外键验证通过PRAGMA defer_foreign_keys进行延迟。 - L. Yan
17
曾经，SQLite并不直接支持此功能。您需要按照四个步骤操作：（1）创建临时表，（2）复制数据，（3）删除旧表，然后（4）重命名临时表。
但现在这些特性已经得到支持，您只需要升级SQLite即可。
- 于2021-03-12发布的版本 3.35.0 增加了
ALTER TABLE DROP COLUMN - 于2018-09-15发布的版本 3.25.0 添加了
ALTER TABLE RENAME COLUMN - 于2005-03-21发布的版本 3.2.0 添加了
ALTER TABLE ADD COLUMN
请注意，仍然存在一些边缘情况可能不起作用，例如，您无法删除主键列。有关详细信息，请参阅文档。当这些ALTER TABLE … COLUMN语句不起作用时，您可以回退到四个步骤的方法。
顺便说一下，文档中的四步过程实际上是12个步骤。但其中有4个步骤非常重要，容易出错，并且在文档中特别提到。
- andrewdotn
14
http://www.sqlite.org/lang_altertable.html
如图所示，仅支持ADD COLUMN。但是有一个（有点复杂的）解决方法：http://www.sqlite.org/faq.html#q11
- LeleDumbo
1
这个“变通方法”似乎并不正确。你会失去列类型、约束、索引等等。 - Yukio Usuzumi
3
我改进了user2638929的回答，现在它可以保留列类型、主键、默认值等。
public static void dropColumns(SQLiteDatabase database, String tableName, Collection<String> columnsToRemove){
List<String> columnNames = new ArrayList<>();
List<String> columnNamesWithType = new ArrayList<>();
List<String> primaryKeys = new ArrayList<>();
String query = "pragma table_info(" + tableName + ");";
Cursor cursor = database.rawQuery(query,null);
while (cursor.moveToNext()){
String columnName = cursor.getString(cursor.getColumnIndex("name"));
if (columnsToRemove.contains(columnName)){
continue;
}
String columnType = cursor.getString(cursor.getColumnIndex("type"));
boolean isNotNull = cursor.getInt(cursor.getColumnIndex("notnull")) == 1;
boolean isPk = cursor.getInt(cursor.getColumnIndex("pk")) == 1;
columnNames.add(columnName);
String tmp = "`" + columnName + "` " + columnType + " ";
if (isNotNull){
tmp += " NOT NULL ";
}
int defaultValueType = cursor.getType(cursor.getColumnIndex("dflt_value"));
if (defaultValueType == Cursor.FIELD_TYPE_STRING){
tmp += " DEFAULT " + "\"" + cursor.getString(cursor.getColumnIndex("dflt_value")) + "\" ";
}else if(defaultValueType == Cursor.FIELD_TYPE_INTEGER){
tmp += " DEFAULT " + cursor.getInt(cursor.getColumnIndex("dflt_value")) + " ";
}else if (defaultValueType == Cursor.FIELD_TYPE_FLOAT){
tmp += " DEFAULT " + cursor.getFloat(cursor.getColumnIndex("dflt_value")) + " ";
}
columnNamesWithType.add(tmp);
if (isPk){
primaryKeys.add("`" + columnName + "`");
}
}
cursor.close();
String columnNamesSeparated = TextUtils.join(", ", columnNames);
if (primaryKeys.size() > 0){
columnNamesWithType.add("PRIMARY KEY("+ TextUtils.join(", ", primaryKeys) +")");
}
String columnNamesWithTypeSeparated = TextUtils.join(", ", columnNamesWithType);
database.beginTransaction();
try {
database.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
database.execSQL("CREATE TABLE " + tableName + " (" + columnNamesWithTypeSeparated + ");");
database.execSQL("INSERT INTO " + tableName + " (" + columnNamesSeparated + ") SELECT "
+ columnNamesSeparated + " FROM " + tableName + "_old;");
database.execSQL("DROP TABLE " + tableName + "_old;");
database.setTransactionSuccessful();
}finally {
database.endTransaction();
}
}
PS. 我在这里使用了 android.arch.persistence.db.SupportSQLiteDatabase，但是您可以轻松地修改它以使用 android.database.sqlite.SQLiteDatabase
- Berdimurat Masaliev
4
这个很好用。也许你知道如何使用相同的通用方式重命名列吗？如果是这样，这里有一个类似的问题示例：https://dev59.com/orvoa4cB1Zd3GeqP435Q - android developer
请注意，在使用“RENAME TO”重命名表时，您应该检查要重命名的表是否已经存在。这里是带有此修复的链接：https://dev59.com/UGoy5IYBdhLWcg3wl_Mx#65718980。 - android developer
我认为
defaultValueType因某些原因变成了String类型，而不是Integer类型。最好避免使用cursor.getType(cursor.getColumnIndex("dflt_value"))。 - android developer请注意，此代码不处理外键。 - proc
3
正如其他人指出的那样，sqlite的ALTER TABLE语句不支持DROP COLUMN，并且标准的处理方法不能保留约束和索引。
以下是一些Python代码，可以通用地执行此操作，同时保留所有关键约束和索引。
请在使用前备份数据库！该函数依赖于修改原始CREATE TABLE语句，可能有点不安全 - 例如，如果标识符包含嵌入的逗号或括号，则会出现错误。
如果有人能够提供更好的解析SQL的方法，那就太好了！
更新：我找到了一种更好的解析方法，使用开源的sqlparse软件包。如果有兴趣，我会在这里发布它，只需留下评论要求即可...
以下是一些Python代码，可以通用地执行此操作，同时保留所有关键约束和索引。
请在使用前备份数据库！该函数依赖于修改原始CREATE TABLE语句，可能有点不安全 - 例如，如果标识符包含嵌入的逗号或括号，则会出现错误。
如果有人能够提供更好的解析SQL的方法，那就太好了！
更新：我找到了一种更好的解析方法，使用开源的sqlparse软件包。如果有兴趣，我会在这里发布它，只需留下评论要求即可...
import re
import random
def DROP_COLUMN(db, table, column):
columns = [ c[1] for c in db.execute("PRAGMA table_info(%s)" % table) ]
columns = [ c for c in columns if c != column ]
sql = db.execute("SELECT sql from sqlite_master where name = '%s'"
% table).fetchone()[0]
sql = format(sql)
lines = sql.splitlines()
findcol = r'\b%s\b' % column
keeplines = [ line for line in lines if not re.search(findcol, line) ]
create = '\n'.join(keeplines)
create = re.sub(r',(\s*\))', r'\1', create)
temp = 'tmp%d' % random.randint(1e8, 1e9)
db.execute("ALTER TABLE %(old)s RENAME TO %(new)s" % {
'old': table, 'new': temp })
db.execute(create)
db.execute("""
INSERT INTO %(new)s ( %(columns)s )
SELECT %(columns)s FROM %(old)s
""" % {
'old': temp,
'new': table,
'columns': ', '.join(columns)
})
db.execute("DROP TABLE %s" % temp)
def format(sql):
sql = sql.replace(",", ",\n")
sql = sql.replace("(", "(\n")
sql = sql.replace(")", "\n)")
return sql
- spam_eggs
3
它是否也维护该表的外键？ - Lasse V. Karlsen
@LasseV.Karlsen 我进行了一些测试，它应该保持外键约束，因为这些约束似乎是由表名强制执行的。 - spam_eggs
我该如何从Java中运行它？ - Leos Literak
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接