MySQL: Praktische Erfahrung mit der Verwendung der Insert-Anweisung

1. Mehrere Syntaxen von Insert

1-1. Gewöhnliche Insert-Anweisung

INSERT INTO-Tabelle (`a`, `b`, `c`, …) VALUES ('a', 'b', 'c', …);

Ich werde hier nicht ins Detail gehen, achten Sie einfach auf die Reihenfolge. Ich empfehle Ihnen nicht, den Inhalt in den vorderen Klammern zu entfernen. Fragen Sie nicht nach dem Grund, da Sie sonst möglicherweise von Ihren Kollegen gescholten werden.

1-2. Einfügen oder aktualisieren

Wenn wir einen neuen Datensatz einfügen (INSERT), den Datensatz aber aktualisieren möchten, wenn er bereits vorhanden ist, können wir die Anweisung „INSERT INTO … ON DUPLICATE KEY UPDATE …“ verwenden:

Szenariobeispiel: Diese Tabelle speichert den historischen Aufladebetrag des Benutzers. Wenn es sich um die erste Aufladung handelt, wird ein neuer Datensatz hinzugefügt. Wenn der Benutzer zuvor aufgeladen hat, wird der historische Aufladebetrag kumuliert. Es muss sichergestellt werden, dass die Daten eines einzelnen Benutzers nicht wiederholt eingegeben werden.

Zu diesem Zeitpunkt können Sie die Anweisung „INSERT INTO … ON DUPLICATE KEY UPDATE …“ verwenden.

Hinweis: Die Anweisung „INSERT INTO … ON DUPLICATE KEY UPDATE …“ ermittelt die Eindeutigkeit (Existenz) anhand des eindeutigen Indexes bzw. Primärschlüssels. Wie im folgenden SQL gezeigt, müssen Sie einen eindeutigen Index (Unique) für das Feld „Benutzername“ erstellen und die Trans-ID auf automatische Inkrementierung einstellen.

-- Benutzer Chen Haha hat 30 Yuan aufgeladen, um eine Mitgliedschaft zu kaufen. INSERT INTO total_transaction (t_transId, username, total_amount, last_transTime, last_remark) 
 WERTE (null, ‚chenhaha‘, 30, ‚2020-06-11 20:00:20‘, ‚Mitgliedschaft hinzufügen‘) 
 BEIM UPDATE DES DOPPELTEN SCHLÜSSELS total_amount=total_amount + 30, last_transTime='2020-06-11 20:00:20', last_remark ='Nicht berücksichtigt';
 
-- Benutzer Chen Haha hat 100 Yuan aufgeladen, um Blind Man‘s Supreme Fist-Skin zu kaufen. INSERT INTO total_transaction (t_transId, username, total_amount, last_transTime, last_remark) 
 WERTE (null, „chenhaha“, 100, „11.06.2020 20:00:20“, „Lee Sin Supreme Fist-Skin kaufen“) 
 BEI DUPLIZIERTEM SCHLÜSSEL-UPDATE total_amount=total_amount + 100, last_transTime='2020-06-11 21:00:00', last_remark ='Lee Sin Supreme Fist Skin kaufen';

Wenn der Datensatz mit Benutzername='chenhaha' nicht existiert, fügt die INSERT-Anweisung einen neuen Datensatz ein. Andernfalls wird der aktuelle Datensatz mit Benutzername='chenhaha' aktualisiert und die aktualisierten Felder werden durch UPDATE angegeben.

Übrigens ist ON DUPLICATE KEY UPDATE eine MySQL-spezifische Syntax. Wenn Sie beispielsweise MySQL nach Oracle oder anderen Datenbanken migrieren, müssen ähnliche Anweisungen in die MERGE INTO-Syntax geändert werden, was das Kompatibilitätsproblem so schlimm macht, dass man fluchen möchte. Aber das geht nicht, denn Office kann auch keine in WPS geschriebene xlsx-Datei öffnen.

1-3. Einfügen oder ersetzen

Wenn wir einen neuen Datensatz einfügen möchten (INSERT), der Datensatz aber bereits vorhanden ist, löschen Sie zuerst den Originaldatensatz und fügen Sie dann den neuen Datensatz ein.

