Usuń kolumnę z tabeli SQLite


114

Mam problem: muszę usunąć kolumnę z mojej bazy danych SQLite. Napisałem to zapytanie

alter table table_name drop column column_name 

ale to nie działa. Proszę pomóż mi.

Odpowiedzi:


207

Od: http://www.sqlite.org/faq.html :

(11) Jak dodać lub usunąć kolumny z istniejącej tabeli w SQLite.

SQLite ma ograniczoną obsługę ALTER TABLE, której możesz użyć do dodania kolumny na końcu tabeli lub do zmiany nazwy tabeli. Jeśli chcesz wprowadzić bardziej złożone zmiany w strukturze tabeli, będziesz musiał ponownie utworzyć tabelę. Możesz zapisać istniejące dane w tabeli tymczasowej, usunąć starą tabelę, utworzyć nową, a następnie skopiować dane z powrotem z tabeli tymczasowej.

Na przykład załóżmy, że masz tabelę o nazwie „t1” z nazwami kolumn „a”, „b” i „c” oraz że chcesz usunąć kolumnę „c” z tej tabeli. Poniższe kroki ilustrują, jak można to zrobić:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

8
+ Zawsze czytaj dokumentację SQLite. W przypadku błędów zauważysz zbyt wiele ograniczeń i różnic w gramatyce SQL. Dokumentacja SQLite jest bardzo łatwa do zrozumienia. Nie martw się tym.
AhmetB - Google

2
Po usunięciu kolumn ze względów bezpieczeństwa należy wykonać polecenie VACUUM; bez odkurzania plik bazy danych nadal zawiera dane usuniętych kolumn.
jj1bdx

@ jj1bdx Nie sądzę, aby nadal zawierał dane, ale „niewykorzystane miejsce na dysku jest dodawane do wewnętrznej„ wolnej listy ”i jest ponownie wykorzystywane przy następnym włożeniu danych. Miejsce na dysku nie jest tracone. Ale też nie jest wrócił do systemu operacyjnego. ” cytat z witryny sqlite3.
Guilherme Salomé

Ponieważ użyłem usuwania wielu kolumn w jednej transakcji, zadziałało to tylko wtedy, gdy usunąłem się TEMPORARYz CREATE TABLE.
efemeryczne

Oto moja implementacja wykorzystująca QSqlQuery Qt: gist.github.com/ephemerr/568d0d41bc389ec78f9fb7d1f015a82a
efemer

56

Zamiast upuszczać tabelę kopii zapasowych, po prostu zmień jej nazwę ...

BEGIN TRANSACTION;
CREATE TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
ALTER TABLE t1_backup RENAME TO t1;
COMMIT;

6
Nie będzie działać, jeśli masz podłączony klucz zewnętrzny t1.
efemeryczny

39

Dla uproszczenia, dlaczego nie utworzyć tabeli kopii zapasowej na podstawie instrukcji select?

CREATE TABLE t1_backup AS SELECT a, b FROM t1;
DROP TABLE t1;
ALTER TABLE t1_backup RENAME TO t1;

3
Wydaje się, że takie podejście zachowuje typy danych w kolumnach, podczas gdy coś w rodzaju zaakceptowanej odpowiedzi wydaje się powodować, że wszystkie kolumny są typu TEXT.
Uwe Keim

2
Oświadczenie to powinno być również zawarte w transakcji.
Georg Schölly

10
Zauważ, że nie zachowuje to klucza podstawowego, a sqlite nie obsługuje zmiany tabel w celu dodania klucza podstawowego. Więc jeśli klucz główny jest ważny, nie powinieneś tego używać
Tim

2
To również nie zachowuje NOT NULL.
FutureShocked

zaakceptowana odpowiedź działa poprawnie. Podczas tworzenia tabeli należy określić typy danych. Westchnienie.
John Lord

8

Ta opcja działa tylko wtedy, gdy możesz otworzyć DB w przeglądarce DB, takiej jak przeglądarka DB dla SQLite .

W przeglądarce DB dla SQLite:

  1. Przejdź do zakładki „Struktura bazy danych”
  2. Wybierz tabelę Wybierz Modyfikuj tabelę (tuż pod zakładkami)
  3. Wybierz kolumnę, którą chcesz usunąć
  4. Kliknij Usuń pole i kliknij OK

3

=> Utwórz nową tabelę bezpośrednio za pomocą następującego zapytania:

CREATE TABLE table_name (Column_1 TEXT,Column_2 TEXT);

=> Teraz wstaw dane do nazwa_tabeli z istniejącej_tabeli za pomocą następującego zapytania:

INSERT INTO table_name (Column_1,Column_2) FROM existing_table;

=> Teraz upuść istniejącą tabelę, wykonując następujące zapytanie:

DROP TABLE existing_table;

1

Dla SQLite3 c ++:

void GetTableColNames( tstring sTableName , std::vector<tstring> *pvsCols )
{
    UASSERT(pvsCols);

    CppSQLite3Table table1;

    tstring sDML = StringOps::std_sprintf(_T("SELECT * FROM %s") , sTableName.c_str() );



    table1 = getTable( StringOps::tstringToUTF8string(sDML).c_str() );

    for ( int nCol = 0 ; nCol < table1.numFields() ; nCol++ )
    {
        const char* pch1 = table1.fieldName(nCol);  

        pvsCols->push_back( StringOps::UTF8charTo_tstring(pch1));
    }
}


