Можно ли одновременно вставлять несколько строк в базу данных SQLite?

В MySQL вы можете вставить несколько строк, например:

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

Тем не менее, я получаю сообщение об ошибке, когда я пытаюсь сделать что-то подобное. Можно ли одновременно вставлять несколько строк в базу данных SQLite? Какой синтаксис для этого?

22.10.2009 20:04:42
Не дурак, так как этот вопрос касается конкретно SQLite, в отличие от SQL в целом (хотя некоторые ответы на этот вопрос полезны для этого).
Brian Campbell 22.10.2009 20:15:37
На массовых вставках: stackoverflow.com/questions/1711631/…
tuinstoel 22.11.2009 08:48:32
Вопрос в том, почему вы должны это сделать. Поскольку SQlite находится в процессе на одной машине, и вы можете заключить несколько транзакций в транзакцию, я не вижу необходимости?
andig 10.07.2012 12:36:33
Да, начиная с версии 2012-03-20 (3.7.11), ваш синтаксис поддерживается.
mjb 16.05.2013 16:14:57
24 ОТВЕТА
РЕШЕНИЕ

Обновить

Как указывает здесь BrianCampbell , SQLite 3.7.11 и выше теперь поддерживает более простой синтаксис исходного поста . Однако показанный подход все еще уместен, если вы хотите максимальной совместимости между устаревшими базами данных.

оригинальный ответ

Если бы у меня были привилегии, я бы добавил ответ реки : вы можете вставить несколько строк в 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. Однако , как отмечает Джейм Кук , неясно, что это более быстрое включение индивидуума INSERTsв одну транзакцию:

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

Если ваша цель - эффективность, сначала попробуйте это.

заметка о СОЮЗЕ против СОЮЗА ВСЕХ

Как прокомментировали несколько человек, если вы используете UNION ALL(как показано выше), все строки будут вставлены, поэтому в этом случае вы получите четыре строки data1, data2. Если вы опустите ALL, дублирующиеся строки будут удалены (и операция, вероятно, будет немного медленнее). Мы используем UNION ALL, так как он более точно соответствует семантике исходного поста.

в заключение

PS: Пожалуйста , ответьте +1 реке , так как он представил решение в первую очередь.

607
7.04.2020 12:32:05
В качестве дальнейшего примечания, кажется, что sqlite поддерживает до 500 таких объединений за запрос, поэтому, если вы пытаетесь добавить больше данных, чем вам, вам нужно разбить их на 500 блоков элементов ( sqlite.org/limits.html )
Jamie Cook 6.03.2011 06:02:54
Согласен: SQLite не является узким местом, это накладные расходы на отдельные транзакции ORM (в моем случае, Ruby On Rails). Так что это все еще большая победа.
fearless_fool 19.03.2013 19:58:39
Как это решение или решение 3.7.11 сравнивается с использованием блоков транзакций? это быстрее insert into t values (data),(data),(data)или begin transaction; insert into t values (data);insert into t values (data);insert into t values (data);Commit;?
Dan 10.02.2014 17:03:14
Как еще примечание: будьте осторожны! этот синтаксис удаляет повторяющиеся строки! используйте, UNION ALLчтобы избежать этого (или для небольшого увеличения производительности).
chacham15 30.03.2014 17:04:32
@Shadowfax для проверки версии SQLite, посмотрите sqlite3.sqlite_version(это должно быть 3.7.x или 3.8.x), а не sqlite3.version(это просто версия модуля python).
max 8.03.2016 07:38:46

Да, SQL может сделать это, но с другим синтаксисом. SQLite документация очень хорошо, кстати. Он также скажет вам, что единственный способ вставить несколько строк - использовать оператор выбора в качестве источника данных, которые нужно вставить.

10
27.04.2015 21:20:25

Да, в SQLite 3.7.11 это поддерживается в SQLite. Из документации SQLite :

Синтаксис оператора SQLite INSERT

(когда этот ответ был изначально написан, это не было поддержано)

Для совместимости с более старыми версиями SQLite, вы можете использовать трюк , предложенный Энди и fearless_fool использования UNION, но и для 3.7.11 и позже простой синтаксис описан здесь должен быть предпочтительным.

