如何在SQLITE中删除或添加列?

小开

最佳答案

ALTER TABLE SQLite

SQLite支持ALTER TABLE的一个有限子集。SQLite中的ALTER TABLE命令允许用户重命名表或向现有表添加新列。不能重命名列、删除列或从表中添加或删除约束。

您可以:

创建新表作为您要更改的表，
复制所有数据，
丢掉旧桌子，
重新命名新文件。

小开

http://www.sqlite.org/lang_altertable.html

正如您在图中看到的，只支持ADD COLUMN。不过，有一个(有点沉重的)变通方法:http://www.sqlite.org/faq.html#q11

小开

我根据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/

小开

在SQLite 3中不能删除特定的列。参见常见问题解答。

小开

您可以使用SQlite管理员修改列名。右键单击表名并选择编辑表。在这里你可以找到表的结构，你可以很容易地重命名它

小开

由于SQLite对ALTER TABLE的支持有限，所以你只能在表的末尾添加列或在SQLite中更改TABLE_NAME。

这里是如何从SQLITE删除列的最佳答案?

访问从SQLite表删除列

小开

正如其他人所指出的，sqlite的ALTER TABLE语句不支持DROP COLUMN，并且这样做的标准配方并不保留约束&指数。

这里有一些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

小开

正如其他人指出的那样

不能重命名列，删除列，或添加或
.删除表中的约束

来源: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
);

小开

作为替代:

如果有一个带模式的表

CREATE TABLE person(
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER,
height INTEGER
);

你可以使用CREATE TABLE...AS语句，如CREATE TABLE person2 AS SELECT id, first_name, last_name, age FROM person;，即去掉你不想要的列。然后删除原来的person表并重命名新表。

注意，此方法生成的表没有PRIMARY KEY，也没有约束。为了保存这些表，可以使用其他人描述的方法来创建一个新表，或者使用临时表作为中间对象。

小开

这个不同问题的答案是面向修改列的，但我相信，如果你有很多列，并且不想为INSERT语句手动重新键入大部分列，部分答案也可以产生有用的方法:

https://stackoverflow.com/a/10385666 < a href = " https://stackoverflow.com/a/10385666 " > < / >

您可以按照上面链接中描述的方法转储数据库，然后从该转储中获取“create table”语句和“insert”模板，然后按照SQLite FAQ条目“如何从SQLite中现有的表中添加或删除列”中的说明进行操作。(常见问题在本页其他地方有链接。)

小开

你可以使用Sqlitebrowser。在浏览器模式下，对于各自的数据库和表，在选项卡-database结构下，选择Modify table后，可以删除各自的列。

小开

我重写了@Udinic回答，以便代码生成表创建查询自动。它也不需要ConnectionSource。它也必须在事务中这样做。

public static String getOneTableDbSchema(SQLiteDatabase db, String tableName) {
Cursor c = db.rawQuery(
"SELECT * FROM `sqlite_master` WHERE `type` = 'table' AND `name` = '" + tableName + "'", null);
String result = null;
if (c.moveToFirst()) {
result = c.getString(c.getColumnIndex("sql"));
}
c.close();
return result;
}


public List<String> getTableColumns(SQLiteDatabase db, String tableName) {
ArrayList<String> columns = new ArrayList<>();
String cmd = "pragma table_info(" + tableName + ");";
Cursor cur = db.rawQuery(cmd, null);


while (cur.moveToNext()) {
columns.add(cur.getString(cur.getColumnIndex("name")));
}
cur.close();


return columns;
}


private void dropColumn(SQLiteDatabase db, String tableName, String[] columnsToRemove) {
db.beginTransaction();
try {
List<String> columnNamesWithoutRemovedOnes = getTableColumns(db, tableName);
// Remove the columns we don't want anymore from the table's list of columns
columnNamesWithoutRemovedOnes.removeAll(Arrays.asList(columnsToRemove));


String newColumnNamesSeparated = TextUtils.join(" , ", columnNamesWithoutRemovedOnes);
String sql = getOneTableDbSchema(db, tableName);
// Extract the SQL query that contains only columns
String oldColumnsSql = sql.substring(sql.indexOf("(")+1, sql.lastIndexOf(")"));


db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
db.execSQL("CREATE TABLE `" + tableName + "` (" + getSqlWithoutRemovedColumns(oldColumnsSql, columnsToRemove)+ ");");
db.execSQL("INSERT INTO " + tableName + "(" + newColumnNamesSeparated + ") SELECT " + newColumnNamesSeparated + " FROM " + tableName + "_old;");
db.execSQL("DROP TABLE " + tableName + "_old;");
db.setTransactionSuccessful();
} catch {
//Error in between database transaction
} finally {
db.endTransaction();
}




}

小开

您现在还可以使用DB浏览器为SQLite操作列

小开