bool ColExists( tstring sColName )
{
    bool bColExists = true;

    try
    {
        tstring sQuery = StringOps::std_sprintf(_T("SELECT %s FROM MyOriginalTable LIMIT 1;") , sColName.c_str() );

        ShowVerbalMessages(false);

        CppSQLite3Query q = execQuery( StringOps::tstringTo_stdString(sQuery).c_str() );

        ShowVerbalMessages(true);
    }
    catch (CppSQLite3Exception& e)
    {
        bColExists = false;
    }

    return bColExists;
}

void DeleteColumns( std::vector<tstring> *pvsColsToDelete )
{
    UASSERT(pvsColsToDelete);

    execDML( StringOps::tstringTo_stdString(_T("begin transaction;")).c_str() );


    std::vector<tstring> vsCols;
    GetTableColNames( _T("MyOriginalTable") , &vsCols );


    CreateFields( _T("TempTable1") , false );

    tstring sFieldNamesSeperatedByCommas;

    for ( int nCol = 0 ; nCol < vsCols.size() ; nCol++ )
    {

        tstring sColNameCurr = vsCols.at(nCol);

        bool bUseCol = true;

        for ( int nColsToDelete = 0; nColsToDelete < pvsColsToDelete->size() ; nColsToDelete++ )
        {
            if ( pvsColsToDelete->at(nColsToDelete) == sColNameCurr )
            {
                bUseCol = false;
                break;
            }
        }

        if ( bUseCol )
            sFieldNamesSeperatedByCommas+= (sColNameCurr + _T(","));

    }

    if ( sFieldNamesSeperatedByCommas.at( int(sFieldNamesSeperatedByCommas.size()) - 1) == _T(','))
        sFieldNamesSeperatedByCommas.erase( int(sFieldNamesSeperatedByCommas.size()) - 1 );

    tstring sDML;


    sDML = StringOps::std_sprintf(_T("insert into TempTable1 SELECT %s FROM MyOriginalTable;\n") , sFieldNamesSeperatedByCommas.c_str() );
    execDML( StringOps::tstringTo_stdString(sDML).c_str() );

    sDML = StringOps::std_sprintf(_T("ALTER TABLE MyOriginalTable RENAME TO MyOriginalTable_old\n") );
    execDML( StringOps::tstringTo_stdString(sDML).c_str() );

    sDML = StringOps::std_sprintf(_T("ALTER TABLE TempTable1 RENAME TO MyOriginalTable\n") );
    execDML( StringOps::tstringTo_stdString(sDML).c_str() );


    sDML = ( _T("DROP TABLE MyOriginalTable_old;") );   
    execDML( StringOps::tstringTo_stdString(sDML).c_str() );


    execDML( StringOps::tstringTo_stdString(_T("commit transaction;")).c_str() );   
}

1

Stworzyłem funkcję Pythona, w której wpisujesz tabelę i kolumnę, aby usunąć jako argumenty:

def removeColumn(table, column):
    columns = []
    for row in c.execute('PRAGMA table_info(' + table + ')'):
        columns.append(row[1])
    columns.remove(column)
    columns = str(columns)
    columns = columns.replace("[", "(")
    columns = columns.replace("]", ")")
    for i in ["\'", "(", ")"]:
        columns = columns.replace(i, "")
    c.execute('CREATE TABLE temptable AS SELECT ' + columns + ' FROM ' + table)
    c.execute('DROP TABLE ' + table)
    c.execute('ALTER TABLE temptable RENAME TO ' + table)
    conn.commit()

Zgodnie z informacją o odpowiedziach Dudy i MeBigFatGuy to nie zadziała, jeśli w tabeli znajduje się klucz obcy, ale można to naprawić za pomocą 2 linii kodu (tworzenie nowej tabeli, a nie tylko zmiana nazwy tabeli tymczasowej)


Co to jest c? Co to jest Conn? W tej odpowiedzi zbyt wiele założeń dotyczących dostępnych zmiennych nieznanego typu.
Ivan Castellanos

0

W przypadku, gdy ktoś potrzebuje (prawie) gotowej do użycia funkcji PHP, na podstawie tej odpowiedzi przedstawiono następujące :

/**
 * Remove a column from a table.
 * 
 * @param string $tableName The table to remove the column from.
 * @param string $columnName The column to remove from the table.
 */
