In sqlite How to add column in table if same column is not exists in table
How can I add a column in an SQLite table if and only if the same column does not exist in the table?
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?
您可以使用'.schema tableName'查看表格列
SQLite returns an error like "no such column: foo" if the table doesn't contain the column:
select foo from yourTable limit 1
Also you can get the create-table statement:
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.
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.
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.
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 (eg specific alter table statement to go from schema version 1 to 2):
pragma user_version;
上一篇: 如果不存在，则创建表