tutorial - sqlite语句




是否可以在SQLite数据库中一次插入多行? (16)

从版本3.7.11开始,SQLite确实支持多行插入。 理查德·希普评论道:

我正在使用3.6.13

我的命令是这样的:

insert into xtable(f1,f2,f3) select v1 as f1, v2 as f2, v3 as f3 
union select nextV1+, nextV2+, nextV3+

每次插入50条记录,只需要一秒钟或更短的时间。

这是真的使用sqlite一次插入多行是非常可能的。 @Andy写道。

感谢Andy +1

在MySQL中,你可以像这样插入多行:

INSERT INTO 'tablename' ('column1', 'column2') VALUES
    ('data1', 'data2'),
    ('data1', 'data2'),
    ('data1', 'data2'),
    ('data1', 'data2');

但是,当我尝试执行此类操作时,出现错误。 是否可以在SQLite数据库中一次插入多行? 这是什么语法?


更新

正如BrianCampbell在这里指出的那样 , SQLite 3.7.11及更高版本现在支持原始文章的更简单的语法 。 但是,如果您希望跨遗留数据库达到最大兼容性,则所示方法仍然适用。

原始答案

如果我有权限,我会碰到andy的回复 :你可以在SQLite中插入多行,你只需要不同的语法 。 为了使它完全清楚,OP的MySQL示例:

INSERT INTO 'tablename' ('column1', 'column2') VALUES
  ('data1', 'data2'),
  ('data1', 'data2'),
  ('data1', 'data2'),
  ('data1', 'data2');

这可以重新转换为SQLite,如下所示:

     INSERT INTO 'tablename'
          SELECT 'data1' AS 'column1', 'data2' AS 'column2'
UNION ALL SELECT 'data1', 'data2'
UNION ALL SELECT 'data1', 'data2'
UNION ALL SELECT 'data1', 'data2'

关于表演的说明

我最初使用这种技术来从Ruby on Rails高效地加载大型数据集。 然而 , 正如Jaime Cook指出的那样 ,目前还不清楚在单个事务中是否有更快的包装单个INSERTs

BEGIN TRANSACTION;
INSERT INTO 'tablename' table VALUES ('data1', 'data2');
INSERT INTO 'tablename' table VALUES ('data3', 'data4');
...
COMMIT;

如果效率是您的目标,那么您应该先尝试一下。

一个关于联盟vs联盟所有的说明

正如几个人所评论的,如果你使用UNION ALL (如上所示),所有的行都会被插入,所以在这种情况下,你会得到四行data1, data2 。 如果你省略了ALL ,那么重复的行将被消除(操作可能会慢一点)。 我们使用UNION ALL,因为它更贴近原始帖子的语义。

关闭

PS:请+1 安迪的回复 ,不是我的! 他首先提出了解决方案。


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速度的建议操作:

默认情况下,每个INSERT语句都是它自己的事务。 但是,如果使用BEGIN ... COMMIT包围多个INSERT语句,则所有插入操作都会分组到一个事务中。 提交事务所需的时间将在所有随附的插入语句中摊销,因此每个插入语句的时间大大减少。

另一个选项是运行PRAGMA synchronous = OFF。 这个命令将导致SQLite不等待数据到达磁盘表面,这将使写入操作看起来更快。 但是,如果您在交易中断电,您的数据库文件可能会损坏。


fearless_fool对旧版本有很好的答案。 我只想补充一点,你需要确保列出所有列。 所以如果你有3列,你需要确保在3列上选择行为。

例如:我有3列,但我只想插入2列数据。 假设我不关心第一列,因为它是一个标准的整数ID。 我可以做以下...

INSERT INTO 'tablename'
      SELECT NULL AS 'column1', 'data1' AS 'column2', 'data2' AS 'column3'
UNION SELECT NULL, 'data3', 'data4'
UNION SELECT NULL, 'data5', 'data6'
UNION SELECT NULL, 'data7', 'data8'

注意:请记住“select ... union”语句将失去排序。 (来自AG1)


从版本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。 不是我的! 他首先提出了解决方案。


你不能,但我不认为你错过任何东西。

因为你总是在调用sqlite,所以执行1个插入语句或100个插入语句在性能上几乎没有关系。 但是提交需要很多时间,因此将这100个插入放入事务中。