public void DeleteColFromTable(String DbName, String TableName, String ColName){
SQLiteDatabase db = openOrCreateDatabase(""+DbName+"", Context.MODE_PRIVATE, null);
db.execSQL("CREATE TABLE IF NOT EXISTS "+TableName+"(1x00dff);");
Cursor c = db.rawQuery("PRAGMA table_info("+TableName+")", null);
if (c.getCount() == 0) {


} else {
String columns1 = "";
String columns2 = "";
while (c.moveToNext()) {
if (c.getString(1).equals(ColName)) {
} else {
columns1 = columns1 + ", " + c.getString(1) + " " + c.getString(2);
columns2 = columns2 + ", " + c.getString(1);
}
if (c.isLast()) {
db.execSQL("CREATE TABLE IF NOT EXISTS DataBackup (" + columns1 + ");");
db.execSQL("INSERT INTO DataBackup SELECT " + columns2 + " FROM "+TableName+";");
db.execSQL("DROP TABLE "+TableName+"");
db.execSQL("ALTER TABLE DataBackup RENAME TO "+TableName+";");
}
}
}
}

只需要调用一个方法

DeleteColFromTable("Database name","Table name","Col name which want to delete");

小开

基于http://www.sqlite.org/faq.html#q11中的信息在Python中实现。

import sqlite3 as db
import random
import string


QUERY_TEMPLATE_GET_COLUMNS = "PRAGMA table_info(@table_name)"
QUERY_TEMPLATE_DROP_COLUMN = """
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE @tmp_table(@columns_to_keep);
INSERT INTO @tmp_table SELECT @columns_to_keep FROM @table_name;
DROP TABLE @table_name;
CREATE TABLE @table_name(@columns_to_keep);
INSERT INTO @table_name SELECT @columns_to_keep FROM @tmp_table;
DROP TABLE @tmp_table;
COMMIT;
"""


def drop_column(db_file, table_name, column_name):
con = db.connect(db_file)
QUERY_GET_COLUMNS = QUERY_TEMPLATE_GET_COLUMNS.replace("@table_name", table_name)
query_res = con.execute(QUERY_GET_COLUMNS).fetchall()
columns_list_to_keep = [i[1] for i in query_res if i[1] != column_name]
columns_to_keep = ",".join(columns_list_to_keep)
tmp_table = "tmp_%s" % "".join(random.sample(string.ascii_lowercase, 10))
QUERY_DROP_COLUMN = QUERY_TEMPLATE_DROP_COLUMN.replace("@table_name", table_name)\
.replace("@tmp_table", tmp_table).replace("@columns_to_keep", columns_to_keep)
con.executescript(QUERY_DROP_COLUMN)
con.close()


drop_column(DB_FILE, TABLE_NAME, COLUMN_NAME)

这个脚本首先创建随机的临时表，并只插入必要列的数据，除了将要删除的列。然后根据临时表恢复原表，删除临时表。

小开

我的解，只需要调用这个方法。

public static void dropColumn(SQLiteDatabase db, String tableName, String[] columnsToRemove) throws java.sql.SQLException {
List<String> updatedTableColumns = getTableColumns(db, tableName);
updatedTableColumns.removeAll(Arrays.asList(columnsToRemove));
String columnsSeperated = TextUtils.join(",", updatedTableColumns);


db.execSQL("ALTER TABLE " + tableName + " RENAME TO " + tableName + "_old;");
db.execSQL("CREATE TABLE " + tableName + " (" + columnsSeperated + ");");
db.execSQL("INSERT INTO " + tableName + "(" + columnsSeperated + ") SELECT "
+ columnsSeperated + " FROM " + tableName + "_old;");
db.execSQL("DROP TABLE " + tableName + "_old;");
}

辅助方法获取列:

public static List<String> getTableColumns(SQLiteDatabase db, String tableName) {
ArrayList<String> columns = new ArrayList<>();
String cmd = "pragma table_info(" + tableName + ");";
Cursor cur = db.rawQuery(cmd, null);


while (cur.moveToNext()) {
columns.add(cur.getString(cur.getColumnIndex("name")));
}
cur.close();


return columns;
}

小开

我已经改进了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();
}
}

注:我在这里使用了android.arch.persistence.db.SupportSQLiteDatabase，但你可以很容易地修改它以使用android.database.sqlite.SQLiteDatabase

小开

我猜您想做的是数据库迁移。在SQLite中不存在“删除”列。但是，您可以使用ALTER表查询添加一个额外的列。

小开

DB浏览器的SQLite允许你添加或删除列。

在主视图中，选项卡数据库结构，单击表名。一个按钮修改表被启用，它会打开一个新窗口，你可以在其中选择列/字段并删除它。

小开

Kotlin解决方案，基于在这里，但也:

确保临时表不存在
修复了检查默认值的类型，因为当它是Integer时返回String类型(关于此问题报告这里<强> < / >强)。
避免在希望删除的列不存在时执行任何操作。

