Я хочу удалить или добавить столбец в базе данных sqlite
Я использую следующий запрос для удаления столбца.
ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME
Но это дает ошибку
System.Data.SQLite.SQLiteException: SQLite error
near "DROP": syntax error
SQLite поддерживает ограниченное подмножество ALTER TABLE. Команда ALTER TABLE в SQLite позволяет пользователю переименовать таблицу или добавить новый столбец в существующую таблицу. Невозможно переименовать столбец, удалить столбец или добавить или удалить ограничения из таблицы.
Вы можете:
Я написал реализацию Java на основе рекомендованного Sqlite способа сделать это:
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 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
Теперь вы хотите удалить столбец 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
);
http://www.sqlite.org/lang_altertable.html
Как вы можете видеть на диаграмме, поддерживается только ADD COLUMN. Существует (более тяжелое) обходное решение: http://www.sqlite.org/faq.html#q11
Мы не можем удалить определенный столбец в 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 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();
}
}
Как отмечали другие, оператор sqlite ALTER TABLE не поддерживает DROP COLUMN, а стандартный рецепт для этого не сохраняет ограничения и индексы.
Здесь приведен код python для этого в общем случае, сохраняя все ключевые ограничения и индексы.
Пожалуйста, создайте резервную копию своей базы данных перед использованием!. Эта функция основана на вращении оригинального оператора CREATE TABLE и потенциально немного небезопасна - например, он сделает неправильную вещь, если идентификатор содержит встроенную запятой или скобками.
Если кто-то хочет внести свой вклад в лучший способ анализа SQL, это будет здорово!
UPDATE Я нашел лучший способ разобраться с использованием открытого пакета 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 имеет ограниченную поддержку ALTER TABLE, вы можете использовать только столбец ADD в конце таблицы ИЛИ CHANGE TABLE_NAME в SQLite.
Вот лучший ответ: КАК УДАЛИТЬ КОЛОНКУ ОТ SQLITE?
посетить Удалить столбец из таблицы SQLite
Реализация в Python основана на информации по адресу http://www.sqlite.org/faq.html#q11.
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. Однако вы можете добавить дополнительный столбец, используя запрос таблицы ALTER.
Вы можете использовать SQlite Administrator для изменения имен столбцов. Щелкните правой кнопкой мыши на имени таблицы и выберите "Редактировать таблицу". Здесь вы найдете структуру таблицы, и вы можете легко переименовать ее.
В качестве альтернативы:
Если у вас есть таблица со схемой
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:
fooobar.com/questions/38851/...
Вы можете сбросить базу данных, как описано в приведенной выше ссылке, затем захватить инструкцию "create table" и шаблон "insert" из этого дампа, а затем следовать инструкциям в записи SQLite FAQ "Как добавлять или удалять столбцы из существующей таблицы в SQLite". (FAQ связан в другом месте на этой странице.)
вы можете использовать Sqlitebrowser. В режиме браузера для соответствующей базы данных и таблицы в структуре tab -database, после опции "Изменить таблицу", соответствующий столбец можно удалить.
пример добавления столбца: -
alter table student add column TOB time;
здесь ученик - это имя_таблицы и TOB - это имя_столбца, которое нужно добавить.
Он работает и тестируется.
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");
Мое решение, нужно только вызвать этот метод.
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, и теперь он может сохранять тип столбца, первичный ключ, значение по умолчанию и т.д.
private static void dropColumn(SupportSQLiteDatabase database, String tableName, List<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.query(query);
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.SupportSQLiteDatabase, но вы можете легко изменить его для использования android.database.sqlite.SQLiteDatabase
Браузер БД для SQLite позволяет добавлять или удалять столбцы.
Теперь вы можете использовать браузер БД для SQLite для управления столбцами