我想在sqlite数据库中删除或添加列
我正在使用以下查询删除列。
ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME
但它会产生错误
System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error
我想在sqlite数据库中删除或添加列
我正在使用以下查询删除列。
ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME
但它会产生错误
System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error
当前回答
基于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)
这个脚本首先创建随机的临时表,并只插入必要列的数据,除了将要删除的列。然后根据临时表恢复原表,删除临时表。
其他回答
在SQLite 3中不能删除特定的列。参见FAQ。
我重写了@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();
}
}
http://www.sqlite.org/lang_altertable.html
正如您在图中看到的,只支持ADD COLUMN。不过,有一个(有点沉重的)变通方法:http://www.sqlite.org/faq.html#q11
有一段时间不直接支持这一点,您需要遵循以下四个步骤:(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 3.2.0在2005-03-21添加了ALTER TABLE ADD COLUMN
请注意,仍然有一些边缘情况下,这些可能不起作用,例如,您不能删除主键列。有关详细信息,请参阅文档。当这些ALTER TABLE…COLUMN语句不起作用时,您可以退回到四步过程。
顺便说一下,文档中的四步流程实际上是十二步流程。但其中四个步骤非常重要,很容易出错,在这些文档中特别指出。
您可以使用SQlite管理员更改列名。 右键单击表名并选择编辑表。在这里你可以找到表的结构,你可以很容易地重命名它。