我想在sqlite数据库中删除或添加列
我正在使用以下查询删除列。
ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME
但它会产生错误
System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error
当前回答
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>;
其他回答
我已经改进了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。支持sqlitedatabase，但你可以很容易地修改它使用android.database.sqlite.SQLiteDatabase
正如其他人指出的那样，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
我根据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/
正如其他人指出的那样
不能重命名列、删除列或添加或 从表中删除约束。
来源: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
}
}
推荐文章
- 创建存储过程和SQLite?
- SQLite并发访问
- 如何连接字符串与填充在sqlite
- 在Android SQLite中处理日期的最佳方法
- SQLite数据库文件使用什么扩展名重要吗?
- 无法加载DLL 'SQLite.Interop.dll'
- 快速简单的方法迁移SQLite3到MySQL?
- 在Ubuntu上安装sqlite3-ruby错误
- sqlite3。ProgrammingError:提供的绑定数量不正确。当前语句使用1，并提供了74个
- SQLite有。net / c#包装器吗?
- Android开发有什么好的ORM工具吗?
- 如何在SQLite中看到表的结构?
- Android中使用的SQLite版本?
- Core Data vs SQLite 3
- 如何在SQLITE中删除或添加列?
