MySQL-Abfrage soll auch doppelte Einträge aus find_in_set liefern

[Klaus1]

Hallo,

Ich habe in der Tabelle 1 ein Feld in dem mehrere Artikelnummern durch Komma getrennt aufgelistet werden. Dabei können Artikelnummern aber auch doppelt vorkommen, also bspw. 519,83,77,84,81,519,519,519

Die Detaildaten der Artikel stehen in Tabelle 2.

Wie kann ich diese nun so verknüpfen, dass ich in der Ergebnisliste auch der Artikel, der mehrfach vorkommt, mehrfach auftaucht?

Meine bisherige Abfrage liefert immer nur 1 Ergebnis pro Artikelnummer:

SELECT t2.* FROM tabelle1 as t1 LEFT JOIN tabelle2 as t2 ON find_in_set(t2.artnr, t1.artikelliste) where t1.artikelliste <> '' order by typ, bezeichnung, preis

LG Klaus

[Raketenwilli]

Wie kann ich diese nun so verknüpfen, dass ich in der Ergebnisliste auch der Artikel, der mehrfach vorkommt, mehrfach auftaucht?

Meine bisherige Abfrage liefert immer nur 1 Ergebnis pro Artikelnummer:

SELECT t2.* FROM tabelle1 as t1 LEFT JOIN tabelle2 as t2 ON find_in_set(t2.artnr, t1.artikelliste) where t1.artikelliste <> '' order by typ, bezeichnung, preis

Erst mal das Zitat:

A SET column can have a maximum of 64 distinct members.

Duplicate values in the definition cause a warning, or an error
if strict SQL mode is enabled.

Nur 64 verschiedene Artikelnummern? Das klingt nicht nach Zukunft…

Und jetzt?

Dein Aufbau ist fatal und fährt früher oder später gegen eine Wand. Schon jetzt ist erkennbar, dass der Join über das Set irgendwann zu einem inakzeptablen Performanceverlust führen wird, denn das kann man so auch nicht indizieren. Außerdem willst ja wohl kaum Tabellendefinitionen ändern, wenn ein Artikel dazu kommt oder gestrichen wird.

Lösung:

Löse das Set auf → neue Tabelle.

Dann klappt es auch mit select count.

[Klaus1]

Erst mal das Zitat:

A SET column can have a maximum of 64 distinct members.

Duplicate values in the definition cause a warning, or an error
if strict SQL mode is enabled.

Nur 64 verschiedene Artikelnummern? Das klingt nicht nach Zukunft…

Und jetzt?

In meiner Anwendung läuft es im Moment mit einer Liste von aktuell 419 unterschiedlichen Artikeln. Und dies ist das maximale Extrem. Üblich sind nicht mehr als 3-4 Einträge. Sicherlich weil das Feld nicht vom Typ "Set" ist, sondern vom Typ "longtext". Find_in_set funktioniert ja auch wunderbar mit Textfeldern.

MySQL provides a built-in string function called FIND_IN_SET() that allows you to find the position of a string within a comma-separated list of strings.

Performance-Probleme habe ich bisher auch keine feststellen können, subjektiv wird das Ergebnis unverzögert und sofort geliefert.

LG Klaus

[Raketenwilli]

In meiner Anwendung läuft es im Moment mit einer Liste von aktuell 419 unterschiedlichen Artikeln. Und dies ist das maximale Extrem. Üblich sind nicht mehr als 3-4 Einträge. Sicherlich weil das Feld nicht vom Typ "Set" ist, sondern vom Typ "longtext".

Dein „set“ ist also sogar gar kein „set“.

Das ist „abenteuerlich“ - und ich betone, diese Bezeichnung ist höchst zurückhaltend. Es gibt Wörter, die haben mit dem Endprodukt eines biologisch-gastronomischen Prozesses zu tun, die wären „treffend“.

In meiner Anwendung läuft es im Moment

Eigentlich ja schon nicht mehr. Das Konstrukt erfüllt den Punkt „gleiche Artikelnummern sollen gezählt ausgegeben werden können“ nicht. (Das geht schon, aber eben nicht ohne einen riesigen Aufwand)

