sqlite

sqlite学习笔记

判断表的某一列是否存在

sqlite里面有一种特殊的语法pragma，其用法如下，摘自官方文档

PRAGMAs that return results and that have no side-effects can be accessed from ordinary SELECT statements as table-valued functions. For each participating PRAGMA, the corresponding table-valued function has the same name as the PRAGMA with a 7-character “pragma_” prefix. The PRAGMA argument and schema, if any, are passed as arguments to the table-valued function.

For example, information about the columns in an index can be read using the index_info pragma as follows:

PRAGMA index_info('idx52');

Or, the same content can be read using:

SELECT * FROM pragma_index_info('idx52');

PRAGMA schema.**table_info(**table-name);

This pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The “pk” column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.

The table named in the table_info pragma can also be a view.

eg:

cid         name        type        notnull     dflt_value  pk        
----------  ----------  ----------  ----------  ----------  ----------
0           id          integer     0                       1         
1           name        text        0                       0

上面这个表的name就是列名，则下面这个语句name不为null就可以判断该列存在了。

SELECT name FROM PRAGMA_TABLE_INFO(table-name) WHERE name=[columnName];