Szenariobeispiel: In dieser Tabelle werden die aktuellsten Transaktionsauftragsinformationen für jeden Kunden gespeichert. Sie ist erforderlich, um sicherzustellen, dass die Daten eines einzelnen Benutzers nicht wiederholt eingegeben werden. Außerdem soll sie die höchste Ausführungseffizienz und die geringste Interaktion mit der Datenbank aufweisen und eine hohe Verfügbarkeit der Datenbank unterstützen.

Zu diesem Zeitpunkt können Sie die Anweisung „REPLACE INTO“ verwenden, sodass Sie nicht zuerst eine Abfrage durchführen und dann entscheiden müssen, ob gelöscht und dann eingefügt werden soll.

Mit der Anweisung „REPLACE INTO“ wird ermittelt, ob eine Eindeutigkeit (Existenz) auf einem eindeutigen Index oder Primärschlüssel beruht.
Mit der Anweisung „REPLACE INTO“ wird ermittelt, ob eine Eindeutigkeit (Existenz) auf einem eindeutigen Index oder Primärschlüssel beruht.
Mit der Anweisung „REPLACE INTO“ wird ermittelt, ob eine Eindeutigkeit (Existenz) auf einem eindeutigen Index oder Primärschlüssel beruht.

Hinweis: Wie im folgenden SQL gezeigt, müssen Sie einen eindeutigen Index (Unique) für das Feld „Benutzername“ erstellen und die Trans-ID auf automatische Inkrementierung einstellen.

-- 20 Punkte aufladen REPLACE INTO last_transaction (Transaktions-ID, Benutzername, Betrag, Transaktionszeit, Bemerkung) 
 WERTE (null, ‚chenhaha‘, 30, ‚11.06.2020 20:00:20‘, ‚Mitgliederaufladung‘);
 
-- Skin um 21 Uhr kaufen REPLACE INTO last_transaction (transId, username, amount, trans_time, remark) 
 WERTE (null, ‚chenhaha‘, 100, ‚11.06.2020 21:00:00‘, ‚Lee Sin Supreme Fist-Skin kaufen‘);

Wenn der Datensatz mit Benutzername='chenhaha' nicht existiert, fügt die REPLACE-Anweisung einen neuen Datensatz ein (erste Neuladung), andernfalls wird der aktuelle Datensatz mit Benutzername='chenhaha' gelöscht und dann der neue Datensatz eingefügt.

Geben Sie für die ID keinen bestimmten Wert an, da sonst die SQL-Ausführung beeinträchtigt wird, es sei denn, es liegen besondere Geschäftsanforderungen vor.

Tipps:
BEIM UPDATE MIT DOPPELTEM SCHLÜSSEL: Wenn die eingefügte Zeile einen doppelten eindeutigen Index oder Primärschlüssel aufweist, wird das alte Update ausgeführt. Wenn dadurch kein doppelter eindeutiger Index oder Primärschlüssel entsteht, wird direkt eine neue Zeile hinzugefügt.
ERSETZEN IN: Wenn die eingefügte Zeile einen doppelten eindeutigen Index oder Primärschlüssel aufweist, löschen Sie den alten Datensatz und geben Sie den neuen Datensatz ein. Wenn dadurch kein doppelter eindeutiger Index oder Primärschlüssel entsteht, fügen Sie direkt eine neue Zeile hinzu.

Ersetzen durch vs. Einfügen bei doppeltem Update:

1. Wenn kein doppelter Primärschlüssel oder eindeutiger Index vorhanden ist, ist „Ersetzen durch“ dasselbe wie „Einfügen bei doppeltem Update“.

2. Wenn der Primärschlüssel oder der eindeutige Index wiederholt wird, löscht „Replace“ den alten Datensatz und gibt den neuen Datensatz ein, sodass alle ursprünglichen Datensätze gelöscht werden. Wenn zu diesem Zeitpunkt die Felder der „Replace“-Anweisung unvollständig sind, werden einige der ursprünglichen Werte, z. B. das Feld „c“, automatisch mit dem Standardwert (z. B. Null) gefüllt.

