sqllite插入多条数据，mysql中最简单是insert into table（列名，。。。）values (值，。。。),(值，。。。)
但是这个语句在sqllite是不支持的。
sqlite can not do this. The sqlite documentation is pretty good, by the way. It will also tell you that the only way to insert several row is when you use a select statement as the source of the data to be inserted.
用select的写法如下：
INSERT INTO table_name (column1, column2)
SELECT 'value1', 'value1'
UNION SELECT 'value2', 'value2'
UNION SELECT 'value3', 'value3' 排序错误的例子：
insert into mytable (col)
select 'c'
union
select 'd'
union
select 'a'
union
select 'b';
In the sqlite the data will be store a,b,c,d...
再转一个blog，介绍了一下sqllite插入的效率问题
http://hi.baidu.com/duxikuan/item/f75583dd61d54a3be3108f8d
在Android开发中我们常会遇到这样一种情况：在建立一个SQLite数据库时，想同时插入大量数据。那么应该怎么做呢？
下面笔者以插入20条记录为例：
- 将同时插入大量的数据写成一条SQL语句
- 最笨的方法用insert语句一条一条的输入
- 使用事务
代码如下：
public class DateBaseOpenHelper extends SQLiteOpenHelper {
public static final String DBNAME="radiomap";
public static final int VERSION=1;
public DateBaseOpenHelper(Context context){
super(context,DBNAME,null,VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL("create table radiomap(location varchar(20),ap1 int,ap2 int)");
long a=System.currentTimeMillis();
db.execSQL("insert into radiomap(location,ap1,ap2) select 'x=1,y=1',-80,-73 " +
"union all select 'x=2,y=3',80,40 union all select 'x=3,y=5',30,20 "+
"union all select 'x=4,y=5',3,2 union all select 'x=30,y=50',30,20 union all select 'x=3,y=5',40,20"
+" union all select 'x=3,y=5',6,20 union all select 'x=3,y=5',6,7 union all select 'x=3,y=5',7,8 union all select 'x=3,y=5',8,9 union all select 'x=3,y=5',9,9" +
" union all select 'x=3,y=5',3,5 union all select 'x=3,y=5',7,20 union all select 'x=3,y=5',4,20 union all select 'x=3,y=5',5,20 union all select 'x=3,y=5',6,20" +
" union all select 'x=3,y=5',3,6 union all select 'x=3,y=5',7,7 union all select 'x=3,y=5',3,8 union all select 'x=3,y=5',8,2");
long b=System.currentTimeMillis();
long c=b-a;
Log.i("LocationActivity", String.valueOf(c));
a=System.currentTimeMillis();
db.beginTransaction();
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',4,5)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',6,7)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',4,5)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',6,7)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',1,3)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',1,5)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',1,7)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',2,5)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',2,7)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',3,3)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',3,5)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',3,7)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',4,3)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=7',5,5)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',4,7)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',5,3)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',5,5)");
db.setTransactionSuccessful();
db.endTransaction();
b=System.currentTimeMillis();
Log.i("LocationActivity", String.valueOf(b-a));
a=System.currentTimeMillis();
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=7,y=8',7,8)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=8,y=9',8,9)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=9,y=10',9,10)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',4,5)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',6,7)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',2,3)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',2,5)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',2,7)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',3,3)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',3,5)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',3,7)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',4,3)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',4,4)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',4,7)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',5,3)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=7',6,5)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=6,y=7',5,7)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=4,y=5',6,3)");
db.execSQL("insert into radiomap(location,ap1,ap2) values('x=5,y=6',6,5)");
b=System.currentTimeMillis();
Log.i("LocationActivity", String.valueOf(b-a));
}
第一种方式及：
insert into 表名(列名1,列名2)
select 值1,值2
union all
select 值1,值2
union all
select 值1,值2
以上三种方式测试结果，及运行效率：
第一种方式为9ms
第二种用事务的为：86ms
第三种直接用insert插入的为：29ms
这是因为本次测试用了20条数据，所以用事务的反而比不用的开销大时间长。若1000条以上则明显快于直接用insert插入的。
今天有个朋友测试 SQLite，然后得出的结论是：SQLite 效率太低，批量插入1000条记录，居然耗时2分钟！
下面是他发给我的测试代码。我晕~~~~~~
usingSystem.Data;
usingSystem.Data.Common;
usingSystem.Data.SQLite;
//创建数据库文件
File.Delete("test1.db3");
SQLiteConnection.CreateFile("test1.db3");
DbProviderFactory factory=SQLiteFactory.Instance;
using(DbConnection conn=factory.CreateConnection())
{
//连接数据库
conn.ConnectionString="Data Source=test1.db3";
conn.Open();
//创建数据表
stringsql="create table [test1] ([id] INTEGER PRIMARY KEY, [s] TEXT COLLATE NOCASE)";
DbCommand cmd=conn.CreateCommand();
cmd.Connection=conn;
cmd.CommandText=sql;
cmd.ExecuteNonQuery();
//添加参数
cmd.Parameters.Add(cmd.CreateParameter());
//开始计时
Stopwatch watch=newStopwatch();
watch.Start();
//连续插入1000条记录
for(inti=0; i<1000; i++)
{
cmd.CommandText="insert into [test1] ([s]) values (?)";
cmd.Parameters[0].Value=i.ToString();
cmd.ExecuteNonQuery();
}
//停止计时
watch.Stop();
Console.WriteLine(watch.Elapsed);
}
哎~~~~一个常识性的错误，我加几行代码 (新增代码标记"// <-------------------")。
usingSystem.Data;
usingSystem.Data.Common;
usingSystem.Data.SQLite;
//创建数据库文件
File.Delete("test1.db3");
SQLiteConnection.CreateFile("test1.db3");
DbProviderFactory factory=SQLiteFactory.Instance;
using(DbConnection conn=factory.CreateConnection())
{
//连接数据库
conn.ConnectionString="Data Source=test1.db3";
conn.Open();
//创建数据表
stringsql="create table [test1] ([id] INTEGER PRIMARY KEY, [s] TEXT COLLATE NOCASE)";
DbCommand cmd=conn.CreateCommand();
cmd.Connection=conn;
cmd.CommandText=sql;
cmd.ExecuteNonQuery();
//添加参数
cmd.Parameters.Add(cmd.CreateParameter());
//开始计时
Stopwatch watch=newStopwatch();
watch.Start();
DbTransaction trans=conn.BeginTransaction();//<-------------------
try
{
//连续插入1000条记录
for(inti=0; i<1000; i++)
{
cmd.CommandText="insert into [test1] ([s]) values (?)";
cmd.Parameters[0].Value=i.ToString();
cmd.ExecuteNonQuery();
}
trans.Commit();//<-------------------
}
catch
{
trans.Rollback();//<-------------------
throw;//<-------------------
}
//停止计时
watch.Stop();
Console.WriteLine(watch.Elapsed);
}
执行一下，耗时0.2秒。这差距是不是太大了点？
为什么只是简单启用了一个事务会有这么大的差距呢？很简单，SQLite 缺省为每个操作启动一个事务，那么原代码1000次插入起码开启了1000个事务，"事务开启 + SQL 执行 + 事务关闭"自然耗费了大量的时间，这也是后面显示启动事务后为什么如此快的原因。其实这是数据库操作的基本常识，大家要紧记，不好的代码效率差的不是一点半点。
最近在做android项目的时候遇到一个问题，应用程序初始化时需要批量的向sqlite中插入大量数，导致应用启动过慢。
android使用的是sqlite数据库，sqlite是比较轻量级的数据库，在Google了之后发现，sqlite事务处理的问题，在sqlite插入数据的时候默认一条语句就是一个事务，有多少条数据就有多少次磁盘操作。我的应用初始5000条记录也就是要5000次读写磁盘操作。
解决方法：
添加事务处理，把5000条插入作为一个事务
dataBase.beginTransaction(); //手动设置开始事务
//数据插入操作循环
dataBase.setTransactionSuccessful(); //设置事务处理成功，不设置会自动回滚不提交
dataBase.endTransaction(); //处理完成
SQLite的数据库本质上来讲就是一个磁盘上的文件，所以一切的数据库操作其实都会转化为对文件的操作，而频繁的文件操作将会是一个很好时的过程，会极大地影响数据库存取的速度。
例如：向数据库中插入100万条数据，在默认的情况下如果仅仅是执行
sqlite3_exec(db, “insert into name values ‘lxkxf', ‘24'; ”,0,0,&zErrMsg);
将会重复的打开关闭数据库文件100万次，所以速度当然会很慢。因此对于这种情况我们应该使用“事务”。
具体方法如下：在执行SQL语句之前和SQL语句执行完毕之后加上
rc=sqlite3_exec(db,"BEGIN;",0,0,&zErrMsg);
//执行SQL语句
rc=sqlite3_exec(db,"COMMIT;",0,0,&zErrMsg);
这样SQLite将把全部要执行的SQL语句先缓存在内存当中，然后等到COMMIT的时候一次性的写入数据库，这样数据库文件只被打开关闭了一次，效率自然大大的提高。有一组数据对比：
测试1:1000INSERTs
CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');
INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');
...995lines omitted
INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');
INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');
INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');
SQLite2.7.6:
13.061
SQLite2.7.6(nosync):
0.223
测试2: 使用事务25000INSERTs
BEGIN;
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t2 VALUES(1,59672,'fifty nine thousand six hundred seventy two');
...24997lines omitted
INSERT INTO t2 VALUES(24999,89569,'eighty nine thousand five hundred sixty nine');
INSERT INTO t2 VALUES(25000,94666,'ninety four thousand six hundred sixty six');
COMMIT;
SQLite2.7.6:
0.914
SQLite2.7.6(nosync):
0.757
可见使用了事务之后却是极大的提高了数据库的效率。但是我们也要注意，使用事务也是有一定的开销的，所以对于数据量很小的操作可以不必使用，以免造成而外的消耗。
扫一扫
1774
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
