从 SQLite 表中删除列

更新: SQLite 2021-03-12(3.35.0)现在支持 DROP COLUMN。

发信人: http://www.sqlite.org/faq.html:

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

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

例如，假设您有一个名为“ 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;

使用以下查询直接创建一个新表:

CREATE TABLE table_name (Column_1 TEXT,Column_2 TEXT);

现在使用以下查询将数据插入现有 _ table 中的 table _ name:

INSERT INTO table_name (Column_1,Column_2) FROM existing_table;

现在通过以下查询删除现有 _ table:

DROP TABLE existing_table;

对于 SQLite3c + + :

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

与其删除备份表，不如重命名它..。

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;

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

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

如果有人需要(几乎)现成的 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列保留为 没有。

只有在像 SQLite 数据库浏览器这样的数据库浏览器中打开数据库时，此选项才有效。

在 DB 的 SQLite 浏览器中:

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

以防它能帮到我这样的人。

基于 官方网站和 接受回答，我使用使用 System.Data.SQLite NuGet 包的 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);
}
}

我创建了一个 巨蟒函数，在其中输入作为参数删除的表和列:

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 回答的信息，如果表上有一个外键，这个方法就不起作用了，但是这可以通过两行代码来修复(创建一个新的表，而不仅仅是重命名临时表)

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

在 Python 3.8中。 保留主键和列类型。

需要3个输入:

1. 一个 sqlite 游标: db _ cur,
2. 表名: t 和,
3. 列到垃圾的列表:

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 ()函数的引用。

需要两个输入:

1. Sqlite 游标 db _ cur 和,
2. Query 字符串: query

def query(db_cur, query):


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


return r

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