3. Aufmerksame Benutzer werden feststellen, dass das Einfügen bei doppelter Aktualisierung nur eine Zeile betrifft, während REPLACE INTO mehrere Zeilen betreffen kann. Warum? Geschrieben im letzten Abschnitt des Artikels ~

1-4. Einfügen oder ignorieren

Wenn wir einen neuen Datensatz einfügen möchten (INSERT), der Datensatz aber bereits vorhanden ist, möchten wir ihn ignorieren und nichts tun. In diesem Fall können wir die Anweisung INSERT IGNORE INTO ... verwenden: Es gibt viele Szenarien, daher werde ich nicht mit Beispielen ins Detail gehen.

Hinweis: Wie oben bestimmt die Anweisung „INSERT IGNORE INTO …“ die Eindeutigkeit (ob sie vorhanden ist) anhand eines eindeutigen Indexes oder Primärschlüssels. Sie müssen einen eindeutigen Index (Unique) für das Feld „Benutzername“ erstellen und transId auf automatische Inkrementierung einstellen.

-- Benutzer zum ersten Mal hinzugefügt INSERT IGNORE INTO users_info (id, username, sex, age, balance, create_time) 
 WERTE (null, ‚chenhaha‘, ‚Männlich‘, 26, 0, ‚11.06.2020 20:00:20‘);
 
- Zweite Ergänzung, INSERT IGNORE INTO users_info (id, username, sex, age, balance, create_time) direkt ignorieren 
 WERTE (null, ‚chenhaha‘, ‚Männlich‘, 26, 0, ‚2020-06-11 21:00:20‘);

2. Einfügen großer Datenmengen

2-1. Drei Behandlungsmethoden

2-1-1. Einfügen einer einzelnen Schleife

Wir haben 100.000 Daten genommen und einige Tests durchgeführt. Wenn die Einfügemethode darin besteht, einzeln in einer Programmdurchlaufschleife einzufügen. Die Geschwindigkeit zum Einfügen einer Zeile in MySQL liegt zwischen 0,01 s und 0,03 s.

Die durchschnittliche Geschwindigkeit des Einfügens einzeln beträgt 0,02*100000, also etwa 33 Minuten.

Der folgende Code ist ein Testbeispiel:

1. Zeittest zum Einfügen von 100.000 Datensätzen in eine normale Schleife

 @Prüfen
 öffentliche void insertUsers1() {
  
  Benutzer Benutzer = neuer Benutzer();
  
  Benutzer.setUserName("Kapitän Teemo");
  user.setPassword("sterben");
  Benutzer.setPrice(3150);
  user.setHobby("Pilze züchten");
  
  für (int i = 0; i < 100000; i++) {
   Benutzer.setUserName("Kapitän Teemo" + i);
   //Rufen Sie die Einfügemethode auf userMapper.insertUser(user);
  }
 }

Die Ausführungsgeschwindigkeit beträgt 30 Minuten, also 0,018*100000. Man kann sagen, dass es sehr langsam ist.

Dabei zeigte sich, dass der Optimierungsaufwand durch Einfügen nacheinander zu hoch war. Gehen Sie dann zur Abfrageoptimierungsmethode. Es wurde festgestellt, dass die Geschwindigkeit durch die Batch-Einfügungsmethode erheblich verbessert werden kann.

Erhöhen Sie die Einfügegeschwindigkeit von 100.000 Datensätzen auf ca. 1-2 Minuten↓

2-1-2. SQL-Anweisungen für die Batch-Einfügung ändern