241
23.05.2017 12:02:59
Я бы сказал, что диаграмма допускает несколько строк, так как после круглых скобок есть замкнутый цикл с запятой VALUES.
Johannes Gerer 22.03.2012 00:36:26
@JohannesGerer Они обновили это изображение с тех пор, как я его встроил. Вы можете увидеть диаграмму в то время, когда я встроил ее в интернет-архив . Фактически, всего два месяца назад они добавили поддержку нескольких строк в вставке и всего два дня назад выпустили версию с этим изменением. Я обновлю свой ответ соответственно.
Brian Campbell 22.03.2012 17:26:08
@ Брайан, не могли бы вы процитировать текст SQLite документации, что государства , что это IS возможно? Я перечитал вставку документов 3 раза и ничего не нашел о вставке нескольких строк, а только эту картинку (и ничего о запятой VALUES (...), (...)) :(
Prizoff 15.10.2012 16:53:28
@Prizoff Я связался с коммитом, в который была добавлена ​​эта поддержка , включая тестовые случаи. Вы можете видеть на диаграмме (сравните ссылку IA ), что есть выражение вокруг выражения после VALUES, указывающее, что оно может повторяться через запятую. И я связался с примечаниями к выпуску для версии, добавляющей функцию, которая гласит: «Улучшить синтаксис INSERT, чтобы разрешить вставку нескольких строк с помощью предложения VALUES».
Brian Campbell 15.10.2012 18:58:02
@Prizoff Я упоминал об этом сопровождающему SQLite, он исправил исправление, которое доступно в проекте документации . Я предполагаю, что это будет в официальной документации следующего выпуска.
Brian Campbell 15.10.2012 20:51:29

Согласно этой странице это не поддерживается:

  • 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 делает поддержку многорядную-вставку . Ричард Хипп комментирует:

«Новая многозначная вставка представляет собой просто синтаксический suger (sic) для составной вставки. Так или иначе, преимущества в производительности нет».

38
20.03.2012 22:54:11
Там нет никакого преимущества в производительности, так или иначе. - Не могли бы вы сказать мне, где вы видели это замечание? Я не мог найти это нигде.
Alix Axel 1.05.2013 02:54:37

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 является собственной транзакцией. Но если вы окружите несколько операторов INSERT с помощью BEGIN ... COMMIT, тогда все вставки будут сгруппированы в одну транзакцию. Время, необходимое для фиксации транзакции, амортизируется по всем вложенным операторам вставки, поэтому время на оператор вставки значительно сокращается.

Другой вариант - запустить PRAGMA синхронно = OFF. Эта команда заставит SQLite не ждать, пока данные достигнут поверхности диска, что сделает операции записи намного более быстрыми. Но если вы потеряете мощность в середине транзакции, файл базы данных может испортиться.

9
22.10.2009 20:39:15
Если вы посмотрите на исходный код команды SQLite .import, то это просто цикл, считывающий строку из входного файла (или tty), а затем оператор INSERT для этой строки. К сожалению, не значительно улучшена эффективность.
Bill Karwin 12.12.2012 17:12:14

Да, это возможно, но не с обычными значениями вставки, разделенными запятыми.

Попробуй это...

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

Да, это немного уродливо, но достаточно легко автоматизировать генерацию оператора из набора значений. Кроме того, кажется, вам нужно только объявить имена столбцов при первом выборе.

561
14.11.2009 12:06:02
пожалуйста, UNION ALLне используйте UNION. За исключением случаев, когда вы хотите удалить дубликаты.
Benoit 23.02.2012 13:57:27
Если вы хотите, чтобы идентификаторы автоматически увеличивались, присвойте им значение NULL.
lenooh 8.09.2015 21:33:55

Как говорили другие авторы, SQLite не поддерживает этот синтаксис. Я не знаю, являются ли составные INSERT частью стандарта SQL, но по моему опыту они не реализованы во многих продуктах.

Кроме того, вы должны знать, что производительность INSERT в SQLite значительно улучшится, если вы включите несколько INSERT в явную транзакцию.

10
14.11.2009 12:21:19

Вы не можете, но я не думаю, что вы что-то упускаете.

Поскольку вы вызываете sqlite всегда в процессе, по производительности почти не имеет значения, выполняете ли вы 1 оператор вставки или 100 операторов вставки. Однако фиксация занимает много времени, поэтому поместите эти 100 вставок в транзакцию.

Sqlite намного быстрее, когда вы используете параметризованные запросы (требуется гораздо меньше разбора), поэтому я бы не стал объединять большие выражения, подобные этому:

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

Их нужно анализировать снова и снова, потому что каждое объединенное утверждение отличается.

6
14.11.2009 12:26:26

Если вы используете плагин Sqlite manager firefox, он поддерживает массовые вставки из INSERTоператоров SQL.

Infact это не поддерживает это, но Браузер Sqlite делает (работает на Windows, OS X, Linux)

2
28.01.2010 00:28:43

В MySQL Lite вы не можете вставить несколько значений, но вы можете сэкономить время, открыв соединение только один раз, а затем выполнив все вставки и затем закрыв соединение. Это экономит много времени

6
21.05.2010 06:05:17

Я написал некоторый код ruby ​​для генерации одиночной многострочной вставки из 500 элементов из серии операторов вставки, что было значительно быстрее, чем выполнение отдельных вставок. Затем я попытался просто обернуть несколько вставок в одну транзакцию и обнаружил, что могу добиться такого же ускорения при значительно меньшем количестве кода.

BEGIN TRANSACTION;
INSERT INTO table VALUES (1,1,1,1);
INSERT INTO table VALUES (2,2,2,2);
...
COMMIT;
57
6.03.2011 06:26:04
но это не работает в коде, в то время как он работает непосредственно в диспетчере SQLite. В коде он вставляет только 1-й ряд :(
Vaibhav Saran 16.05.2013 07:27:43
Я использую этот стиль вставки в моем коде, и он отлично работает. Вам нужно только убедиться, что вы отправляете ВСЕ SQL одновременно. Для меня это было огромным увеличением скорости, но мне любопытно, будет ли принятый ответ быстрее или медленнее, чем этот?
Dan 10.02.2014 17:08:27
Этот подход очень хорошо масштабируется при изменении нескольких таблиц в одной транзакции, что я часто делаю при пакетной загрузке базы данных.
Frank 19.10.2015 00:38:56
Обратите внимание, что этот подход не будет работать, если вам нужно использовать привязки . Механизм SQLite3 предполагает, что все ваши привязки должны применяться к первому оператору, и игнорирует следующие операторы. Посмотрите этот ТАК вопрос для более подробного объяснения.
Philippe Hebert 8.03.2018 18:27:04

Проблема с использованием транзакции заключается в том, что вы блокируете таблицу также для чтения. Так что если у вас действительно много данных для вставки и вам нужен доступ к ним, например, для предварительного просмотра или около того, этот способ не работает.

Проблема с другим решением заключается в том, что вы теряете порядок вставки

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

В sqlite данные будут храниться a, b, c, d ...

5
7.03.2011 22:32:13
INSERT INTO tabela(coluna1,coluna2) 
SELECT 'texto','outro'
UNION ALL 
SELECT 'mais texto','novo texto';
4
5.08.2011 23:22:15

Начиная с версии 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

5
4.06.2012 18:21:46

Алекс прав: утверждение "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
8
19.06.2012 07:11:39
INSERT INTO TABLE_NAME 
            (DATA1, 
             DATA2) 
VALUES      (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2), 
            (VAL1, 
             VAL2); 
7
11.10.2012 14:29:56

У меня есть запрос, как показано ниже, но с драйвером ODBC SQLite имеет ошибку с "," говорит он. Я запускаю vbscript в HTA (HTML-приложение).

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())
2
21.10.2012 19:48:02

У fearless_fool отличный ответ для старых версий. Я просто хотел добавить, что вам нужно убедиться, что у вас есть все столбцы в списке. Поэтому, если у вас есть 3 столбца, вы должны убедиться, что select действует на 3 столбца.

Пример: у меня есть 3 столбца, но я хочу вставить только 2 столбца данных. Предположим, меня не волнует первый столбец, потому что это стандартный целочисленный идентификатор. Я мог бы сделать следующее ...

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)

