SQL 从 SQLite 表中删除列

Delete column from SQLite table

提问by sandy

I have a problem: I need to delete a column from my SQLite database. I wrote this query

我有一个问题：我需要从我的 SQLite 数据库中删除一列。我写了这个查询

alter table table_name drop column column_name 

but it does not work. Please help me.

但它不起作用。请帮我。

回答by MeBigFatGuy

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

来自：http: //www.sqlite.org/faq.html：

(11) How do I add or delete columns from an existing table in SQLite.

SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table. If you want to make more complex changes in the structure of a table, you will have to recreate the table. You can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.

For example, suppose you have a table named "t1" with columns names "a", "b", and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done:

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;

(11) 如何在SQLite 中的现有表中添加或删除列。

SQLite 具有有限的 ALTER TABLE 支持，您可以使用它在表的末尾添加一列或更改表的名称。如果要对表的结构进行更复杂的更改，则必须重新创建该表。您可以将现有数据保存到临时表，删除旧表，创建新表，然后从临时表中将数据复制回。

例如，假设您有一个名为“t1”的表，其中列名为“a”、“b”和“c”，并且您想从该表中删除列“c”。以下步骤说明了如何做到这一点：

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;

回答by Duda

Instead of dropping the backup table, just rename it...

而不是删除备份表，只需重命名它......

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;

回答by user4086833

For simplicity, why not create the backup table from the select statement?

为简单起见，为什么不从 select 语句创建备份表？

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

回答by MagTun

This option works only if you can open the DB in a DB Browser like DB Browser for SQLite.

仅当您可以在 DB Browser（例如DB Browser for SQLite）中打开 DB 时，此选项才有效。

In DB Browser for SQLite:

在 SQLite 的数据库浏览器中：

1. Go to the tab, "Database Structure"
2. Select you table Select Modify table (just under the tabs)
3. Select the column you want to delete
4. Click on Remove field and click OK

1. 转到选项卡，“数据库结构”
2. 选择你的表 选择修改表（就在选项卡下）
3. 选择要删除的列
4. 单击删除字段，然后单击确定

回答by user3317939

=>Create a new table directly with the following query:

=>直接使用以下查询创建一个新表：

CREATE TABLE table_name (Column_1 TEXT,Column_2 TEXT);

=>Now insert the data into table_name from existing_table with the following query:

=>现在使用以下查询将数据从 existing_table 插入到 table_name 中：

INSERT INTO table_name (Column_1,Column_2) FROM existing_table;

=>Now drop the existing_table by following query:

=>现在通过以下查询删除 existing_table ：

DROP TABLE existing_table;

回答by Dom

I've made a Pythonfunction where you enter the table and column to remove as arguments:

我制作了一个Python函数，您可以在其中输入要删除的表和列作为参数：

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()

As per the info on Duda's and MeBigFatGuy's answers this won't work if there is a foreign key on the table, but this can be fixed with 2 lines of code (creating a new table and not just renaming the temporary table)

根据 Duda 和 MeBigFatGuy 的回答的信息，如果表上有外键，这将不起作用，但这可以用 2 行代码修复（创建一个新表，而不仅仅是重命名临时表）

回答by Sunny127

For SQLite3 c++ :

对于 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() );   
}

回答by Uwe Keim

In case anyone needs a (nearly) ready-to-use PHP function, the following is based on this answer:

如果有人需要（几乎）随时可用的 PHP 函数，以下内容基于此答案：

/**
 * 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`;");
}

In contrast to other answers, the SQL used in this approach seems to preserve the data types of the columns, whereas something like the accepted answer seems to result in all columns to be of type TEXT.

与其他答案相比，此方法中使用的 SQL 似乎保留了列的数据类型，而类似于已接受的答案似乎导致所有列的类型为TEXT。

Update 1:

更新 1：

The SQL used has the drawback that autoincrementcolumns are notpreserved.

使用的 SQL 的缺点autoincrement是不保留列。

回答by Naveen Kumar V

Just in case if it could help someone like me.

以防万一它可以帮助像我这样的人。

Based on the Official websiteand the Accepted answer, I made a code using C#that uses System.Data.SQLiteNuGet package.

基于官方网站和接受的答案，我使用C#编写了一个使用System.Data.SQLiteNuGet 包的代码。

This code also preserves the Primary keyand Foreign key.

此代码还保留了Primary key和Foreign key。

CODE in C#:

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);
 }
}