我想把我的iPhone应用程序迁移到一个新的数据库版本。由于我没有保存一些版本,我需要检查是否存在某些列名。

这个Stackoverflow条目建议执行选择

SELECT sql FROM sqlite_master
WHERE tbl_name = 'table_name' AND type = 'table'

并解析结果。

这是常见的方式吗?选择呢?


当前回答

我知道这是一个旧的帖子,但最近我需要同样的帖子,并找到了一个简洁的方法:

SELECT c.name FROM pragma_table_info('your_table_name') c;

其他回答

-(NSMutableDictionary*)tableInfo:(NSString *)table
{
  sqlite3_stmt *sqlStatement;
  NSMutableDictionary *result = [[NSMutableDictionary alloc] init];
  const char *sql = [[NSString stringWithFormat:@"pragma table_info('%s')",[table UTF8String]] UTF8String];
  if(sqlite3_prepare(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK)
  {
    NSLog(@"Problem with prepare statement tableInfo %@",[NSString stringWithUTF8String:(const char *)sqlite3_errmsg(db)]);

  }
  while (sqlite3_step(sqlStatement)==SQLITE_ROW)
  {
    [result setObject:@"" forKey:[NSString stringWithUTF8String:(char*)sqlite3_column_text(sqlStatement, 1)]];

  }

  return result;
  }

获得这里没有提到的列名列表的另一种方法是,从PRAGMA_TABLE_INFO()表值函数中进行选择,这种方法是跨平台的,不依赖于sqlite3.exe shell。

SELECT name FROM PRAGMA_TABLE_INFO('your_table');
name      
tbl_name  
rootpage  
sql

您可以通过查询来检查某个列是否存在:

SELECT 1 FROM PRAGMA_TABLE_INFO('your_table') WHERE name='column1';
1

如果你不想解析select sql from sqlite_master或pragma table_info的结果,你可以使用它。

注意此特性是实验性的,在SQLite 3.16.0(2017-01-02)版本中添加。

参考:

https://www.sqlite.org/pragma.html#pragfunc

     //Called when application is started. It works on Droidscript, it is tested
     function OnStart()
     {
     //Create a layout with objects vertically centered. 
     lay = app.CreateLayout( "linear", "VCenter,FillXY" );  

     //Create a text label and add it to layout.
     txt = app.CreateText( "", 0.9, 0.4, "multiline" )  
     lay.AddChild( txt );
     app.AddLayout(lay);

     db = app.OpenDatabase( "MyData" )  
  
     //Create a table (if it does not exist already).  
     db.ExecuteSql( "drop table if exists test_table" )
     db.ExecuteSql( "CREATE TABLE IF NOT EXISTS test_table " +  
       "(id integer primary key, data text, num integer)",[],null, OnError )  
        db.ExecuteSql( "insert into test_table values (1,'data10',100), 
        (2,'data20',200),(3,'data30',300)")
        //Get all the table rows.      
        DisplayAllRows("SELECT * FROM test_table");
        DisplayAllRows("select *, id+100 as idplus, 'hahaha' as blabla from 
        test_table order by id desc;") 
     }

//function to display all records 
function DisplayAllRows(sqlstring)  // <-- can you use for any table not need to 
                                //  know column names, just use a *
                                // example: 
{ 
//Use all rows what is in ExecuteSql  (try any, it will works fine)
db.ExecuteSql( sqlstring, [], OnResult, OnError ) 
} 
//Callback to show query results in debug.  
function OnResult( res )   
{  
var len = res.rows.length; 
var s = txt.GetText();  
// ***********************************************************************
// This is the answer how to read column names from table:
for(var ColumnNames in res.rows.item(0)) s += " [ "+ ColumnNames +" ] "; // "[" & "]" optional, i use only in this demo 
// ***********************************************************************
//app.Alert("Here is all Column names what Select from your table:\n"+s);
s+="\n";
for(var i = 0; i < len; i++ )   
{  
    var rows = res.rows.item(i) 
    for (var item in rows) 
        {
            s += "    " + rows[item] + "   ";
        }
    s+="\n\n";
} 
//app.Alert(s);
txt.SetText( s )  
}  
//Callback to show errors.  
function OnError( msg )   
{  
   app.Alert( "Error: " + msg )  
}  

只是为了像我这样的超级菜鸟想知道人们是如何或什么意思

PRAGMA table_info('table_name') 

你想使用它作为你的准备语句,如下所示。这样做将选择一个与此类似的表,只是填充了属于您的表的值。

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

其中id和name是列的实际名称。所以要得到这个值,你需要使用以下命令选择列名:

//returns the name
sqlite3_column_text(stmt, 1);
//returns the type
sqlite3_column_text(stmt, 2);

它将返回当前行的列名。为了获取它们或找到你想要的,你需要遍历所有行。最简单的方法是采用下面的方式。

//where rc is an int variable if wondering :/
rc = sqlite3_prepare_v2(dbPointer, "pragma table_info ('your table name goes here')", -1, &stmt, NULL);

if (rc==SQLITE_OK)
{
    //will continue to go down the rows (columns in your table) till there are no more
    while(sqlite3_step(stmt) == SQLITE_ROW)
    {
        sprintf(colName, "%s", sqlite3_column_text(stmt, 1));
        //do something with colName because it contains the column's name
    }
}

这是一个老问题,但这里有一个替代答案,检索SQLite数据库中的所有列,并为每列提供关联表的名称:

WITH tables AS (SELECT name tableName, sql 
FROM sqlite_master WHERE type = 'table' AND tableName NOT LIKE 'sqlite_%')
SELECT fields.name, fields.type, tableName
FROM tables CROSS JOIN pragma_table_info(tables.tableName) fields

返回这样的结果:

{
    "name": "id",
    "type": "integer",
    "tableName": "examples"
}, {
    "name": "content",
    "type": "text",
    "tableName": "examples"
}

对于包含标识符和字符串内容的简单表。