in user_info einfügen (Benutzer-ID, Benutzername, Passwort, Preis, Hobby)
 Werte (null, „Captain Teemo 1“, „123456“, 3150, „Pilze züchten“, (null, „Garen“, „123456“, 450, „Auf Pilze treten“);

Verwenden Sie Batch-Einfügen, um 100.000 Datensätze einzufügen. Der Testcode lautet wie folgt:

 @Prüfen
 öffentliche void insertUsers2() {
   
  List<Benutzer> Liste= neue ArrayList<Benutzer>();
		
  Benutzer Benutzer = neuer Benutzer();
  user.setPassword("sterben");
  Benutzer.setPrice(3150);
  user.setHobby("Pilze züchten");
		
  für (int i = 0; i < 100000; i++) {
   Benutzer.setUserName("Kapitän Teemo" + i);
   // Füge ein einzelnes Objekt in die Parameterliste ein list.add(user);
   
  }
  userMapper.insertListUser(Liste);
 }

Das Batch-Einfügen dauert 0,046 Sekunden, was der Geschwindigkeit des Einfügens von einem oder zwei Daten entspricht. Daher verbessert die Verwendung des Batch-Einfügens die Geschwindigkeit des Einfügens von Daten erheblich. Wenn eine große Datenmenge eingefügt werden muss, verwenden Sie die Batch-Einfügeoptimierung.

So schreiben Sie Batch-Inserts:

DAO-Definitionsschichtmethode:

Integer insertListUser(List<Benutzer> Benutzer);

SQL-Schreiben im mybatis Mapper:

<insert id="insertListUser" parameterType="java.util.List">
  INSERT INTO `db`.`user_info`
   ( `id`,
    `Benutzername`,
    `Passwort`,
    `Preis`,
    `Hobby`) 
   Werte
  <foreach Sammlung="Liste" Element="Element" Trennzeichen=""," index="index">
   (null,
   #{item.userName},
   #{item.password},
   #{Artikelpreis},
   #{item.hobby})
  </foreach>
 
 </einfügen>

Dadurch werden Batch-Einfügevorgänge ermöglicht:

Hinweis: Dies gilt jedoch nur, wenn die Datenmenge der Stapelverarbeitung groß ist. Wenn ich beispielsweise 100.000 Datensätze in eine SQL-Anweisung einfüge und das zu verarbeitende Datenpaket 1 MB überschreitet, meldet MySQL den folgenden Fehler:

Fehlermeldung:

Mysql Sie können diesen Wert auf dem Server ändern, indem Sie die Variable „max_allowed_packet“ festlegen. Paket für Abfrage ist zu groß (6832997 > 1048576). Sie können diesen Wert auf dem Server ändern, indem Sie die Variable „max_allowed_packet“ festlegen.

erklären:

Das für die Abfrage verwendete Paket war zu groß (6832997 > 1048576). Sie können diesen Wert auf Ihrem Server ändern, indem Sie die Variable max_allowed_packet festlegen.

Aus der Erklärung ist ersichtlich, dass das für die Operation verwendete Paket zu groß ist. Die Größe der hier einzufügenden SQL-Inhaltsdaten beträgt 6 MB, daher wird ein Fehler gemeldet.

Lösung:

Die Datenbank ist MySQL57. Ich habe die Informationen überprüft und festgestellt, dass es sich um ein Systemparameterproblem von MySQL handelt:

max_allowed_packet, dessen Standardwert 1048576 (1M) ist,

Abfrage:

VARIABLEN wie „%max_allowed_packet%“ anzeigen;

Ändern Sie den Wert dieser Variable: im Abschnitt [mysqld] der Datei my.ini (Windows) oder /etc/mysql.cnf (Linux) im MySQL-Installationsverzeichnis

max_allowed_packet = 1M, wenn auf 20M geändert (oder größer, wenn es keine solche Zeile gibt, fügen Sie diese Zeile hinzu), wie unten gezeigt

Speichern Sie und starten Sie den MySQL-Dienst neu. Jetzt können Sie SQL-Anweisungen mit einer Größe größer als 1 MB und kleiner als 20 MB ausführen.

Aber was, wenn 20 Millionen nicht ausreichen?

2-1-3. In Stapeln und mehreren Zyklen einfügen

Wenn es unpraktisch ist, die Datenbankkonfiguration zu ändern, oder wenn zu viele Inhalte eingefügt werden müssen, können Sie dies auch über den Backend-Code steuern. Um beispielsweise 100.000 Daten einzufügen, können Sie in 100 Stapeln jeweils 1.000 Daten einfügen, was nur wenige Sekunden dauert. Wenn der Inhalt jedes Datenstücks sehr groß ist, ist das natürlich eine andere Sache. .

2-2. Andere Möglichkeiten zur Optimierung langsamer Einfügegeschwindigkeit

A. Verwenden Sie den Befehl „show processlist“, um zu überprüfen, ob andere lange Prozesse oder eine große Anzahl kurzer Prozesse die Ressourcen des Thread-Pools belegen. Prüfen Sie, ob Sie den Druck auf die primäre Datenbank verringern können, indem Sie einige Prozesse der Sicherungsdatenbank zuweisen. Oder beenden Sie zunächst einige nicht benötigte Prozesse. (manuell am Kopf kratzen o_O)

B. Um Daten in großen Mengen zu importieren, können Sie den Index auch zuerst schließen und ihn dann nach dem Importieren der Daten öffnen.

Deaktivieren: ALTER TABLE user_info DISABLE KEYS;
Aktivieren: ALTER TABLE user_info ENABLE KEYS;

3. Die Fallstricke der REPLACE INTO-Syntax

Oben wurde erwähnt, dass REPLACE mehr als 3 Datensätze betreffen kann, da in der Tabelle mehr als ein eindeutiger Index vorhanden ist. In diesem Fall berücksichtigt REPLACE jeden eindeutigen Index, löscht die jedem Index entsprechenden doppelten Datensätze und fügt dann den neuen Datensatz ein. Angenommen, es gibt eine Tabelle1 mit drei Feldern a, b, c. Was ist, wenn beide einen eindeutigen Index haben? Lassen Sie es uns früher mit einigen Daten testen.

- Testen Sie die Tabellenerstellung, die Felder a, b, c haben alle eindeutige Indizes. CREATE TABLE table1(a INT NOT NULL UNIQUE, b INT NOT NULL UNIQUE, c INT NOT NULL UNIQUE);
--Fügen Sie drei Testdaten ein INSERT into table1 VALUES(1,1,1);
INSERT in Tabelle1 VALUES(2,2,2);
INSERT in Tabelle1 VALUES(3,3,3);

Zu diesem Zeitpunkt hat Tabelle1 bereits 3 Datensätze und die Felder a, b und c sind alle eindeutige Indizes.

mysql> wähle * aus Tabelle1;
+---+---+---+
| ein | b | c |
+---+---+---+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+---+---+---+
3 Zeilen im Satz (0,00 Sek.)

Als nächstes verwenden wir die REPLACE-Anweisung, um einen Datensatz in Tabelle1 einzufügen.

Ersetzen Sie in Tabelle1 (a, b, c) Werte (1,2,3);

mysql> REPLACE INTO Tabelle1(a, b, c) VALUES(1,2,3);
Abfrage OK, 4 Zeilen betroffen (0,04 Sek.)

Fragen Sie zu diesem Zeitpunkt die Datensätze in Tabelle 1 wie folgt ab. Es ist nur noch ein Datensatz übrig ~

mysql> wähle * aus Tabelle1;
+---+---+---+
| ein | b | c |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
1 Zeile im Satz (0,00 Sek.)

(Chef: Nach dem Einfügen der ersten 10 W-Daten und dem Einfügen von 5 W-Daten sind immer noch 8 W-Daten übrig?? Haben Sie unsere Daten dem Hund verfüttert?!)

Syntaxüberprüfung „REPLACE INTO“: Wenn die eingefügte Zeile einen doppelten eindeutigen Index oder Primärschlüssel aufweist, löschen Sie den alten Datensatz und geben Sie den neuen Datensatz ein. Wenn dadurch kein doppelter eindeutiger Index oder Primärschlüssel entsteht, fügen Sie direkt eine neue Zeile hinzu.

Wir können sehen, dass bei Verwendung von REPLACE INTO jeder eindeutige Index betroffen ist, was zu einem versehentlichen Löschen von Daten führen kann. Daher wird empfohlen, REPLACE INTO nicht in einer Tabelle mit mehreren eindeutigen Indizes zu verwenden.

Zusammenfassen

Dies ist das Ende dieses Artikels über die Erfahrungen mit der Verwendung der Insert-Anweisung in der MySQL-Praxis. Weitere relevante Inhalte über die Erfahrungen mit der Verwendung der MySQL-Insert-Anweisung finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder durchsuchen Sie die verwandten Artikel weiter unten. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!