public function DropTableColumn($tableName, $columnName)
{
    // --
    // Determine all columns except the one to remove.

    $columnNames = array();

    $statement = $pdo->prepare("PRAGMA table_info($tableName);");
    $statement->execute(array());
    $rows = $statement->fetchAll(PDO::FETCH_OBJ);

    $hasColumn = false;

    foreach ($rows as $row)
    {
        if(strtolower($row->name) !== strtolower($columnName))
        {
            array_push($columnNames, $row->name);
        }
        else
        {
            $hasColumn = true;
        }
    }

    // Column does not exist in table, no need to do anything.
    if ( !$hasColumn ) return;

    // --
    // Actually execute the SQL.

    $columns = implode('`,`', $columnNames);

    $statement = $pdo->exec(
       "CREATE TABLE `t1_backup` AS SELECT `$columns` FROM `$tableName`;
        DROP TABLE `$tableName`;
        ALTER TABLE `t1_backup` RENAME TO `$tableName`;");
}

W przeciwieństwie do innych odpowiedzi, SQL używany w tym podejściu wydaje się zachowywać typy danych kolumn, podczas gdy coś w rodzaju zaakceptowanej odpowiedzi wydaje się skutkować tym, że wszystkie kolumny są typu TEXT.

Aktualizacja 1:

Użyty SQL ma tę wadę, że autoincrementkolumny nie są zachowywane.


0

Na wszelki wypadek, gdyby to mogło pomóc komuś takiemu jak ja.

Opierając się na oficjalnej stronie internetowej i zaakceptowanej odpowiedzi , stworzyłem kod przy użyciu C #, który używa System.Data.SQLite pakietu NuGet.

Ten kod zachowuje również klucz podstawowy i klucz obcy .

KOD w C #:

void RemoveColumnFromSqlite (string tableName, string columnToRemove) {
 try {
    var mSqliteDbConnection = new SQLiteConnection ("Data Source=db_folder\\MySqliteBasedApp.db;Version=3;Page Size=1024;");
    mSqliteDbConnection.Open ();             
    // Reads all columns definitions from table
    List<string> columnDefinition = new List<string> ();
    var mSql = $"SELECT type, sql FROM sqlite_master WHERE tbl_name='{tableName}'";
    var mSqliteCommand = new SQLiteCommand (mSql, mSqliteDbConnection);
    string sqlScript = "";
    using (mSqliteReader = mSqliteCommand.ExecuteReader ()) {
       while (mSqliteReader.Read ()) {
          sqlScript = mSqliteReader["sql"].ToString ();
          break;
       }
    }
    if (!string.IsNullOrEmpty (sqlScript)) {
       // Gets string within first '(' and last ')' characters
       int firstIndex = sqlScript.IndexOf ("(");
       int lastIndex = sqlScript.LastIndexOf (")");
       if (firstIndex >= 0 && lastIndex <= sqlScript.Length - 1) {
          sqlScript = sqlScript.Substring (firstIndex, lastIndex - firstIndex + 1);
       }
       string[] scriptParts = sqlScript.Split (new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
       foreach (string s in scriptParts) {
          if (!s.Contains (columnToRemove)) {
             columnDefinition.Add (s);
          }
       }
    }
    string columnDefinitionString = string.Join (",", columnDefinition);
    // Reads all columns from table
    List<string> columns = new List<string> ();
    mSql = $"PRAGMA table_info({tableName})";
    mSqliteCommand = new SQLiteCommand (mSql, mSqliteDbConnection);
    using (mSqliteReader = mSqliteCommand.ExecuteReader ()) {
       while (mSqliteReader.Read ()) columns.Add (mSqliteReader["name"].ToString ());
    }
    columns.Remove (columnToRemove);
    string columnString = string.Join (",", columns);
    mSql = "PRAGMA foreign_keys=OFF";
    mSqliteCommand = new SQLiteCommand (mSql, mSqliteDbConnection);
    int n = mSqliteCommand.ExecuteNonQuery ();
    // Removes a column from the table
    using (SQLiteTransaction tr = mSqliteDbConnection.BeginTransaction ()) {
       using (SQLiteCommand cmd = mSqliteDbConnection.CreateCommand ()) {
          cmd.Transaction = tr;
          string query = $"CREATE TEMPORARY TABLE {tableName}_backup {columnDefinitionString}";
          cmd.CommandText = query;
          cmd.ExecuteNonQuery ();
          cmd.CommandText = $"INSERT INTO {tableName}_backup SELECT {columnString} FROM {tableName}";
          cmd.ExecuteNonQuery ();
          cmd.CommandText = $"DROP TABLE {tableName}";
          cmd.ExecuteNonQuery ();
          cmd.CommandText = $"CREATE TABLE {tableName} {columnDefinitionString}";
          cmd.ExecuteNonQuery ();
          cmd.CommandText = $"INSERT INTO {tableName} SELECT {columnString} FROM {tableName}_backup;";
          cmd.ExecuteNonQuery ();
          cmd.CommandText = $"DROP TABLE {tableName}_backup";
          cmd.ExecuteNonQuery ();
       }
       tr.Commit ();
    }
    mSql = "PRAGMA foreign_keys=ON";
    mSqliteCommand = new SQLiteCommand (mSql, mSqliteDbConnection);
    n = mSqliteCommand.ExecuteNonQuery ();
 } catch (Exception ex) {
    HandleExceptions (ex);
 }
}

0
PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 (
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

INSERT INTO table1 (column1, column2, ... column_n)
  SELECT column1, column2, ... column_n
  FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

Więcej informacji: https://www.techonthenet.com/sqlite/tables/alter_table.php

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.