在MySQL中,你可以像这样插入多行:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2');
然而,当我尝试这样做时,我得到了一个错误。是否可以在SQLite数据库中一次插入多行?这样做的语法是什么?
在MySQL中,你可以像这样插入多行:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2');
然而,当我尝试这样做时,我得到了一个错误。是否可以在SQLite数据库中一次插入多行?这样做的语法是什么?
当前回答
我能够使查询动态。这是我的桌子:
创造可写的“tblPlanner”文本。
我得到所有的数据通过一个JSON,所以在得到一个NSArray内的所有东西后,我遵循这个:
NSMutableString *query = [[NSMutableString alloc]init];
for (int i = 0; i < arr.count; i++)
{
NSString *sqlQuery = nil;
sqlQuery = [NSString stringWithFormat:@" ('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@'),",
[[arr objectAtIndex:i] objectForKey:@"plannerid"],
[[arr objectAtIndex:i] objectForKey:@"probid"],
[[arr objectAtIndex:i] objectForKey:@"userid"],
[[arr objectAtIndex:i] objectForKey:@"selectedtime"],
[[arr objectAtIndex:i] objectForKey:@"isLocal"],
[[arr objectAtIndex:i] objectForKey:@"subject"],
[[arr objectAtIndex:i] objectForKey:@"comment"],
[[NSUserDefaults standardUserDefaults] objectForKey:@"applicationid"]
];
[query appendString:sqlQuery];
}
// REMOVING LAST COMMA NOW
[query deleteCharactersInRange:NSMakeRange([query length]-1, 1)];
query = [NSString stringWithFormat:@"insert into tblPlanner (plannerid, probid, userid, selectedtime, isLocal, applicationid, subject, comment) values%@",query];
最后输出查询是这样的:
insert into tblPlanner (plannerid, probid, userid, selectedtime, isLocal, applicationid, subject, comment) values
<append 1>
('pl1176428260', '', 'US32552', '2013-06-08 12:00:44 +0000', '0', 'subj', 'Hiss', 'ap19788'),
<append 2>
('pl2050411638', '', 'US32552', '2013-05-20 10:45:55 +0000', '0', 'TERI', 'Yahoooooooooo', 'ap19788'),
<append 3>
('pl1828600651', '', 'US32552', '2013-05-21 11:33:33 +0000', '0', 'test', 'Yest', 'ap19788'),
<append 4>
('pl549085534', '', 'US32552', '2013-05-19 11:45:04 +0000', '0', 'subj', 'Comment', 'ap19788'),
<append 5>
('pl665538927', '', 'US32552', '2013-05-29 11:45:41 +0000', '0', 'subj', '1234567890', 'ap19788'),
<append 6>
('pl1969438050', '', 'US32552', '2013-06-01 12:00:18 +0000', '0', 'subj', 'Cmt', 'ap19788'),
<append 7>
('pl672204050', '', 'US55240280', '2013-05-23 12:15:58 +0000', '0', 'aassdd', 'Cmt', 'ap19788'),
<append 8>
('pl1019026150', '', 'US32552', '2013-06-08 12:15:54 +0000', '0', 'exists', 'Cmt', 'ap19788'),
<append 9>
('pl790670523', '', 'US55240280', '2013-05-26 12:30:21 +0000', '0', 'qwerty', 'Cmt', 'ap19788')
它在代码中也运行良好,我能够成功地在SQLite中保存所有内容。
在此之前,我使工会查询东西动态,但开始给一些语法错误。不管怎样,这对我来说很好。
其他回答
你可以使用InsertHelper,它简单快捷
文档: http://developer.android.com/reference/android/database/DatabaseUtils.InsertHelper.html
教程: http://www.outofwhatbox.com/blog/2010/12/android-using-databaseutils-inserthelper-for-faster-insertions-into-sqlite-database/
编辑: 从API级别17开始,InsertHelper已弃用
简单。自我解释。
在版本3.36.0 11/20/21上进行测试。
CREATE TEMP TABLE x (col1 TEXT, col2 TEXT, col3 TEXT);
INSERT INTO x
VALUES
('xx','yy','cc'),
('xx','yy','cc'),
('xx','yy','cc'),
('xx','yy','cc'),
('xx','yy','cc'),
('xx','yy','cc');
SELECT * FROM x;
输出:
col1|col2|col3|
----+----+----+
xx |yy |cc |
xx |yy |cc |
xx |yy |cc |
xx |yy |cc |
xx |yy |cc |
xx |yy |cc |
版本检查:
SELECT sqlite_version();
输出:
sqlite_version()|
----------------+
3.36.0 |
有些人可能会说-所有的“..”的答案已经过时了;尽管如此,它们还是非常有用的。有时候,我们都坐在桌子上“过去的爆炸”,然后15年前的纸条拯救了我们的一天。
Sqlite3不能在SQL中直接做到这一点,除非通过一个SELECT,虽然SELECT可以返回一个“行”表达式,但我知道没有办法让它返回一个假列。
但是,CLI可以做到:
.import FILE TABLE Import data from FILE into TABLE
.separator STRING Change separator used by output mode and .import
$ sqlite3 /tmp/test.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table abc (a);
sqlite> .import /dev/tty abc
1
2
3
99
^D
sqlite> select * from abc;
1
2
3
99
sqlite>
如果你在INSERT中使用了一个循环,而不是使用CLI .import命令,那么一定要遵循sqlite FAQ中的建议来提高INSERT速度:
By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced. Another option is to run PRAGMA synchronous=OFF. This command will cause SQLite to not wait on data to reach the disk surface, which will make write operations appear to be much faster. But if you lose power in the middle of a transaction, your database file might go corrupt.
Alex是正确的:“select…”“Union”语句将失去对某些用户非常重要的排序。即使当您以特定的顺序插入时,sqlite也会更改内容,因此如果插入顺序很重要,则更倾向于使用事务。
create table t_example (qid int not null, primary key (qid));
begin transaction;
insert into "t_example" (qid) values (8);
insert into "t_example" (qid) values (4);
insert into "t_example" (qid) values (9);
end transaction;
select rowid,* from t_example;
1|8
2|4
3|9
从版本2012-03-20(3.7.11)开始,sqlite支持以下INSERT语法:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data3', 'data4'),
('data5', 'data6'),
('data7', 'data8');
阅读文档:http://www.sqlite.org/lang_insert.html
PS:请给Brian Campbell的回复+1。不是我的!他首先提出了解决方案。