8
12.12.2012 17:07:03

На sqlite 3.7.2:

INSERT INTO table_name (column1, column2) 
                SELECT 'value1', 'value1' 
          UNION SELECT 'value2', 'value2' 
          UNION SELECT 'value3', 'value3' 

и так далее

2
17.03.2013 14:29:07

Я могу сделать запрос динамическим. Это мой стол:

CREATE TABLE "tblPlanner" ("probid" text,"userid" TEXT,"selectedtime" DATETIME,"plannerid" TEXT,"isLocal" BOOL,"applicationid" TEXT, "comment" TEXT, "subject" TEXT)

и я получаю все данные через JSON, так что после получения всего внутри NSArrayя следовал этому:

    NSMutableString *query = [[NSMutableString alloc]init];
    for (int i = 0; i < arr.count; i++)
    {
        NSString *sqlQuery = nil;
        sqlQuery = [NSString stringWithFormat:@" ('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@'),",
                    [[arr objectAtIndex:i] objectForKey:@"plannerid"],
                    [[arr objectAtIndex:i] objectForKey:@"probid"],
                    [[arr objectAtIndex:i] objectForKey:@"userid"],
                    [[arr objectAtIndex:i] objectForKey:@"selectedtime"],
                    [[arr objectAtIndex:i] objectForKey:@"isLocal"],
                    [[arr objectAtIndex:i] objectForKey:@"subject"],
                    [[arr objectAtIndex:i] objectForKey:@"comment"],
                    [[NSUserDefaults standardUserDefaults] objectForKey:@"applicationid"]
                    ];
        [query appendString:sqlQuery];
    }
    // REMOVING LAST COMMA NOW
    [query deleteCharactersInRange:NSMakeRange([query length]-1, 1)];

    query = [NSString stringWithFormat:@"insert into tblPlanner (plannerid, probid, userid, selectedtime, isLocal, applicationid, subject, comment) values%@",query];

