È ansible inserire più righe alla volta in un database SQLite?

In MySQL puoi inserire più righe come questa:

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

Tuttavia, sto ricevendo un errore quando provo a fare qualcosa di simile. È ansible inserire più righe alla volta in un database SQLite? Qual è la syntax per farlo?

aggiornare

Come sottolinea BrianCampbell qui , SQLite 3.7.11 e versioni successive ora supporta la syntax più semplice del post originale . Tuttavia, l’approccio mostrato è ancora appropriato se si desidera la massima compatibilità tra i database legacy.

risposta originale

Se avessi i privilegi, avrei dovuto rispondere ad andy : puoi inserire più righe in SQLite, hai solo bisogno di una syntax diversa . Per rendere perfettamente chiaro, l’esempio MySQL OP:

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

Questo può essere riformulato in SQLite come:

  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' 

una nota sulla performance

Inizialmente ho utilizzato questa tecnica per caricare in modo efficiente dataset di grandi dimensioni da Ruby on Rails. Tuttavia , come osserva Jaime Cook , non è chiaro che si tratti di singoli INSERTs più rapidi all’interno di una singola transazione:

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

Se l’efficienza è il tuo objective, dovresti provare prima questo.

una nota su UNION vs UNION ALL

Come diverse persone hanno commentato, se si utilizza UNION ALL (come mostrato sopra), verranno inserite tutte le righe, quindi in questo caso si otterrebbero quattro righe di data1, data2 . Se si omette il valore ALL , allora le righe duplicate verranno eliminate (e l’operazione sarà presumibilmente un po ‘più lenta). Stiamo usando UNION ALL poiché è più simile alla semantica del post originale.

in chiusura

PS: per favore +1 risposta di andy , non mia! Ha presentato la soluzione per prima.

Sì, è ansible, ma non con i soliti valori di inserimento separati da virgola.

Prova questo…

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

Sì, è un po ‘brutto ma abbastanza facile da automatizzare la generazione della dichiarazione da un insieme di valori. Inoltre, sembra che devi solo dichiarare i nomi delle colonne nella prima selezione.

Sì, a partire da SQLite 3.7.11 questo è supportato in SQLite. Dalla documentazione SQLite :

Sintassi dell'istruzione SQLite INSERT

(quando questa risposta è stata originariamente scritta, questa non era supportata)

Per compatibilità con le versioni precedenti di SQLite, è ansible utilizzare il trucco suggerito da andy e fearless_fool utilizzando UNION , ma per 3.7.11 e versioni successive è preferibile la syntax più semplice descritta qui.

Ho scritto un codice ruby per generare un singolo inserto multi-riga da 500 elementi da una serie di istruzioni di inserimento che era notevolmente più veloce rispetto all’esecuzione dei singoli inserti. Poi ho provato semplicemente ad avvolgere gli inserti multipli in una singola transazione e ho scoperto che avrei potuto ottenere lo stesso tipo di accelerazione con un numero considerevolmente inferiore di codice.

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

Secondo questa pagina non è supportato:

  • 2007-12-03: INSERT multipiattaforma, ovvero INSERT composto non supportato.
  INSERT INTO table (col1, col2) VALUES ('row1col1', 'row1col2'), ('row2col1', 'row2col2'), ... 

In realtà, secondo lo standard SQL92, un’espressione VALUES dovrebbe essere in grado di stare su se stessa. Ad esempio, il seguente dovrebbe restituire una tabella a una colonna con tre righe: VALUES 'john', 'mary', 'paul';

A partire dalla versione 3.7.11 SQLite supporta l’ inserimento multi-riga . Richard Hipp commenta:

“Il nuovo inserto multivalore è semplicemente sintattico suger (sic) per l’inserto composto: non ci sono vantaggi prestazionali in un modo o nell’altro”.

Dalla versione 2012-03-20 (3.7.11), sqlite supporta la seguente syntax INSERT:

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

Leggi la documentazione: http://www.sqlite.org/lang_insert.html

PS: per favore, fai +1 alla risposta / risposta di Brian Campbell. non mio! Ha presentato la soluzione per prima.

Come hanno detto gli altri utenti, SQLite non supporta questa syntax. Non so se gli INSERT composti fanno parte dello standard SQL, ma nella mia esperienza non sono implementati in molti prodotti.

Per inciso, è necessario tenere presente che le prestazioni INSERT in SQLite sono notevolmente migliorate se si inseriscono più INSERT in una transazione esplicita.

Sì, SQL può farlo, ma con una syntax diversa. A proposito, la documentazione sqlite è piuttosto buona. Ti dirà anche che l’unico modo per inserire più righe è usare un’istruzione select come origine dei dati da inserire.

Sqlite3 non può farlo direttamente in SQL se non tramite una SELECT, e mentre SELECT può restituire una “riga” di espressioni, non conosco alcun modo per farlo restituire una colonna fasulla.

Tuttavia, la CLI può farlo:

 .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> 

Se fai un loop su un INSERT, invece di usare il comando CLI .import , assicurati di seguire il consiglio nelle FAQ sqlite per la velocità INSERT:

