是否可以在SQLite数据库中一次插入多行？

在MySQL中,您可以插入多行,如下所示:

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

但是,当我尝试做这样的事情时,我收到了一个错误.是否可以在SQLite数据库中一次插入多行？这样做的语法是什么？

1> fearless_foo..：

---

更新

正如BrianCampbell在此指出的那样,SQLite 3.7.11及更高版本现在支持原始帖子的更简单的语法.但是,如果要在旧数据库中实现最大兼容性,则显示的方法仍然适用.

原始答案

如果我有权限,我会碰到andy的回复:你可以在SQLite中插入多行,你只需要不同的语法.为了使它完全清楚,OPs 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;

如果效率是你的目标,你应该先尝试一下.

关于UNION vs UNION ALL的说明

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

结束

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

---

另外需要注意的是,sqlite似乎只支持每个查询最多500个这样的联合选择,所以如果你试图输入的数据多于你需要将它分成500个元素块(http://www.sqlite.org) /limits.html)

---

进一步说明:小心!此语法删除重复的行!使用`UNION ALL`来避免这种情况(或者为了一些小的性能提升).

---

同意:SQLite不是瓶颈,它是单个ORM事务的开销(在我的例子中,Ruby On Rails).所以它仍然是一个巨大的胜利.

---

该解决方案或3.7.11解决方案与使用事务块相比如何？"插入t值(数据),(数据),(数据)"或"开始事务"是否更快; 插入t值(数据);插入t值(数据);插入t值(数据);提交;`？

2> 小智..：

---

是的,但是没有通常的逗号分隔插入值.

试试这个...

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

是的,它有点丑陋,但很容易从一组值自动生成语句.此外,您似乎只需要在第一个选择中声明列名称.

---

请使用`UNION ALL`而不是'UNION`.除非您想删除重复项.

---

如果您希望ID自动递增,请为它们指定NULL值.

3> Brian Campbe..：

---

是的,从SQLite 3.7.11开始,SQLite 支持这一点.从SQLite文档:

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

为了与旧版本的SQLite的兼容性,可以使用所建议的伎俩安迪和fearless_fool使用UNION,但对于3.7.11,后来在这里所描述的简单的语法应该是首选.

---

我会说diagramm允许多行,因为在"VALUES"之后有一个带括号的闭环.

4> Jamie Cook..：

---

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

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

---

我在我的代码中使用这种插入方式,它完美地运行.您只需要确保一次提交所有SQL.这对我来说是一个巨大的速度提升,但我很好奇接受的答案是否比这更快或更慢？

5> typeseven..：

---

根据此页面,它不受支持:

2007-12-03:不支持多行INSERT又名复合INSERT.

  INSERT INTO table (col1, col2) VALUES 
      ('row1col1', 'row1col2'), ('row2col1', 'row2col2'), ...

实际上,根据SQL92标准,VALUES表达式应该能够独立存在.例如,以下内容应返回包含三行的单列表:VALUES 'john', 'mary', 'paul';

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

"新的多值插入物只是复合插入物的语法特征(sic).没有任何性能优势."

---

对[这里](http://www.mail-archive.com/sqlite-users@sqlite.org/msg68113.html)

6> mjb..：

---

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

7> innaM..：

---

是的,sql可以做到这一点,但使用不同的语法.在SQLite的文档是相当不错的,顺便说一句.它还告诉您插入多行的唯一方法是使用select语句作为要插入的数据的源.

8> Larry Lustig..：

---

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

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

9> DigitalRoss..：

---

除了通过SELECT,Sqlite3不能直接在SQL中执行此操作,而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语句,则所有插入都被分组到一个事务中.提交事务所需的时间在所有随附的insert语句中分摊,因此每个insert语句的时间大大减少.

另一种选择是运行PRAGMA synchronous = OFF.此命令将导致SQLite不等待数据到达磁盘表面,这将使写入操作看起来更快.但是,如果在事务中间断电,则数据库文件可能会损坏.

10> AG1..：

---

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

11> LEO..：

---

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)

12> 小智..：

---

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

13> tuinstoel..：

---

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

因为你总是在调用sqlite,所以无论你执行1个insert语句还是100个insert语句,性能几乎都无关紧要.然而,提交需要花费大量时间,因此将这100个插入内容放入事务中.

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

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

它们需要一次又一次地进行解析,因为每个连接语句都是不同的.

14> g.revolution..：

---

在mysql lite中你不能插入多个值,但是你可以通过只打开一次连接然后执行所有插入然后关闭连接来节省时间.它节省了大量时间

15> 小智..：

---

使用事务的问题是您还要锁定表以进行读取.所以,如果你真的有很多数据要插入,你需要访问您的数据,用于为例预览左右,这种方式不能很好地工作.

另一种解决方案的问题是你丢失了插入的顺序

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

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

16> XenKid..：

---

从版本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写道.

谢谢安迪+1