Как удалить или добавить столбец в SQLITE?

Я хочу удалить или добавить столбец в базе данных sqlite

Я использую следующий запрос для удаления столбца.

ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME 

Но это дает ошибку

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

ALTER TABLE SQLite

SQLite поддерживает ограниченное подмножество ALTER TABLE. Команда ALTER TABLE в SQLite позволяет пользователю переименовать таблицу или добавить новый столбец в существующую таблицу. Невозможно переименовать столбец, удалить столбец или добавить или удалить ограничения из таблицы.

Ты можешь:

  1. создайте новую таблицу как та, которую вы пытаетесь изменить,
  2. скопировать все данные,
  3. падение старой таблицы,
  4. переименуйте новый.

Я написал реализацию Java на основе рекомендованного Sqlite способом сделать это:

 private void dropColumn(SQLiteDatabase db, ConnectionSource connectionSource, String createTableCmd, String tableName, String[] colsToRemove) throws java.sql.SQLException { List 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 getTableColumns(String tableName) { ArrayList columns = new ArrayList(); 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> 

Теперь new_person таблицу 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. См. Часто задаваемые вопросы .

Как отмечали другие, оператор ALTER TABLE sqlite не поддерживает DROP COLUMN , и стандартный рецепт для этого не сохраняет ограничения и индексы.

Вот несколько кодов python для этого в целом, сохраняя все ключевые ограничения и индексы.

Перед использованием используйте резервную копию своей базы данных! Эта функция основана на вращении оригинального оператора CREATE TABLE и потенциально немного небезопасна – например, он сделает неправильную вещь, если идентификатор содержит встроенную запятую или скобки.

Если кто-то хочет внести свой вклад в лучший способ анализа SQL, это было бы здорово!

ОБНОВЛЕНИЕ Я нашел лучший способ проанализировать использование пакета sqlparse с открытым исходным 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 

Я переписал ответ @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 getTableColumns(SQLiteDatabase db, String tableName) { ArrayList 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 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(); } } 

Поскольку 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 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; , т. е. оставьте колонки, которые вы не хотите. Затем отбросьте таблицу оригиналов и переименуйте новую.

Обратите внимание, что этот метод приводит к тому, что таблица не имеет PRIMARY KEY и никаких ограничений. Чтобы сохранить их, используйте описанные методы для создания новой таблицы или используйте временную таблицу в качестве промежуточного.

Этот ответ на другой вопрос ориентирован на модификацию столбца, но я считаю, что часть ответа может также дать полезный подход, если у вас много столбцов и вы не хотите повторно использовать большинство из них вручную для своего оператора INSERT:

https://stackoverflow.com/a/10385666

Вы можете сбросить базу данных, как описано в приведенной выше ссылке, затем захватить оператор «create table» и шаблон «insert» из этого дампа, а затем следовать инструкциям в статье «Часто задаваемые вопросы SQLite» «Как добавлять или удалять столбцы из существующих таблицы в SQLite. ” (FAQ связан в другом месте на этой странице.)

вы можете использовать Sqlitebrowser. В режиме браузера для соответствующей базы данных и таблицы в структуре табуляции-базы данных, после опции «Изменить таблицу», соответствующий столбец можно удалить.

Например, чтобы добавить столбец: –

 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 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 getTableColumns(SQLiteDatabase db, String tableName) { ArrayList 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 columnsToRemove){ List columnNames = new ArrayList<>(); List columnNamesWithType = new ArrayList<>(); List 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

Вы также можете использовать браузер DB для SQLite для управления столбцами

  • Как использовать модуль sqlite3 с электроном?
  • В чем разница между связанными типами данных SQLite, такими как INT, INTEGER, SMALLINT и TINYINT?
  • Как использовать ROW_NUMBER в sqlite
  • Измените тип столбца в sqlite3
  • не может загрузить такой файл - sqlite3 / sqlite3_native (LoadError) на rubyе на рельсах
  • Каковы наилучшие практики для SQLite на Android?
  • Копирование данных из одной базы данных SQLite в другую
  • Escape символ одиночной кавычки для использования в SQLite-запросе
  • Ограничить максимальное количество строк таблицы sqlite
  • Таблица SQL со строкой «список» и таблицей SQL со строкой для каждой записи
  • База данных SQLite3 или диск заполнен / образ диска базы данных неверен
  • Interesting Posts

    Запретить свойства с нулевым значением в ASP.NET Web API

    Как resize max_allowed_packet

    CSS @media печатает проблемы с фоновым цветом;

    Замените «CopyFileFromGuestToHost» и «runProgramInGuest» на xcopy и простое выполнение, что я могу ожидать

    Приложение для проверки неработающих ссылок

    Как повторить «диапазон копирования (A1: A5) в диапазон B с преобразованием (столбец в строку)?

    Можно ли получить указатель на функцию встроенного стандартного оператора?

    Параметрированный запрос для MySQL с C #

    Требуется ли eSATA источник питания?

    Использование Spring, сопоставление с root в web.xml, статические ресурсы не найдены

    Предоставление входных / подкоманд для команды, выполняемой через SSH с помощью JSch

    Объект AudioRecord не инициализируется

    «Невозможно подключиться к реальному Gmail.com»

    Как написать функцию для общих чисел?

    Excel 2007 – «Не удается выполнить эту задачу с доступными ресурсами»

    Давайте будем гением компьютера.