在sqlite中，如果表中不存在相同的列，如何在表中添加列

How can I add a column in an SQLite table if and only if the same column does not exist in the table?

如果且仅当该表中不存在相同的列时，如何在SQLite表中添加列?

Using ALTER TABLE I am able to create a new column but want to know how to check whether that column already exists in the table or not?

使用ALTER TABLE，我可以创建一个新的列，但想知道如何检查该列是否已经存在于该表中?

4 个解决方案

#1

You can view the table columns by using '.schema tableName'

可以使用'查看表列。模式的表的

#2

SQLite returns an error like "no such column: foo" if the table doesn't contain the column:

如果表格不包含列，SQLite返回一个类似“no such column: foo”的错误:

  select foo from yourTable limit 1

Also you can get the create-table statement:

您还可以获得createtable语句:

 select sql from sqlite_master where tbl_name = 'YourTableName'

and then parse the result, looking for the column-name. I don't know of an elegant way to query the list of columns for a specified table, though one may exist.

然后解析结果，查找列名。我不知道有什么优雅的方法可以查询指定表的列列表，尽管可能存在一个。

Also if you attempt to do this:

如果你想这样做:

alter table YourTable add column foo {column-def whatever it is}

you get an error from SQLite if the column already exists. You could trap that error too.

如果列已经存在，您将从SQLite获得错误。你也可以设下这个错误。

Finally you could do this:

最后你可以这样做:

  select sql from sqlite_master 
  where tbl_name = 'YOURTABLE' and sql like '%"foo" CHAR%';    -- or whatever type

and if the specified table contains the column which is surrounded by double-quotes in the query, and with the type you have specified, you will get a result, otherwise an empty set. Specifying the datatype ensures that your LIKE substring match occurs on a column-name.

如果指定的表包含在查询中被双引号包围的列，并且使用您指定的类型，您将得到一个结果，否则将得到一个空集。

#3

There's no way (that I know of) to do it all in a single SQLite query. You must use application code to manage the If/Elseness.

在一个SQLite查询中不可能(据我所知)全部完成。您必须使用应用程序代码来管理If/ elc。

Check if table exists or not:

检查表是否存在:

select count(*) from sqlite_master where type = 'table' and name = MyTable';

Check if column exists in table or now

检查列是否存在于表中或现在

pragma table_info(thumbnail);

However, a better approach may be explicit database schema updates based on schema versions your application maintains (e.g. specific alter table statement to go from schema version 1 to 2):

但是，更好的方法可能是基于应用程序维护的模式版本进行显式数据库模式更新(例如，从模式版本1到2的特定alter table语句):

pragma user_version;

#4

It seems like that it is impossible to do checking if the column not exists and addindg the new column in one command, because Sqlite don't support "IF NOT EXISTS" for column. "IF NOT EXISTS" works only on table. Here is what I will do:

似乎不可能检查列是否不存在并在一个命令中添加新列，因为Sqlite不支持列的“如果不存在”。“如果不存在”只适用于表。下面是我要做的:

rev = ExecuteStatement("SELECT columnNamexx FROM tableNamexx limit 1;");

if(rev != SQLITE_OK){ // add col to table
    ExecuteStatement("ALTER TABLE tableNamexx ADD COLUMN columnNamexx INTEGER DEFAULT 0;");
}

#1