MySQL
INSERIRE
Ricerca…
Sintassi
INSERISCI [LOW_PRIORITY | RITARDATO | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name, ...)] [(col_name, ...)] {VALUES | VALUE} ({expr | DEFAULT}, ...), (...), ... [ON DUPLICATE KEY UPDATE col_name = expr [, nome_col = expr] ...]
INSERISCI [LOW_PRIORITY | RITARDATO | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name, ...)] SET col_name = {expr | DEFAULT}, ... [ON DUPLICATE KEY UPDATE col_name = expr [, nome_col = expr] ...]
INSERISCI [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name, ...)] [(nome_col, ...)] SELECT ... [ON DUPLICATE KEY UPDATE col_name = expr [, nome_col = expr] ...]
Un'espr espressione può riferirsi a qualsiasi colonna che è stata impostata in precedenza in una lista valori. Ad esempio, puoi farlo perché il valore di col2 si riferisce a col1, che è stato precedentemente assegnato:
INSERISCI IN VALORI tbl_name (col1, col2) (15, col1 * 2);Le istruzioni INSERT che utilizzano la sintassi VALUES possono inserire più righe. Per fare ciò, includere più elenchi di valori di colonna, ciascuno racchiuso tra parentesi e separati da virgole. Esempio:
INSERISCI IN VALORI tbl_name (a, b, c) (1,2,3), (4,5,6), (7,8,9);L'elenco dei valori per ogni riga deve essere racchiuso tra parentesi. La seguente dichiarazione è illegale perché il numero di valori nell'elenco non corrisponde al numero di nomi di colonna:
INSERISCI IN VALORI tbl_name (a, b, c) (1,2,3,4,5,6,7,8,9);INSERIRE ... SELEZIONA Sintassi
INSERISCI [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name, ...)] [(nome_col, ...)] SELECT ... [ON DUPLICATE KEY UPDATE col_name = expr, ...]Con INSERT ... SELECT, puoi inserire rapidamente molte righe in una tabella da una o più tabelle. Per esempio:
INSERISCI IN tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id> 100;
Osservazioni
Inserto di base
INSERT INTO `table_name` (`field_one`, `field_two`) VALUES ('value_one', 'value_two');
In questo banale esempio, table_name è dove i dati devono essere aggiunti, field_one e field_two sono campi per impostare i dati contro, value_one e value_two sono i dati da fare rispettivamente contro field_one e field_two .
È buona norma elencare i campi in cui stai inserendo i dati all'interno del tuo codice, come se la tabella cambi e nuove colonne vengano aggiunte, il tuo inserto si spezzerebbe se non fossero lì
INSERT, ON DUPLICATE KEY UPDATE
INSERT INTO `table_name`
(`index_field`, `other_field_1`, `other_field_2`)
VALUES
('index_value', 'insert_value', 'other_value')
ON DUPLICATE KEY UPDATE
`other_field_1` = 'update_value',
`other_field_2` = VALUES(`other_field_2`);
Questo INSERT in table_name i valori specificati, ma se la chiave univoca esiste già, aggiornerà il other_field_1 per avere un nuovo valore.
A volte, quando si aggiorna la chiave duplicata, è utile usare VALUES() per accedere al valore originale che è stato passato a INSERT invece di impostare direttamente il valore. In questo modo, puoi impostare valori diversi usando INSERT e UPDATE . Vedere l'esempio sopra dove other_field_1 è impostato su insert_value su INSERT o su update_value su UPDATE mentre other_field_2 è sempre impostato su other_value .
Fondamentale per l'Insert su Duplicate Key Update (IODKU) da utilizzare è lo schema contenente una chiave univoca che segnalerà uno scontro duplicato. Questa chiave univoca può essere una chiave primaria o meno. Può essere una chiave univoca su una singola colonna o una multi-colonna (chiave composita).
Inserimento di più righe
INSERT INTO `my_table` (`field_1`, `field_2`) VALUES
('data_1', 'data_2'),
('data_1', 'data_3'),
('data_4', 'data_5');
Questo è un modo semplice per aggiungere più righe contemporaneamente con una sola istruzione INSERT .
Questo tipo di inserto "batch" è molto più veloce dell'inserimento di righe una per una. In genere, l'inserimento di 100 righe in un singolo batch in questo modo è 10 volte più veloce di inserirle tutte singolarmente.
Ignorando le righe esistenti
Quando si importano insiemi di dati di grandi dimensioni, in determinate circostanze potrebbe essere preferibile saltare le righe che di solito causano il fallimento della query a causa di un vincolo di colonna, ad esempio chiavi primarie duplicate. Questo può essere fatto usando INSERT IGNORE .
Considera il seguente database di esempio:
SELECT * FROM `people`;
--- Produces:
+----+------+
| id | name |
+----+------+
| 1 | john |
| 2 | anna |
+----+------+
INSERT IGNORE INTO `people` (`id`, `name`) VALUES
('2', 'anna'), --- Without the IGNORE keyword, this record would produce an error
('3', 'mike');
SELECT * FROM `people`;
--- Produces:
+----+--------+
| id | name |
+----+--------+
| 1 | john |
| 2 | anna |
| 3 | mike |
+----+--------+
La cosa importante da ricordare è che INSERTO IGNORE salterà anche silenziosamente altri errori, ecco cosa dice la documentazione ufficiale di Mysql:
Le conversioni di dati che potrebbero causare errori interrompono l'istruzione se IGNORE non è> specificato. Con IGNORE, i valori non validi vengono adattati ai valori più vicini e> inseriti; gli avvertimenti sono prodotti ma la dichiarazione non abortisce.
Nota: - La sezione seguente viene aggiunta per completezza, ma non è considerata la migliore pratica (questo fallirebbe, ad esempio, se un'altra colonna fosse stata aggiunta alla tabella).
Se si specifica il valore della colonna corrispondente per tutte le colonne nella tabella, è possibile ignorare l'elenco delle colonne INSERT come segue:
INSERT INTO `my_table` VALUES
('data_1', 'data_2'),
('data_1', 'data_3'),
('data_4', 'data_5');
INSERT SELECT (Inserimento di dati da un'altra tabella)
Questo è il modo base per inserire dati da un'altra tabella con l'istruzione SELECT.
INSERT INTO `tableA` (`field_one`, `field_two`)
SELECT `tableB`.`field_one`, `tableB`.`field_two`
FROM `tableB`
WHERE `tableB`.clmn <> 'someValue'
ORDER BY `tableB`.`sorting_clmn`;
È possibile selezionare SELECT * FROM , ma la tableA e la tableB devono avere il conteggio delle colonne corrispondente e i tipi di dati corrispondenti.
Le colonne con AUTO_INCREMENT sono trattate come nella clausola INSERT con VALUES .
Questa sintassi semplifica il riempimento di tabelle (temporanee) con dati di altre tabelle, ancor più quando i dati devono essere filtrati sull'inserto.
INSERISCI con AUTO_INCREMENT + LAST_INSERT_ID ()
Quando una tabella ha una PRIMARY KEY AUTO_INCREMENT , normalmente non viene inserita in quella colonna. Invece, specifica tutte le altre colonne, quindi chiedi quale fosse il nuovo ID.
CREATE TABLE t (
id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
this ...,
that ...,
PRIMARY KEY(id) );
INSERT INTO t (this, that) VALUES (..., ...);
SELECT LAST_INSERT_ID() INTO @id;
INSERT INTO another_table (..., t_id, ...) VALUES (..., @id, ...);
Nota che LAST_INSERT_ID() è legato alla sessione, quindi anche se più connessioni si stanno inserendo nella stessa tabella, ognuna con il proprio ID.
La tua API client probabilmente ha un modo alternativo per ottenere LAST_INSERT_ID() senza eseguire effettivamente una SELECT e restituire il valore al client invece di lasciarlo in una @variable all'interno di MySQL. Di solito è preferibile.
Esempio più lungo e dettagliato
L'utilizzo "normale" di IODKU è di attivare "chiave duplicata" in base a una chiave UNIQUE , non alla AUTO_INCREMENT PRIMARY KEY . Il seguente dimostra tale. Si noti che non fornisce l' id nell'INSERT.
Installazione per gli esempi da seguire:
CREATE TABLE iodku (
id INT AUTO_INCREMENT NOT NULL,
name VARCHAR(99) NOT NULL,
misc INT NOT NULL,
PRIMARY KEY(id),
UNIQUE(name)
) ENGINE=InnoDB;
INSERT INTO iodku (name, misc)
VALUES
('Leslie', 123),
('Sally', 456);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
+----+--------+------+
| id | name | misc |
+----+--------+------+
| 1 | Leslie | 123 |
| 2 | Sally | 456 |
+----+--------+------+
Il caso di IODKU che esegue un "aggiornamento" e LAST_INSERT_ID() recupera l' id rilevante:
INSERT INTO iodku (name, misc)
VALUES
('Sally', 3333) -- should update
ON DUPLICATE KEY UPDATE -- `name` will trigger "duplicate key"
id = LAST_INSERT_ID(id),
misc = VALUES(misc);
SELECT LAST_INSERT_ID(); -- picking up existing value
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
Il caso in cui IODKU esegue un "inserimento" e LAST_INSERT_ID() recupera il nuovo id :
INSERT INTO iodku (name, misc)
VALUES
('Dana', 789) -- Should insert
ON DUPLICATE KEY UPDATE
id = LAST_INSERT_ID(id),
misc = VALUES(misc);
SELECT LAST_INSERT_ID(); -- picking up new value
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+
Contenuto della tabella risultante:
SELECT * FROM iodku;
+----+--------+------+
| id | name | misc |
+----+--------+------+
| 1 | Leslie | 123 |
| 2 | Sally | 3333 | -- IODKU changed this
| 3 | Dana | 789 | -- IODKU added this
+----+--------+------+
ID_INCREMENT perso
Diverse funzioni di "inserimento" possono "bruciare" id. Ecco un esempio, utilizzando InnoDB (altri motori potrebbero funzionare in modo diverso):
CREATE TABLE Burn (
id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
name VARCHAR(99) NOT NULL,
PRIMARY KEY(id),
UNIQUE(name)
) ENGINE=InnoDB;
INSERT IGNORE INTO Burn (name) VALUES ('first'), ('second');
SELECT LAST_INSERT_ID(); -- 1
SELECT * FROM Burn ORDER BY id;
+----+--------+
| 1 | first |
| 2 | second |
+----+--------+
INSERT IGNORE INTO Burn (name) VALUES ('second'); -- dup 'IGNOREd', but id=3 is burned
SELECT LAST_INSERT_ID(); -- Still "1" -- can't trust in this situation
SELECT * FROM Burn ORDER BY id;
+----+--------+
| 1 | first |
| 2 | second |
+----+--------+
INSERT IGNORE INTO Burn (name) VALUES ('third');
SELECT LAST_INSERT_ID(); -- now "4"
SELECT * FROM Burn ORDER BY id; -- note that id=3 was skipped over
+----+--------+
| 1 | first |
| 2 | second |
| 4 | third | -- notice that id=3 has been 'burned'
+----+--------+
Pensaci (all'incirca) in questo modo: prima l'inserto cerca di vedere quante righe potrebbero essere inserite. Quindi prendi tutti quei valori dall'auto_increment per quella tabella. Infine, inserisci le righe, usando gli ID secondo necessità e masterizzando gli eventuali rimanenti.
L'unica volta che i rimanenti sono recuperabili è se il sistema viene arrestato e riavviato. Al riavvio, viene effettivamente eseguito MAX(id) . Questo può riutilizzare id che sono stati masterizzati o liberati da DELETEs con l'id più alto.
Essenzialmente qualsiasi sapore di INSERT (incluso REPLACE , che è DELETE + INSERT ) può masterizzare id. In InnoDB, la variabile globale (non di sessione!) innodb_autoinc_lock_mode può essere utilizzata per controllare parte di ciò che sta accadendo.
Quando si "normalizzano" le lunghe stringhe in un AUTO INCREMENT id , la masterizzazione può facilmente verificarsi. Ciò potrebbe portare a un sovraccarico delle dimensioni INT hai scelto.