И, наконец, выходной запрос:

insert into tblPlanner (plannerid, probid, userid, selectedtime, isLocal, applicationid, subject, comment) values 
<append 1>
('pl1176428260', '', 'US32552', '2013-06-08 12:00:44 +0000', '0', 'subj', 'Hiss', 'ap19788'),
<append 2>
('pl2050411638', '', 'US32552', '2013-05-20 10:45:55 +0000', '0', 'TERI', 'Yahoooooooooo', 'ap19788'), 
<append 3>
('pl1828600651', '', 'US32552', '2013-05-21 11:33:33 +0000', '0', 'test', 'Yest', 'ap19788'),
<append 4>
('pl549085534', '', 'US32552', '2013-05-19 11:45:04 +0000', '0', 'subj', 'Comment', 'ap19788'), 
<append 5>
('pl665538927', '', 'US32552', '2013-05-29 11:45:41 +0000', '0', 'subj', '1234567890', 'ap19788'), 
<append 6>
('pl1969438050', '', 'US32552', '2013-06-01 12:00:18 +0000', '0', 'subj', 'Cmt', 'ap19788'),
<append 7>
('pl672204050', '', 'US55240280', '2013-05-23 12:15:58 +0000', '0', 'aassdd', 'Cmt', 'ap19788'), 
<append 8>
('pl1019026150', '', 'US32552', '2013-06-08 12:15:54 +0000', '0', 'exists', 'Cmt', 'ap19788'), 
<append 9>
('pl790670523', '', 'US55240280', '2013-05-26 12:30:21 +0000', '0', 'qwerty', 'Cmt', 'ap19788')

который хорошо работает через код, и я могу успешно сохранить все в SQLite.

До этого я делал UNIONдинамические запросы, но это приводило к некоторой синтаксической ошибке. В любом случае, это хорошо для меня.

2
16.05.2013 08:42:07

Начиная с версии 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: Пожалуйста, +1 к ответу / ответу Брайана Кэмпбелла. не мой! Он представил решение первым.

14
16.05.2013 16:13:14

Вы можете использовать InsertHelper, это легко и быстро

документация: http://developer.android.com/reference/android/database/DatabaseUtils.InsertHelper.html

учебное пособие: http://www.outofwhatbox.com/blog/2010/12/android-using-databaseutils-inserthelper-for-faster-insertions-into-sqlite-database/

Изменить: InsertHelper устарела начиная с уровня API 17

0
26.09.2013 22:30:00
InsertHelper устарел с API 17
GregM 26.09.2013 21:52:03

Я удивлен, что никто не упомянул подготовленные заявления . Если вы не используете SQL самостоятельно, а не на каком-либо другом языке, то я думаю, что подготовленные операторы, заключенные в транзакцию , будут наиболее эффективным способом вставки нескольких строк.

2
12.04.2014 10:11:08
Подготовленные заявления всегда хорошая идея, но никак не связаны с вопросом, который задает ФП. Он спрашивает, каков основной синтаксис для вставки нескольких данных в одном выражении.
Lakey 10.07.2015 19:17:42

Если вы используете оболочку bash, вы можете использовать это:

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.

-1
28.07.2014 15:02:15
Это не работает, если вы хотите вставить полезные данные.
CL. 28.07.2014 14:33:53
@CL. неправда. Вы можете смешать его со случайными датами и идентификаторами, как вы хотите.
test30 28.07.2014 15:03:25