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

Question

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

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

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

Answer 1

Обновить

Как указывает здесь 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 ответ Энди, не мой! Он представил решение первым.

Answer 2

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

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

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

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

Answer 3

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

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

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

Answer 4

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

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

Answer 5

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

• 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) для составной вставки. Там нет преимуществ в производительности, так или иначе".

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

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

Answer 10

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

Answer 11

У 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)

Answer 12

Алекс прав: утверждение "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

Answer 13

Простой. Самоочевидно.

Тест на версии 3.36.0 20.11.21.

      CREATE TEMP TABLE x (col1 TEXT, col2 TEXT, col3 TEXT);

INSERT INTO x 
VALUES 
('xx','yy','cc'),
('xx','yy','cc'),
('xx','yy','cc'),
('xx','yy','cc'),
('xx','yy','cc'),
('xx','yy','cc');

SELECT * FROM x;

Выход:

      col1|col2|col3|
----+----+----+
xx  |yy  |cc  |
xx  |yy  |cc  |
xx  |yy  |cc  |
xx  |yy  |cc  |
xx  |yy  |cc  |
xx  |yy  |cc  |

Проверка версии:

      SELECT sqlite_version();

Выход:

      sqlite_version()|
----------------+
3.36.0          |

Кто-то может сказать - все ответы "..СОЮЗ.." устарели; тем не менее, они очень полезны. Иногда мы все получаем на наших столах «взрыв из прошлого», и тогда записка 15-летней давности спасает нас.

Answer 14

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

Answer 15

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

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

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

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

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

Answer 16

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

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

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

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

Answer 17

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

Answer 18

INSERT INTO tabela(coluna1,coluna2) 
SELECT 'texto','outro'
UNION ALL 
SELECT 'mais texto','novo texto';

Answer 19

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

Answer 20

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

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 динамические запросы, но это привело к некоторой синтаксической ошибке. В любом случае, это хорошо для меня.

Answer 21

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

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

Answer 22

На sqlite 3.7.2:

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

и так далее

Answer 23

У меня есть запрос, как показано ниже, но с драйвером 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())

Answer 24

Вы можете использовать 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

Answer 25

Например, есть ниже:

      CREATE TABLE person (
  id INTEGER PRIMARY KEY, 
  name TEXT,
  age INTEGER
);

Затем вы можете вставить несколько строк вpersonтаблица с этими способами ниже. *ДляINTEGER PRIMARY KEYстолбецid, вы можете опустить это или поставитьNULLдля автоматического увеличения на 1:

      INSERT INTO person (id, name, age) 
VALUES (1, 'John', 36), (2, 'David', 24), (3, 'Lisa', 18);

      INSERT INTO person (name, age) 
VALUES ('John', 36), ('David', 24), ('Lisa', 18);

      INSERT INTO person (id, name, age) 
VALUES (NULL, 'John', 36), (NULL, 'David', 24), (NULL, 'Lisa', 18);

      INSERT INTO person 
VALUES (1, 'John', 36), (2, 'David', 24), (3, 'Lisa', 18);

      INSERT INTO person
VALUES (NULL, 'John', 36), (NULL, 'David', 24), (NULL, 'Lisa', 18);

Затем вы можете получить тот же результат, что и ниже. * Мой ответ объясняет, как отображать имена столбцов:

      sqlite> .headers on  
sqlite> .mode box
sqlite> SELECT * FROM person;
┌────┬───────┬─────┐
│ id │ name  │ age │
├────┼───────┼─────┤
│ 1  │ John  │ 36  │
│ 2  │ David │ 24  │
│ 3  │ Lisa  │ 18  │
└────┴───────┴─────┘

Answer 26

Если вы используете оболочку 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.