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

Is it possible to insert multiple rows at a time in an SQLite database?

提问by Andrew

In MySQL you can insert multiple rows like this:

在 MySQL 中，您可以像这样插入多行：

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

However, I am getting an error when I try to do something like this. Is it possible to insert multiple rows at a time in an SQLite database? What is the syntax to do that?

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

回答by fearless_fool

update

更新

As BrianCampbell points out here, SQLite 3.7.11 and above now supports the simpler syntax of the original post. However, the approach shown is still appropriate if you want maximum compatibility across legacy databases.

正如BrianCampbell 在这里指出的那样，SQLite 3.7.11 及更高版本现在支持原始帖子的更简单的语法。但是，如果您希望跨旧版数据库具有最大兼容性，则所示方法仍然适用。

original answer

原答案

If I had privileges, I would bump river's reply: You caninsert multiple rows in SQLite, you just need different syntax. To make it perfectly clear, the OPs MySQL example:

如果我有特权，我会撞到River 的回复：您可以在 SQLite 中插入多行，您只需要不同的语法。为清楚起见，OPs MySQL 示例：

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

This can be recast into SQLite as:

这可以重新转换为 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'

a note on performance

性能说明

I originally used this technique to efficiently load large datasets from Ruby on Rails. However, as Jaime Cook points out, it's not clear this is any faster wrapping individual INSERTswithin a single transaction:

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

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

If efficiency is your goal, you should try this first.

如果效率是你的目标，你应该先试试这个。

a note on UNION vs UNION ALL

关于 UNION 与 UNION ALL 的说明

As several people commented, if you use UNION ALL(as shown above), all rows will be inserted, so in this case, you'd get four rows of data1, data2. If you omit the ALL, then duplicate rows will be eliminated (and the operation will presumably be a bit slower). We're using UNION ALL since it more closely matches the semantics of the original post.

正如一些人评论的那样，如果您使用UNION ALL（如上所示），将插入所有行，因此在这种情况下，您将获得四行data1, data2. 如果省略ALL，则重复行将被消除（并且操作可能会慢一点）。我们使用 UNION ALL 是因为它更接近原始帖子的语义。

in closing

在结束

P.S.: Please +1 river's reply, as it presented the solution first.

PS：请+1 River 的回复，因为它首先提出了解决方案。

回答by river

Yes it is possible, but not with the usual comma-separated insert values.

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

Try this...

尝试这个...

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

Yes, it's a little ugly but easy enough to automate the generation of the statement from a set of values. Also, it appears you only need to declare the column names in the first select.

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

回答by Brian Campbell

Yes, as of SQLite 3.7.11this is supported in SQLite. From the SQLite documentation:

是的，从 SQLite 3.7.11 开始，SQLite支持此功能。从SQLite 文档：

(when this answer was originally written, this was not supported)

（最初编写此答案时，不支持此功能）

For compatibility with older versions of SQLite, you can use the trick suggested by andyand fearless_foolusing UNION, but for 3.7.11 and later the simpler syntax described in here should be preferred.

为了与旧版本的 SQLite 兼容，您可以使用andy和fearless_fool建议的技巧using UNION，但对于 3.7.11 及更高版本，应首选此处描述的更简单的语法。

回答by Jamie Cook

I wrote some ruby code to generate a single 500 element multi-row insert from a series of insert statements which was considerably faster than running the individual inserts. Then I tried simply wrapping the multiple inserts into a single transaction and found that I could get the same kind of speed up with considerably less code.

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

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

回答by typeseven

According to this pageit is not supported:

根据此页面，不支持：

• 2007-12-03 : Multi-row INSERT a.k.a. compound INSERT not supported.

• 2007-12-03：不支持多行插入又名复合插入。

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

Actually, according to the SQL92 standard, a VALUES expression should be able to stand on itself. For example, the following should return a one-column table with three rows: VALUES 'john', 'mary', 'paul';

其实按照SQL92标准，VALUES表达式应该是可以自立的。例如，以下应返回一个包含三行的单列表：VALUES 'john', 'mary', 'paul';

As of version 3.7.11 SQLite doessupport multi-row-insert. Richard Hipp comments:

从 3.7.11 版本开始，SQLite确实支持multi-row-insert。理查德·希普评论：

"The new multi-valued insert is merely syntactic suger (sic) for the compound insert. There is no performance advantage one way or the other."

“新的多值插入只是复合插入的句法suger（原文如此）。没有任何一种方式的性能优势。”

回答by mjb

Start from version 2012-03-20 (3.7.11), sqlite support the following INSERT syntax:

从 2012-03-20 (3.7.11) 版本开始，sqlite 支持以下 INSERT 语法：

INSERT INTO 'tablename' ('column1', 'column2') VALUES
  ('data1', 'data2'),
  ('data3', 'data4'),
  ('data5', 'data6'),
  ('data7', 'data8');

Read documentation: http://www.sqlite.org/lang_insert.html

阅读文档：http: //www.sqlite.org/lang_insert.html

PS: Please +1 to Brian Campbell's reply/answer. not mine! He presented the solution first.

PS：请 +1 到 Brian Campbell 的回复/回答。不是我的！他首先提出了解决方案。

回答by innaM

Yes, sql can do this, but with a different syntax. The sqlite documentationis pretty good, by the way. It will also tell youthat the only way to insert several row is use a select statementas the source of the data to be inserted.

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

回答by Larry Lustig

As the other posters have said, SQLite does not support this syntax. I don't know if compound INSERTs are part of the SQL standard, but in my experience they're notimplemented in many products.

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

As an aside, you should be aware that INSERT performance in SQLite is improved considerably if you wrap multiple INSERTs in an explicit transaction.

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

回答by DigitalRoss

Sqlite3 can't do that directly in SQL except via a SELECT, and while SELECT can return a "row" of expressions, I know of no way to make it return a phony column.

除了通过 SELECT 之外，Sqlite3 不能直接在 SQL 中做到这一点，虽然 SELECT 可以返回一个“行”表达式，但我知道没有办法让它返回一个假列。

However, the CLI can do it:

但是，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> 

If you do put a loop around an INSERT, rather than using the CLI .importcommand, then be sure to follow the advice in the sqlite FAQ for INSERT speed:

如果您确实在 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.

默认情况下，每个 INSERT 语句都是它自己的事务。但是，如果您使用 BEGIN...COMMIT 将多个 INSERT 语句括起来，那么所有插入都将组合到一个事务中。提交事务所需的时间在所有包含的插入语句中分摊，因此每个插入语句的时间大大减少。

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

回答by AG1

Alex is correct: the "select ... union" statement will lose the ordering which is very important for some users. Even when you insert in a specific order, sqlite changes things so prefer to use transactions if insert ordering is important.

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