mysql update if exists else insert

[mldb1]

Moin,
Ich bastel an eine Tabelle mit ca 250000 Einträgen:
- Serialnumber int(11) / AUTO_INCREMENT
- Und diversen Daten: (int(11),double,bigint(11)
Minütlich will ich ca. 10000 Daten aktualisieren oder neu einfügen.
Also etwa "mysql update if exists else insert".
Problem dabei ist, dass ich gern mit einem Befehl so viele wie möglich von den Daten einfügen lassen will, da ich nicht jeden einzelnen Wert prüfen kann.
Gibt es da einen Befehl, der das kann?
Vielen Dank
Martin

 

[castorp]

Du suchst nach: INSERT ON DUPLICATE KEY UPDATE: MySQL :: MySQL 8.0 Reference Manual :: 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Syntax

 

[mldb1]

Ok, das klingt gut. Jetzt noch mal andersrum:
Eigentlich habe ich die Daten alle schon in einer Tabelle. Problem dabei ist, dass in der Tabelle bald eine Milliarde (1.000.000.000) zeilen sind. Jetzt müsste ich für die Seriennummern (ca. 10.000) nach den jeweils letzten Einträgen suchen. Wenn ich nach einer speziellen Seriennummer suche geht das akzeptablen schnell.
SELECT *
FROM serialnumbers
WHERE sn LIKE 123456789
ORDER BY id DESC
LIMIT 1
Nur, wie könnte ich jetzt Ressourcen schonend nach 10.000 Seriennummern suchen?
Vielen Dank
Martin

 

[castorp]

Huch, wie kommen wir denn von einem INSERT zu einem SELECT?

LIKE ist für Strings (Zeichen), nicht für Zahlen (Datenbanken die etwas mehr Wert auf korrekte Datentypen legen, würden ein LIKE 1234 direkt als Fehler zurückweisen). Wenn Du eine bestimmte Zahl suchst, solltest Du WHERE sn = 1234 verwenden.

Wenn auf SN ein Index liegt, sollte ein

where sn between 1 and 10000

eigentlich recht schnell gehen.

Mit einem INSERT ON DUPLICATE musst Du aber vorher gar nicht prüfen ob die Zahlen existieren. Das passiert automatisch wenn auf der Spalten "SN" ein unique Index definiert wurde. Es gibt keinen Grund die Arbeit doppelt zu machen.

 

[mldb1]

sorry, war habs vielleicht nicht ausreichend erklärt.
Die eigentliche Datenbank in die alle Daten gehen hat die eine Milliarde Zeilen und ca. 70GB. Wenn ich da viele Abfragen drauf laufen lassen, dann......
Darum wollte ich für die aktuellen Daten der Seriennummern eine zweite Tabelle anlegen, die gleichzeitig gefüllt wird mit INSERT ON DUPLICATE KEY UPDATE.
Wenn ich allerdings von ca. 10.000 Seriennummern in kurzer Zeit jeweils den letzten Wert auslesen kann, dann bräuchte ich die zweite Tabelle nicht.
Ok, = anstelle von Like
SELECT *
FROM serialnumbers
WHERE sn = 123456789
ORDER BY id DESC
LIMIT 1
Ich würde jetzt aber gern folgendes machen:
SELECT *
FROM serialnumbers
WHERE sn = 123456789,234567891,345678912
ORDER BY id DESC
LIMIT 1
Wobei Limit sagt, dass für jede sn jeweils nur der letzte Wert ausgegeben wird.
Ich will aber auch nicht die sn zwischen 123 und 345 ausgeben, sodern wird durcheinander diverse.
Geht sowas?
Viele Grüße
Martin

 

[akretschmer]

Das geht einfacher, demo in PostgreSQL:

test=# select * from ziel;
 id | data
----+------
  1 |    1
  2 |    2
  3 |    3
  4 |    4
  5 |    5
  6 |    6
  7 |    7
  8 |    8
  9 |    9
 10 |   10
(10 rows)

test=*# select * from quelle;
 id | data
----+------
  5 |   50
  6 |   60
  7 |   70
  8 |   80
  9 |   90
 10 |  100
 11 |  110
 12 |  120
 13 |  130
 14 |  140
 15 |  150
(11 rows)

test=*# insert into ziel select * from quelle on conflict (id) do update set data = excluded.data where ziel.id=excluded.id;
INSERT 0 11
test=*# select * from ziel;
 id | data
----+------
  1 |    1
  2 |    2
  3 |    3
  4 |    4
  5 |   50
  6 |   60
  7 |   70
  8 |   80
  9 |   90
 10 |  100
 11 |  110
 12 |  120
 13 |  130
 14 |  140
 15 |  150
(15 rows)

test=*#

 

[castorp]

Meinst Du:

WHERE sn in (123456789,234567891,345678912)

Ich weiß aber nicht, ob der MySQL Query Optimizer da schlau genug ist, dafür einen Index zu verwenden.

 

[mldb1]

ja, so halb.

SELECT *
FROM serialnumbers
WHERE sn in (123456789,234567891,345678912)
ORDER BY id DESC
LIMIT 1

Diese Variante schmeißt jetzt natürlich nur den ersten Treffer raus. Wenn ich Limit auf 3 setze, dann kann es aber auch sein, dass ich doppelte Einträge bekomme, also 123456789 zwei mal und 234567891 ein mal, dafür gibt es für 345678912 kein Ergebnis.
Ich bräuchte also sowas wie LIMIT 1 UNIQUE oder so?
Danke
Martin

 

[akretschmer]

warum überhaupt noch mal diese zweite Tabelle und warum kein Unique/Primary auf dieser Spalte?

 

[castorp]

Wenn Du einen UNIQUE Index auf der SN Spalte in der "richtigen" Tabelle hast, dann brauchst Du keine zweite Tabelle.
Sogar MySQL sollte dann schlau genug sein, den Index zur Überprüfung der Werte zu verwenden.

 

[mldb1]

in der Haupttabelle hab ich kein UNIQUE. da geht es ja darum alle Aktualisierungen drin zu haben. Die werte zu den SN ändern sich praktisch minütlich.
Es geht entweder nur über die zweite Tabelle oder darüber dass jeweils die neusten Werte ausgelesen werden, denk ich...

 

[castorp]

Dann nimm halt einen normalen Index und keinen unique.

 

[akretschmer]

... und select distinct ...

 

[mldb1]

Moin,
also so richtig komme ich noch nicht weiter.
Also jetzt noch mal komplett. Die große Tabelle schaut folgendermaßen aus:
CREATE TABLE `positionreport` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mmsi` int(11) DEFAULT NULL,
`nav_status` int(11) DEFAULT NULL,
`rot` double DEFAULT NULL,
`sog` double DEFAULT NULL,
`x` double DEFAULT NULL,
`y` double DEFAULT NULL,
`cog` double DEFAULT NULL,
`true_heading` int(11) DEFAULT NULL,
`timestamp` bigint(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `mmsi` (`mmsi`)
) ENGINE=InnoDB AUTO_INCREMENT=832048940 DEFAULT CHARSET=utf8mb4
Es sind also Schiffspositionen, die ich Sammle.
Und was ich jetzt aus der Sammlung haben will sind die jeweils letzten Positionen von einer Auswahl an MMSI.
Vielleicht erklärt es das noch mal etwas genauer.
Danke
Martin