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

我想在sqlite数据库中删除或添加列

我正在使用以下查询删除列。

ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME

但它会产生错误

System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error

当前回答

我重写了@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();
    }


}

2016-12-13 16:24:30

其他回答

我重写了@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();
    }


}

2016-12-13 16:24:30

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

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

从SQLite表中删除列

2014-02-17 06:03:17

在SQLite 3中不能删除特定的列。参见FAQ。

2012-09-26 07:12:27

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

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

2011-12-09 07:07:58

DB Browser for SQLite允许您添加或删除列。

在主视图中，选择Database Structure，单击表名。一个按钮Modify Table被启用，它会打开一个新窗口，您可以在其中选择列/字段并删除它。