是否可以在 SQLite 数据库中一次插入多行? [英] Is it possible to insert multiple rows at a time in an SQLite database?

问题描述

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

In MySQL you can insert multiple rows like this:

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

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

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?

推荐答案

update

正如 BrianCampbell 在此处指出的，SQLite 3.7.11 及更高版本现在支持原始语法的更简单语法发布.但是，如果您希望在旧数据库之间实现最大兼容性，所示方法仍然适用.

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.

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

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

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

这可以改写为 SQLite:

This can be recast into SQLite as:

     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 在单个事务中:

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 INSERTs within a single transaction:

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.

正如一些人评论的那样，如果您使用UNION ALL(如上所示)，将插入所有行，因此在这种情况下，您将获得四行data1, data2.如果省略 ALL，则重复行将被消除(并且操作可能会慢一点).我们使用 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.

P.S.:请 +1 river 的回复，因为它首先提出了解决方案.

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

这篇关于是否可以在 SQLite 数据库中一次插入多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！