Es mag Arbeit machen, aber wenn Du ordentliche Ergebnisse willst brauchst Du ordentliche Tabellen und eben nicht Tabellen als Sets in Tabellen, für die Du dann auch noch Textfelder missbrauchst. (Genau genommen hast Du also CSV in der Tabelle.)

Find_in_set funktioniert ja auch wunderbar mit Textfeldern.

Den Satz „MySQL provides a built-in string function called FIND_IN_SET() that allows you to find the position of a string within a comma-separated list of strings.“ haben diverse Tutorials voneinander abgeschrieben, er steht nicht in der offiziellen Dokumentation. Möglicherweise wird für FIND_IN_SET() also irgendwann ein tatsächliches Set verlangt.

Möglicherweise funktioniert das ergo nur bis zum nächsten Update. Also hast Du entweder später noch viel mehr Arbeit oder machst keine Updates mehr. Was dann irgendwann vielleicht dazu führt, das Du vor die Kunden treten und denen mitteilen musst, dass deren persönliche Daten abgeflossen sind.

ToDo: Mach es gleich richtig.

[Raketenwilli]

Find_in_set funktioniert ja auch wunderbar mit Textfeldern.

Den Satz „MySQL provides a built-in string function called FIND_IN_SET() that allows you to find the position of a string within a comma-separated list of strings.“ haben diverse Tutorials voneinander abgeschrieben, er steht nicht in der offiziellen Dokumentation. Möglicherweise wird für FIND_IN_SET() also irgendwann ein tatsächliches Set verlangt.

Möglicherweise funktioniert das ergo nur bis zum nächsten Update. Also hast Du entweder später noch viel mehr Arbeit oder machst keine Updates mehr. Was dann irgendwann vielleicht dazu führt, das Du vor die Kunden treten und denen mitteilen musst, dass deren persönliche Daten abgeflossen sind.

Ich habe mir mal die Doc angesehen.

Demnach ist find_in_set() zwar für MySQL 4 bis 8 und MariaDB dokumentiert, aber das ändert nichts daran, dass Dein Vorgehen falsch ist. Wenn Du die Datenbankarbeit vom Textfunktionen erledigen lassen willst, kannst Du die Datenbank auch gleich wegwerfen und aus einer Liste wunderbarer Unix-Programme den AWK nehmen.

ToDo: Mach es gleich richtig.

[Rolf B]

Hallo Raketenwilli,

Ich habe mir mal die Doc angesehen.

ich auch.

If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic.

D.h. find_in_set funktioniert durchaus mit CSV-Listen die länger sind als 64 Einträge. Es ist aber eine sequenzielle Suche in einem String und damit - wie Du sagtest - nicht indizierbar.

Rolf

[Rolf B]

Hallo Klaus1,

Wie kann ich diese nun so verknüpfen, dass ich in der Ergebnisliste auch der Artikel, der mehrfach vorkommt, mehrfach auftaucht?

Nicht mit SQL. Der SET Datentyp von MYSQL ist für sowas verlockend, aber ein find_in_set in einer JOIN-Bedingung ist ein ekelhafter Köter, der nur darauf wartet, dass deine Anwendung wächst, um Dir dann mit Begeisterung in den A...llerwertesten zu beißen.

Wie Raketenwilli sagte: find_in_set ist nicht indizierbar. MYSQL muss also für jede Row in Tabelle 1 einen Full Table Scan von Tabelle 2 machen, um den Join herzustellen. Bei 419 Artikeln wird er Tabelle 2 wohl noch komplett im Cache-RAM halten können. Aber genau da kann ein Artikel mehr in Tabelle 2 den Unterschied zwischen "geht noch im Cache" und "jetzt cache ich das nicht mehr komplett" ausmachen, und dann bricht die Performance massiv ein.

Der Set-Datentyp ist einer der vielen Designfehler von MYSQL. Statt Dich zur Normalisierung anzuhalten, bietet es Dir Datentypen an, die eine korrekte DB-Modellierung geradezu als dumm erscheinen lassen. Warum sollte man eine Artikelnummernliste in eine Tabelle auslagern, wenn man sie doch ganz praktisch als kommaseparierte Liste in einen String stecken kann und die DB auch noch Funktionen bietet, darin herumstochern zu können.

Aber sobald die Wünsche wachsen, ist es dann vorbei. Ein Set ist definiert als eine Datenstruktur, die keine doppelten Einträge kennt. Und damit ist find_in_set am Ende.

