MySQL
INSERT
Zoeken…
Syntaxis
PLAATS [LOW_PRIORITY | VERTRAGING | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name, ...)] [(col_name, ...)] {WAARDEN | VALUE} ({expr | DEFAULT}, ...), (...), ... [BIJ DUPLICATE KEY UPDATE col_name = expr [, col_name = expr] ...]
PLAATS [LOW_PRIORITY | VERTRAGING | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partitie_naam, ...)] SET col_name = {expr | DEFAULT}, ... [BIJ DUPLICATE KEY UPDATE col_name = expr [, col_name = expr] ...]
PLAATS [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name, ...)] [(col_name, ...)] SELECTEER ... [ON DUPLICATE KEY UPDATE col_name = expr [, col_name = expr] ...]
Een expressie expr kan verwijzen naar elke kolom die eerder in een waardelijst is ingesteld. U kunt dit bijvoorbeeld doen omdat de waarde voor col2 verwijst naar col1, die eerder is toegewezen:
PLAATS IN tbl_name (col1, col2) WAARDEN (15, col1 * 2);INSERT-instructies die de VALUES-syntaxis gebruiken, kunnen meerdere rijen invoegen. Om dit te doen, neemt u meerdere lijsten met kolomwaarden op, elk ingesloten tussen haakjes en gescheiden door komma's. Voorbeeld:
PLAATS IN tbl_name (a, b, c) WAARDEN (1,2,3), (4,5,6), (7,8,9);De waardenlijst voor elke rij moet tussen haakjes staan. De volgende instructie is illegaal omdat het aantal waarden in de lijst niet overeenkomt met het aantal kolomnamen:
PLAATS IN tbl_name (a, b, c) WAARDEN (1,2,3,4,5,6,7,8,9);INSERT ... SELECT Syntax
PLAATS [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name, ...)] [(col_name, ...)] SELECTEER ... [ON DUPLICATE KEY UPDATE col_name = expr, ...]Met INSERT ... SELECT kunt u snel meerdere rijen in een tabel invoegen vanuit een of meerdere tabellen. Bijvoorbeeld:
PLAATS IN tbl_temp2 (fld_id) SELECTEER tbl_temp1.fld_order_id VANAF tbl_temp1 WAAR tbl_temp1.fld_order_id> 100;
Opmerkingen
Basic Insert
INSERT INTO `table_name` (`field_one`, `field_two`) VALUES ('value_one', 'value_two');
In dit triviale voorbeeld is table_name waar de gegevens moeten worden toegevoegd, field_one en field_two zijn velden om gegevens tegen in te stellen, en value_one en value_two zijn de gegevens die respectievelijk moeten worden uitgevoerd tegen field_one en field_two .
Het is een goede gewoonte om de velden waarin u gegevens invoegt in uw code te vermelden, alsof de tabel verandert en nieuwe kolommen worden toegevoegd, uw invoeging zou breken als ze er niet waren
PLAATSEN, BIJ DUPLICATIE SLEUTEL 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`);
Dit zal INSERT in table_name de opgegeven waarden, maar als de unieke sleutel al bestaat, zal het actualiseren van de other_field_1 om een nieuwe waarde te hebben.
Soms is het handig om bij het bijwerken van een dubbele sleutel VALUES() te gebruiken om toegang te krijgen tot de oorspronkelijke waarde die aan de INSERT is doorgegeven in plaats van de waarde rechtstreeks in te stellen. Op deze manier kunt u verschillende waarden instellen met INSERT en UPDATE . Zie het bovenstaande voorbeeld waarbij other_field_1 is ingesteld op insert_value op INSERT of op update_value op UPDATE terwijl other_field_2 altijd is ingesteld op other_value .
Cruciaal voor de IODKU (Insert on Duplicate Key Update) om te werken is het schema met een unieke sleutel die een dubbele clash aangeeft. Deze unieke sleutel kan een primaire sleutel zijn of niet. Het kan een unieke sleutel op een enkele kolom zijn, of een multi-kolom (samengestelde sleutel).
Meerdere rijen invoegen
INSERT INTO `my_table` (`field_1`, `field_2`) VALUES
('data_1', 'data_2'),
('data_1', 'data_3'),
('data_4', 'data_5');
Dit is een eenvoudige manier om meerdere rijen tegelijk toe te voegen met één INSERT instructie.
Dit soort 'batch' invoegen is veel sneller dan rijen één voor één invoegen. Normaal gesproken is het op deze manier 100 rijen invoegen in een enkele batchinvoeging 10 keer zo snel als ze allemaal afzonderlijk invoegen.
Bestaande rijen negeren
Bij het importeren van grote gegevenssets kan het in bepaalde omstandigheden de voorkeur hebben om rijen over te slaan die meestal tot gevolg hebben dat de query mislukt vanwege een kolombeperking, bijvoorbeeld dubbele primaire sleutels. Dit kan worden gedaan met INSERT IGNORE .
Overweeg de volgende voorbeelddatabase:
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 |
+----+--------+
Het belangrijkste om te onthouden is dat INSERT IGNORE ook andere fouten stilzwijgend overslaat, dit is wat de officiële documentatie van Mysql zegt:
Dataconversies die fouten zouden veroorzaken, zullen de instructie afbreken als IGNORE niet> is opgegeven. Met IGNORE worden ongeldige waarden aangepast aan de dichtstbijzijnde waarden en> ingevoegd; waarschuwingen worden geproduceerd, maar de instructie wordt niet afgebroken.
Opmerking: - De onderstaande sectie is toegevoegd voor de volledigheid, maar wordt niet als beste praktijk beschouwd (dit zou bijvoorbeeld mislukken als er een andere kolom aan de tabel werd toegevoegd).
Als u de waarde van de overeenkomstige kolom voor alle kolommen in de tabel opgeeft, kunt u de kolomlijst in de instructie INSERT als volgt negeren:
INSERT INTO `my_table` VALUES
('data_1', 'data_2'),
('data_1', 'data_3'),
('data_4', 'data_5');
INSERT SELECT (Gegevens uit een andere tabel invoegen)
Dit is de basismanier om gegevens uit een andere tabel in te voegen met de SELECT-instructie.
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`;
U kunt SELECT * FROM , maar dan tableA en tableB moet hebben bijpassende kolomtelling en de bijbehorende datatypes.
Kolommen met AUTO_INCREMENT worden behandeld zoals in de clausule INSERT with VALUES .
Deze syntaxis maakt het gemakkelijk om (tijdelijke) tabellen te vullen met gegevens uit andere tabellen, zelfs nog meer wanneer de gegevens op de invoeging moeten worden gefilterd.
INSERT met AUTO_INCREMENT + LAST_INSERT_ID ()
Wanneer een tabel een PRIMARY KEY AUTO_INCREMENT heeft, wordt deze normaal gesproken niet in die kolom ingevoegd. Geef in plaats daarvan alle andere kolommen op en vraag vervolgens wat de nieuwe id was.
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, ...);
Merk op dat LAST_INSERT_ID() aan de sessie is gekoppeld, dus zelfs als er meerdere verbindingen in dezelfde tabel worden ingevoegd, krijgen elk met hun eigen id.
Uw client-API heeft waarschijnlijk een alternatieve manier om de LAST_INSERT_ID() zonder daadwerkelijk een SELECT en de waarde terug te geven aan de client in plaats van deze in een @variable in MySQL achter te laten. Dit heeft meestal de voorkeur.
Langer, meer gedetailleerd voorbeeld
Het "normale" gebruik van IODKU is het activeren van een "dubbele sleutel" op basis van een UNIQUE sleutel, niet de AUTO_INCREMENT PRIMARY KEY . Het volgende laat dit zien. Merk op dat het niet de id in de INSERT levert.
Stel de volgende voorbeelden in:
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 |
+----+--------+------+
Het geval van IODKU die een "update" LAST_INSERT_ID() en LAST_INSERT_ID() die de relevante id LAST_INSERT_ID() :
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 |
+------------------+
Het geval waarin IODKU een "invoeging" uitvoert en LAST_INSERT_ID() haalt de nieuwe 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 |
+------------------+
Resulterende tabelinhoud:
SELECT * FROM iodku;
+----+--------+------+
| id | name | misc |
+----+--------+------+
| 1 | Leslie | 123 |
| 2 | Sally | 3333 | -- IODKU changed this
| 3 | Dana | 789 | -- IODKU added this
+----+--------+------+
Auto_INCREMENT-id's verloren
Verschillende 'insert'-functies kunnen ID's "branden". Hier is een voorbeeld van InnoDB (andere motoren kunnen anders werken):
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'
+----+--------+
Zie het (grof) op deze manier: eerst kijkt de invoeging hoeveel rijen er kunnen worden ingevoegd. Pak vervolgens zoveel waarden uit de auto_increment voor die tabel. Voeg ten slotte de rijen in, gebruik zo nodig ID's en verbrand eventuele restjes.
De enige keer dat de restanten kunnen worden hersteld, is als het systeem wordt afgesloten en opnieuw wordt opgestart. Bij het opnieuw opstarten wordt effectief MAX(id) uitgevoerd. Dit kan ID's hergebruiken die zijn verbrand of die zijn vrijgemaakt door DELETEs van de hoogste ID ('s).
In wezen kan elke smaak van INSERT (inclusief REPLACE , wat DELETE + INSERT ) ID's verbranden. In InnoDB kan de globale (niet sessie!) Variabele innodb_autoinc_lock_mode worden gebruikt om een deel van wat er gaande is te regelen.
Bij het "normaliseren" van lange reeksen in een AUTO INCREMENT id , kan branden gemakkelijk gebeuren. Dit kan leiden tot een overstroming van de grootte van de INT u hebt gekozen.