Per impostazione predefinita, ciascuna istruzione INSERT è la propria transazione. Ma se si circondano più istruzioni INSERT con BEGIN … COMMIT, tutti gli inserimenti vengono raggruppati in un’unica transazione. Il tempo necessario per eseguire il commit della transazione viene ammortizzato su tutte le istruzioni di inserimento allegate e quindi il tempo per la dichiarazione di inserimento viene notevolmente ridotto.

Un’altra opzione è eseguire PRAGMA synchronous = OFF. Questo comando farà sì che SQLite non attenda che i dati raggiungano la superficie del disco, il che renderà le operazioni di scrittura molto più veloci. Ma se perdi energia nel bel mezzo di una transazione, il tuo file di database potrebbe essere corrotto.

Alex ha ragione: l’affermazione “seleziona … unione” perderà l’ordine che è molto importante per alcuni utenti. Anche quando inserisci un ordine specifico, sqlite cambia le cose, quindi preferisci utilizzare le transazioni se l’ordine degli inserti è importante.

 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 

fearless_fool ha un’ottima risposta per le versioni precedenti. Volevo solo aggiungere che è necessario assicurarsi di avere tutte le colonne elencate. Quindi, se hai 3 colonne, devi assicurarti che la selezione funzioni su 3 colonne.

Esempio: ho 3 colonne ma voglio solo inserire 2 colonne di dati. Supponiamo che non mi interessi della prima colonna perché è un ID intero standard. Potrei fare quanto segue …

 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' 

Nota: ricorda che l’istruzione “seleziona … unione” perderà l’ordine. (Da AG1)

Non puoi, ma non credo che ti manchi qualcosa.

Poiché si chiama sqlite sempre in elaborazione, non importa in termini di prestazioni se si esegue 1 istruzione di inserimento o 100 istruzioni di inserimento. Tuttavia, il commit impiega molto tempo per inserire quei 100 inserti all’interno di una transazione.

Sqlite è molto più veloce quando si usano query parametrizzate (molto meno l’analisi è necessaria), quindi non concatenerei grandi affermazioni come questa:

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

Devono essere analizzati ancora e ancora perché ogni affermazione concatenata è diversa.

in mysql lite non puoi inserire più valori, ma puoi risparmiare tempo aprendo la connessione solo una volta e poi facendo tutti gli inserimenti e quindi chiudendo la connessione. Risparmia molto tempo

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

Il problema con l’utilizzo della transazione è che si blocca la tabella anche per la lettura. Quindi se hai davvero molti dati da inserire e hai bisogno di accedere ai tuoi dati, per esempio un’anteprima o meno, in questo modo non funziona bene.

Il problema con l’altra soluzione è che si perde l’ordine di inserimento

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

Nel sqlite i dati saranno memorizzati a, b, c, d …

A partire dalla versione 3.7.11 SQLite supporta l’inserimento multi-riga. Richard Hipp commenta:

Sto usando 3.6.13

Io comando così:

 insert into xtable(f1,f2,f3) select v1 as f1, v2 as f2, v3 as f3 union select nextV1+, nextV2+, nextV3+ 

Con 50 record inseriti alla volta, ci vuole solo un secondo o meno.

È vero che usare sqlite per inserire più righe alla volta è molto ansible. Per @Andy ha scritto.

grazie Andy +1

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

Se si utilizza il plugin firefox di Sqlite manager , supporta gli inserimenti di massa da INSERT SQL INSERT .

Infatti non supporta questo, ma Sqlite Browser (funziona su Windows, OS X, Linux)

Ho una query come sotto, ma con SQLite driver ODBC ha un errore con “,” dice. Corro vbscript in HTA (applicazione 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()) 

Su sqlite 3.7.2:

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

e così via

Sono in grado di rendere dynamic la query. Questa è la mia tabella:

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

e sto ricevendo tutti i dati attraverso un JSON , quindi dopo aver ottenuto tutto all’interno di un NSArray ho seguito questo:

  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]; 

E infine la query di output è questa:

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

che funziona bene anche attraverso il codice e sono in grado di salvare tutto in SQLite con successo.

Prima di questo ho reso la query di UNION roba dynamic ma ho iniziato a dare qualche errore di syntax. Ad ogni modo, questo sta funzionando bene per me.

Sono sorpreso che nessuno abbia menzionato dichiarazioni preparate . A meno che non si stia utilizzando SQL da solo e non in qualsiasi altra lingua, allora penserei che le istruzioni preparate racchiuse in una transazione sarebbero il modo più efficace di inserire più righe.

Se stai usando bash shell puoi usare questo:

 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;"' 

O se sei in CLI sqlite, allora devi fare questo:

 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; 

Come funziona? Lo usa se la tab tabella:

 id int ------ 1 2 

quindi select a.id, b.id from tab a, tab b restituisce

 a.id int | b.id int ------------------ 1 | 1 2 | 1 1 | 2 2 | 2 

e così via. Dopo la prima esecuzione inseriamo 2 righe, quindi 2 ^ 3 = 8. (tre perché abbiamo tab a, tab b, tab c )

Dopo la seconda esecuzione inseriamo ulteriori (2+8)^3=1000 righe

All’inizio, inseriamo il max(1000^3, 5e5)=500000 righe e così via …

Questo è il metodo più rapido per me di popolamento del database SQLite.