Wenn Du bei der CSV Liste bleiben willst, musst Du auf den LEFT JOIN verzichten. In PHP kannst Du die Artikelliste nehmen und sie als Werteliste in eine IN-Klausel eintragen. Damit holst Du Dir die benötigten Artikel aus der DB. Wobei man das auch noch mal mit EXPLAIN überprüfen müsste, es kann nämlich sein, dass ein IN ebenfalls einen Tablescan macht. Danach EXPLODEst Du die Artikelliste und gibst für jede Artikelnummer den zugehörigen Satz aus. Ist eine Nummer drölfmal drin, wird der Satz dann auch drölf mal ausgegeben.

Ist das empfehlenswertes DB Design? Nein!

Rolf

[Raketenwilli]

Der Set-Datentyp ist einer der vielen Designfehler von MYSQL.

Jepp!

Verschlimmert dadurch, dass sich eine Funktion namens „find_in_set()“ auch noch auf echte Sets und Komma(sic!)-separierte Strings anwenden lässt. Das ist „sowas von voriges Jahrtausend“ - als England noch eine gebärfähige Königin hatte und Elektroautos mit Bleiakkkus herumfuhren war das sicherlich „modern“.

[Klaus1]

Ich huldige hiermit Deine gottgleichen Überlegenheit und unermesslichen Fehlerlosigkeit und verneige mich zutiefst demütig vor diesem begnadetem Wissen, dass Du Dir ohne jeglichen Lernprozess unmittelbar erworben hast. Ohne jegliche Aussicht auch nur annähernd auf dieses himmliche Niveau zu kommen, werde ich alle meine beschämenden Werke unwiederbringlich zerstören und auf ein baldiges Ende meines jämmerlichen Daseins hoffen.

[Tabellenkalk]

Hallo,

endlich mal einer der den Sinn dieses Forums begriffen hat…

Gruß
Kalk

[Raketenwilli]

vor diesem begnadetem Wissen, dass Du Dir ohne jeglichen Lernprozess unmittelbar erworben hast.

Ok. Also wenn's denn persönlich werden soll:

Wenn man Deine Antwort zu würdigen weiß, dann verweigerst Du Dich gerade dem Lernprozess, den nicht gehabt zu haben Du mir einfach mal grundlos und in offensichtlicher Herabwürdigungsabsicht unterstellst - nur um Deine Verweigerung der notwendigen Reparatur Deiner Datenstruktur zu rechtfertigen.

werde ich alle meine beschämenden Werke unwiederbringlich zerstören

Das hat keiner verlangt. Ich habe Dir nur den Weg vom Holzweg zur Autobahn gezeigt, weil ich annahm, Du willst weiter kommen. Du willst aber auf Deinem Weg bleiben.

[Rolf B]

Hallo Klaus1,

mit diesem vor Sarkasmus triefenden Beitrag möchstest Du Jörg und mir mit der Drahtbürste einreiben, dass wir uns viel zu wichtig nehmen und deine Auffassung anerkennen sollten. Denn das, was Du gebaut hast, funktioniert ja für Dich.

Wir haben es also nicht geschafft, Dich bei unseren Vorschlägen mitzunehmen. Das tut mir ehrlich leid.

begnadetem Wissen, dass Du Dir ohne jeglichen Lernprozess unmittelbar erworben hast

Das ist weder Gnade noch ein Geschenk. Es sind - bei mir - 40 Jahre harte Arbeit. Jörg hat zwar deutlich mehr Haare als ich, aber ich glaube nicht, dass er viel jünger ist. Oder weniger hart an seinem Wissen gearbeitet hat. Und da ist man dann tatsächlich schonmal der Meinung, dass einem die Erfahrung recht gibt - der Fluch des alten Mannes. Bitte entschuldige.

Eine mögliche Lösung, auch mit deiner Datenstruktur, habe ich angedeutet. Es geht eben nur nicht in einem SQL Statement. Ich hoffe, du kannst damit etwas anfangen.

Rolf

[TS]

Hello,

[...]
Außerdem hat der SET-Datentyp immer noch eine Macke, wenn man numerische Werte verwendet. Da kann versehentlich die Position im Set und nicht der Eintrag gefunden werden.

Glück Auf
Tom vom Berg