我想在sqlite数据库中删除或添加列
我正在使用以下查询删除列。
ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME
但它会产生错误
System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error
当前回答
正如其他人指出的那样
不能重命名列、删除列或添加或 从表中删除约束。
来源: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
现在要从该表中删除列的高度。
创建另一个名为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
);
其他回答
基于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)
这个脚本首先创建随机的临时表，并只插入必要列的数据，除了将要删除的列。然后根据临时表恢复原表，删除临时表。
正如其他人指出的那样
不能重命名列、删除列或添加或 从表中删除约束。
来源: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
现在要从该表中删除列的高度。
创建另一个名为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
);
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
}
}
有一段时间不直接支持这一点，您需要遵循以下四个步骤:(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中不存在“删除”列。但是，您可以使用ALTER表查询添加一个额外的列。
推荐文章
- 如何转储一些SQLite3表的数据?
- Mac SQLite编辑器
- SQLite:如何将查询结果保存为CSV文件?
- SQLite中“如果不存在，请插入”语句
- 转义单引号字符用于SQLite查询
- 如何创建默认值“现在”的时间戳列?
- 如何检索插入id后插入行在SQLite使用Python?
- 创建存储过程和SQLite?
- SQLite并发访问
- 如何连接字符串与填充在sqlite
- 在Android SQLite中处理日期的最佳方法
- SQLite数据库文件使用什么扩展名重要吗?
- 无法加载DLL 'SQLite.Interop.dll'
- 快速简单的方法迁移SQLite3到MySQL?
- 在Ubuntu上安装sqlite3-ruby错误