当你使用参数化查询时,Sqlite会快得多(不需要太多解析),所以我不会像这样连接大的语句:

insert into mytable (col1, col2)
select 'a','b'
union 
select 'c','d'
union ...

他们需要一次又一次地解析,因为每个连接的语句都是不同的。


使用交易的问题在于您锁定表格以供阅读。 所以如果你有很多数据要插入,而且你需要访问你的数据,例如预览等,这种方式不能很好地工作。

另一个解决方案的问题是您失去了插入的顺序

insert into mytable (col)
select 'c'
union 
select 'd'
union 
select 'a'
union 
select 'b';

在sqlite的数据将存储a,b,c,d ...


在mysql lite中,您无法插入多个值,但只需一次打开连接,然后执行所有插入操作并关闭连接,即可节省时间。 它节省了很多时间


如果你使用bash shell,你可以使用这个:

time bash -c $'
FILE=/dev/shm/test.db
sqlite3 $FILE "create table if not exists tab(id int);"
sqlite3 $FILE "insert into tab values (1),(2)"
for i in 1 2 3 4; do sqlite3 $FILE "INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5"; done; 
sqlite3 $FILE "select count(*) from tab;"'

或者如果你在sqlite CLI中,那么你需要这样做:

create table if not exists tab(id int);"
insert into tab values (1),(2);
INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5;
INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5;
INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5;
INSERT INTO tab (id) select (a.id+b.id+c.id)*abs(random()%1e7) from tab a, tab b, tab c limit 5e5;
select count(*) from tab;

它是如何工作的? 它使用该表格tab

id int
------
1
2

然后select a.id, b.id from tab a, tab b返回

a.id int | b.id int
------------------
    1    | 1
    2    | 1
    1    | 2
    2    | 2

等等。 第一次执行后,我们插入2行,然后2 ^ 3 = 8。 (三个因为我们有tab a, tab b, tab c

第二次执行后,我们插入额外的(2+8)^3=1000

下午我们插入大约max(1000^3, 5e5)=500000行,等等......

这是填充SQLite数据库的最快速的方法。


我写了一些ruby代码来从一系列插入语句中生成一个500元素多行插入,这比运行单个插入语句要快得多。 然后,我试图简单地将多个插入包装到单个事务中,发现我可以用相当少的代码获得相同的加速。

BEGIN TRANSACTION;
INSERT INTO table VALUES (1,1,1,1);
INSERT INTO table VALUES (2,2,2,2);
...
COMMIT;

我很惊讶没有人提到准备好的陈述 。 除非你自己使用SQL而不使用任何其他语言,否则我会认为包装在事务中的准备好的语句将是插入多行的最有效方式。


我有一个像下面这样的查询,但是对于ODBC驱动程序,SQLite在“,”中有一个错误。 我在HTA(Html应用程序)中运行vbscript。

INSERT INTO evrak_ilac_iliskileri (evrak_id, ilac_id, baglayan_kullanici_id, tarih) VALUES (4150,762,1,datetime()),(4150,9770,1,datetime()),(4150,6609,1,datetime()),(4150,3628,1,datetime()),(4150,9422,1,datetime())

是的,从SQLite 3.7.11 ,SQLite支持。 从SQLite文档

(当这个答案最初被写入时,这不被支持)

为了与旧版本的SQLite兼容,你可以使用使用UNION的andy和fearless_fool建议的技巧,但对于3.7.11和更高版本,这里描述的更简单的语法应该是首选。


是的,这是可能的,但不是通常用逗号分隔的插入值。

尝试这个...

insert into myTable (col1,col2) 
     select aValue as col1,anotherValue as col2 
     union select moreValue,evenMoreValue 
     union...

是的,这有点难看,但很容易从一组值中自动生成声明。 此外,它似乎只需要在第一个选择中声明列名称。


正如其他海报所说,SQLite不支持这种语法。 我不知道复合INSERT是否是SQL标准的一部分,但以我的经验来看,它们并未在许多产品中实现。

顺便说一句,你应该知道,如果你在一个显式事务中包装多个INSERT,SQLite中的INSERT性能会得到很大的提高。


INSERT INTO TABLE_NAME 
            (DATA1, 
             DATA2) 
VALUES      (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2); 




syntax