object DbUtil {
/** https://stackoverflow.com/a/51587449/878126 */
@JvmStatic
fun dropColumns(database: SQLiteDatabase, tableName: String,
columnsToRemove: Collection<String>) {
val columnNames: MutableList<String> = ArrayList()
val columnNamesWithType: MutableList<String> = ArrayList()
val primaryKeys: MutableList<String> = ArrayList()
val query = "pragma table_info($tableName);"
val cursor = database.rawQuery(query, null)
val columnDefaultIndex = cursor.getColumnIndex("dflt_value")
val columnNameIndex = cursor.getColumnIndex("name")
val columnTypeIndex = cursor.getColumnIndex("type")
val columnNotNullIndex = cursor.getColumnIndex("notnull")
val columnPrimaryKeyIndex = cursor.getColumnIndex("pk")
val sb = StringBuilder()
var foundColumnsToRemove = false
while (cursor.moveToNext()) {
val columnName = cursor.getString(columnNameIndex)
if (columnsToRemove.contains(columnName)) {
foundColumnsToRemove = true
continue
}
val columnType = cursor.getString(columnTypeIndex)
val isNotNull = cursor.getInt(columnNotNullIndex) == 1
val isPrimaryKey = cursor.getInt(columnPrimaryKeyIndex) == 1
columnNames.add(columnName)
sb.clear()
sb.append("`$columnName` $columnType ")
if (isNotNull)
sb.append(" NOT NULL ")
if (cursor.getType(columnDefaultIndex) != Cursor.FIELD_TYPE_NULL) {
//has default value
when (columnType.uppercase()) {
"INTEGER" -> sb.append(" DEFAULT ${cursor.getInt(columnDefaultIndex)} ")
"TEXT" -> sb.append(" DEFAULT \"${cursor.getString(columnDefaultIndex)}\" ")
"REAL" -> sb.append(" DEFAULT ${cursor.getFloat(columnDefaultIndex)} ")
}
}
columnNamesWithType.add(sb.toString())
if (isPrimaryKey)
primaryKeys.add("`$columnName`")
}
cursor.close()
if (!foundColumnsToRemove)
return
val columnNamesSeparated = TextUtils.join(", ", columnNames)
if (primaryKeys.size > 0)
columnNamesWithType.add("PRIMARY KEY(${TextUtils.join(", ", primaryKeys)})")
val columnNamesWithTypeSeparated = TextUtils.join(", ", columnNamesWithType)
database.beginTransaction()
try {
var newTempTableName: String
var counter = 0
while (true) {
newTempTableName = "${tableName}_old_$counter"
if (!isTableExists(database, newTempTableName))
break
++counter
}
database.execSQL("ALTER TABLE $tableName RENAME TO $newTempTableName;")
database.execSQL("CREATE TABLE $tableName ($columnNamesWithTypeSeparated);")
database.execSQL(
"INSERT INTO $tableName ($columnNamesSeparated) SELECT $columnNamesSeparated FROM $newTempTableName;")
database.execSQL("DROP TABLE ${newTempTableName};")
database.setTransactionSuccessful()
} finally {
database.endTransaction()
}
}


@JvmStatic
fun isTableExists(database: SQLiteDatabase, tableName: String): Boolean {
database.rawQuery(
"select DISTINCT tbl_name from sqlite_master where tbl_name = '$tableName'", null)
?.use {
return it.count > 0
} ?: return false
}
}

小开

SQLite 3.35.0引入了对ALTER TABLE DROP COLUMN的支持。

ALTER TABLE

DROP COLUMN语法用于从表中删除现有列。 DROP COLUMN命令从表中删除指定的列，并重写整个表以清除与该列相关的数据。DROP COLUMN命令仅在列未被模式的任何其他部分引用、不是PRIMARY KEY且没有UNIQUE约束时才有效。

下面的语法是有效的:

ALTER TABLE <TABLENAME> DROP COLUMN <COLUMNNAME>;
ALTER TABLE <TABLENAME> DROP <COLUMNNAME>;

小开

有一段时间不直接支持，你需要遵循以下四个步骤:(1)创建一个temporary_table，(2)复制数据，(3)删除旧表，然后(4)重命名temporary_table。

但现在这些功能是支持，所有你需要做的是升级SQLite。

3.35.0 from 2021-03-12添加ALTER TABLE DROP COLUMN
3.25.0 from 2018-09-15添加了ALTER TABLE RENAME COLUMN
从2005-03-21开始的3.2.0添加了ALTER TABLE ADD COLUMN

请注意，仍然有一些边缘情况下，这些可能不起作用，例如，您不能删除主键列。有关详细信息，请参阅文档。当这些ALTER TABLE … COLUMN语句不起作用时，您可以退回到四步过程。

顺便说一下，文档中的四步流程实际上是十二步流程。但其中四个步骤非常重要，很容易出错，在这些文档中特别指出。