从 SQLite 表中删除列

Question

从 SQLite 表中删除列

137

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

alter table table_name drop column column_name

但它不起作用。请帮帮我。

- sandy

11个回答

64

不要删除备份表，只需要将其重命名即可...

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;

- Duda

10

当您的 t1 表连接了外键时，它将无法正常工作。 - ephemerr

@ephemerr 这是否意味着 INSERT INTO 将外键连接插入到新表中，但对具有外键连接的表应用 RENAME 会破坏外键连接？ - Mark E.

45

为了简单起见，为什么不直接从选择语句创建备份表呢？

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

- user4086833

3

这种方法似乎保留了列的数据类型，而像被接受的答案那样会导致所有列都成为TEXT类型。 - Uwe Keim

2

这些语句也应该被包含在一个事务中。 - Georg Schölly

11

请注意，这不会保留主键，并且SQLite不支持更改表以添加主键。因此，如果主键很重要，那么您不应该使用此方法。 - Tim

2

这也不保留 NOT NULL。 - FutureShocked

1

接受的答案很好。创建表时，您应该指定数据类型。唉。 - John Lord

17

只有当您可以在类似SQLite数据库浏览器之类的DB浏览器中打开DB时，此选项才有效。

在SQLite数据库浏览器中：

转到“数据库结构”选项卡
选择您的表格并选择修改表格 (就在选项卡下面)
选择要删除的列
单击“删除字段”并单击“确定”

- MagTun

3

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

CREATE TABLE table_name (Column_1 TEXT,Column_2 TEXT);

=>现在使用以下查询将现有表中的数据插入到table_name中：

INSERT INTO table_name (Column_1,Column_2) FROM existing_table;

=>现在按照以下查询删除现有表：

DROP TABLE existing_table;

- user3317939

3

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;

更多信息请参见：https://www.techonthenet.com/sqlite/tables/alter_table.php

- Nexus242

1

我已经编写了一个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()

根据Duda和MeBigFatGuy的回答，如果表中有外键，则此方法不适用，但是可以通过两行代码解决此问题（创建一个新表而不仅仅是重命名临时表）。

- Dom

什么是c？什么是conn？这个答案对未知类型的可用变量做出了太多的假设。 - Ivan Castellanos

@IvanCastellanos c: 光标 & conn: 连接 - ATH

0

如果有人需要一个（几乎）可直接使用的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`;");
}

与其他答案不同，在此方法中使用的SQL似乎保留了列的数据类型，而接受的答案似乎会导致所有列都成为TEXT类型。 更新1： 使用的SQL具有缺点，即autoincrement列未被保存。

- Uwe Keim

0

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

- Sunny127

0

在Python 3.8中...

保留主键和列类型。

需要3个输入：

sqlite游标：db_cur，
表名：t和，
要删除的列列表：columns_to_junk

def removeColumns(db_cur, t, columns_to_junk):

    # Obtain column information
    sql = "PRAGMA table_info(" + t + ")"
    record = query(db_cur, sql)

    # Initialize two strings: one for column names + column types and one just
    # for column names
    cols_w_types = "("
    cols = ""

    # Build the strings, filtering for the column to throw out
    for r in record:
        if r[1] not in columns_to_junk:
            if r[5] == 0:
                cols_w_types += r[1] + " " + r[2] + ","
            if r[5] == 1:
                cols_w_types += r[1] + " " + r[2] + " PRIMARY KEY,"
            cols += r[1] + ","

    # Cut potentially trailing commas
    if cols_w_types[-1] == ",":
        cols_w_types = cols_w_types[:-1]
    else:
        pass

    if cols[-1] == ",":
        cols = cols[:-1]
    else:
        pass

    # Execute SQL
    sql = "CREATE TEMPORARY TABLE xfer " + cols_w_types + ")"
    db_cur.execute(sql)
    sql = "INSERT INTO xfer SELECT " + cols + " FROM " + t
    db_cur.execute(sql)
    sql = "DROP TABLE " + t
    db_cur.execute(sql)
    sql = "CREATE TABLE " + t + cols_w_types + ")"
    db_cur.execute(sql)
    sql = "INSERT INTO " + t + " SELECT " + cols  + " FROM xfer"
    db_cur.execute(sql)

你会发现一个query()函数的参考。只是一个辅助...

需要两个输入：

sqlite游标db_cur和
查询字符串：query

def query(db_cur, query):

    r = db_cur.execute(query).fetchall()

    return r

不要忘记包含一个“commit()”！

- user14375440

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- MeBigFatGuy · Accepted Answer

更新：SQLite 2021-03-12（3.35.0）现在支持删除列。

来自：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;