我想把我的iPhone应用程序迁移到一个新的数据库版本。由于我没有保存一些版本,我需要检查是否存在某些列名。
这个Stackoverflow条目建议执行选择
SELECT sql FROM sqlite_master
WHERE tbl_name = 'table_name' AND type = 'table'
并解析结果。
这是常见的方式吗?选择呢?
我想把我的iPhone应用程序迁移到一个新的数据库版本。由于我没有保存一些版本,我需要检查是否存在某些列名。
这个Stackoverflow条目建议执行选择
SELECT sql FROM sqlite_master
WHERE tbl_name = 'table_name' AND type = 'table'
并解析结果。
这是常见的方式吗?选择呢?
当前回答
获得这里没有提到的列名列表的另一种方法是,从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
其他回答
我知道已经太迟了,但这会帮助其他人。
要找到表的列名,您应该执行select * from tbl_name,您将得到sqlite3_stmt *格式的结果。并检查列对所获取的总列的迭代。请参考以下代码相同。
// sqlite3_stmt *statement ;
int totalColumn = sqlite3_column_count(statement);
for (int iterator = 0; iterator<totalColumn; iterator++) {
NSLog(@"%s", sqlite3_column_name(statement, iterator));
}
这将打印结果集的所有列名。
只是为了像我这样的超级菜鸟想知道人们是如何或什么意思
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
}
}
下面的命令设置列名:
.header on
然后,它是这样的:
sqlite> select * from user;
id|first_name|last_name|age
1|Steve|Jobs|56
2|Bill|Gates|66
3|Mark|Zuckerberg|38
下面的命令取消设置列名:
.header off
然后,它是这样的:
sqlite> select * from user;
1|Steve|Jobs|56
2|Bill|Gates|66
3|Mark|Zuckerberg|38
这些命令显示了".header"命令的详细信息:
.help .header
Or:
.help header
然后,它是这样的:
sqlite> .help .header
.headers on|off Turn display of headers on or off
此外,下面的命令设置输出模式“box”:
.mode box
然后,它是这样的:
sqlite> select * from user;
┌────┬────────────┬────────────┬─────┐
│ id │ first_name │ last_name │ age │
├────┼────────────┼────────────┼─────┤
│ 1 │ Steve │ Jobs │ 56 │
│ 2 │ Bill │ Gates │ 66 │
│ 3 │ Mark │ Zuckerberg │ 38 │
└────┴────────────┴────────────┴─────┘
并且,下面的命令设置输出模式“table”:
.mode table
然后,它是这样的:
sqlite> select * from user;
+----+------------+------------+-----+
| id | first_name | last_name | age |
+----+------------+------------+-----+
| 1 | Steve | Jobs | 56 |
| 2 | Bill | Gates | 66 |
| 3 | Mark | Zuckerberg | 38 |
+----+------------+------------+-----+
这些命令显示了命令".mode"的详细信息:
.help .mode
Or:
.help mode
然后,它是这样的:
sqlite> .help .mode
.import FILE TABLE Import data from FILE into TABLE
Options:
--ascii Use \037 and \036 as column and row separators
--csv Use , and \n as column and row separators
--skip N Skip the first N rows of input
--schema S Target table to be S.TABLE
-v "Verbose" - increase auxiliary output
Notes:
* If TABLE does not exist, it is created. The first row of input
determines the column names.
* If neither --csv or --ascii are used, the input mode is derived
from the ".mode" output mode
* If FILE begins with "|" then it is a command that generates the
input text.
.mode MODE ?OPTIONS? Set output mode
MODE is one of:
ascii Columns/rows delimited by 0x1F and 0x1E
box Tables using unicode box-drawing characters
csv Comma-separated values
column Output in columns. (See .width)
html HTML <table> code
insert SQL insert statements for TABLE
json Results in a JSON array
line One value per line
list Values delimited by "|"
markdown Markdown table format
qbox Shorthand for "box --width 60 --quote"
quote Escape answers as for SQL
table ASCII-art table
tabs Tab-separated values
tcl TCL list elements
OPTIONS: (for columnar modes or insert mode):
--wrap N Wrap output lines to no longer than N characters
--wordwrap B Wrap or not at word boundaries per B (on/off)
--ww Shorthand for "--wordwrap 1"
--quote Quote output text as SQL literals
--noquote Do not quote output text
TABLE The name of SQL table used for "insert" mode
//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 )
}
function getDetails(){
var data = [];
dBase.executeSql("PRAGMA table_info('table_name') ", [], function(rsp){
if(rsp.rows.length > 0){
for(var i=0; i<rsp.rows.length; i++){
var o = {
name: rsp.rows.item(i).name,
type: rsp.rows.item(i).type
}
data.push(o);
}
}
alert(rsp.rows.item(0).name);
},function(error){
alert(JSON.stringify(error));
});
}